If statement for if a due date is next month
This is my current statement, but I would like to add a statement for if the [Due date] falls with in the next month, to render "Next month"
=IF(ISBLANK([Due date]@row), " ", IF([Due date]@row < TODAY(), "Late", IF([Due date]@row = TODAY(), "Today", IF([Due date]@row < TODAY(), "Late", IF([Due date]@row < TODAY(5), "This Week", IF(AND(MONTH([Due date]@row) = MONTH(TODAY()), YEAR([Due date]@row) = YEAR(TODAY())), "This Month", IF(AND(MONTH([Due date]@row) >= MONTH(TODAY()), YEAR([Due date]@row) >= YEAR(TODAY())), "Future")))))))
For example:
- Todays is 1/28/2025 the Due date is 2/11/2025, I want this to render "Next month"
- Today is 1/28/2025 the Due date is 3/11/2025 I want this to render "Future"
Thank you in advance for suggestions/advice!
Best Answer
-
Hello @Jess.Peace
You are getting #UNPARSEABLE because Smartsheet does not support the EOMONTH function.
You can modify your formula to include a condition that checks if the [Due date] falls within the next month. Here’s your updated formula:
=IF(ISBLANK([Due Date]@row), " ", IF([Due Date]@row < TODAY(), "Late", IF([Due Date]@row = TODAY(), "Today", IF([Due Date]@row < TODAY(5), "This Week", IF(AND(MONTH([Due Date]@row) = MONTH(TODAY()), YEAR([Due Date]@row) = YEAR(TODAY())), "This Month", IF(AND(MONTH([Due Date]@row) = MONTH(TODAY()) + 1, YEAR([Due Date]@row) = YEAR(TODAY())), "Next Month", "Future"))))))
How it works:
- Checks if
[Due date]
is blank, returns" "
if so. - If
[Due date]
is before today, returns"Late"
. - If
[Due date]
is today, returns"Today"
. - If
[Due date]
is within 5 days, returns"This Week"
. - If
[Due date]
is in the current month, returns"This Month"
. - New Condition: If
[Due date]
is in next month (same year), returns"Next Month"
. - If none of the above apply, it defaults to
"Future"
.
It woked for me as you can see in the image.
Hope this helps!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions - Checks if
Answers
-
=IF(ISBLANK([Due date]@row), " ",
IF([Due date]@row < TODAY(), "Late",
IF([Due date]@row = TODAY(), "Today",
IF([Due date]@row < TODAY(5), "This Week",
IF(AND(MONTH([Due date]@row) = MONTH(TODAY()), YEAR([Due date]@row) = YEAR(TODAY())), "This Month",
IF(AND(MONTH([Due date]@row) = MONTH(TODAY() + 30), YEAR([Due date]@row) = YEAR(TODAY() + 30)), "Next Month",
"Future")))))))
This hard codes in the +30. Give it a try and see if it helps. -
This might work as well, and will check for the current month.
=IF(ISBLANK([Due date]@row), " ",
IF([Due date]@row < TODAY(), "Late",
IF([Due date]@row = TODAY(), "Today",
IF([Due date]@row < TODAY() + 7, "This Week",
IF(AND(MONTH([Due date]@row) = MONTH(TODAY()), YEAR([Due date]@row) = YEAR(TODAY())), "This Month",
IF(AND(MONTH([Due date]@row) = MONTH(EOMONTH(TODAY(), 0) + 1), YEAR([Due date]@row) = YEAR(EOMONTH(TODAY(), 0) + 1)), "Next Month",
"Future"))))))) -
Thank you!
I tried the first option but hard coding the 30 day causes February due dates to render "future" and March due dates render as "Next Month"
I am trying the second suggestion but it is giving me #UNPARSEABLE error, I cant find any obvious errors.
Any suggestions?
Again, I appreciate the help!
-
Hello @Jess.Peace
You are getting #UNPARSEABLE because Smartsheet does not support the EOMONTH function.
You can modify your formula to include a condition that checks if the [Due date] falls within the next month. Here’s your updated formula:
=IF(ISBLANK([Due Date]@row), " ", IF([Due Date]@row < TODAY(), "Late", IF([Due Date]@row = TODAY(), "Today", IF([Due Date]@row < TODAY(5), "This Week", IF(AND(MONTH([Due Date]@row) = MONTH(TODAY()), YEAR([Due Date]@row) = YEAR(TODAY())), "This Month", IF(AND(MONTH([Due Date]@row) = MONTH(TODAY()) + 1, YEAR([Due Date]@row) = YEAR(TODAY())), "Next Month", "Future"))))))
How it works:
- Checks if
[Due date]
is blank, returns" "
if so. - If
[Due date]
is before today, returns"Late"
. - If
[Due date]
is today, returns"Today"
. - If
[Due date]
is within 5 days, returns"This Week"
. - If
[Due date]
is in the current month, returns"This Month"
. - New Condition: If
[Due date]
is in next month (same year), returns"Next Month"
. - If none of the above apply, it defaults to
"Future"
.
It woked for me as you can see in the image.
Hope this helps!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions - Checks if
-
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions -
Yes that is perfect!! Thank you so very much!!
-
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!