If statement for if a due date is next month

Jess.Peace

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!

Marcela

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:

1. Checks if [Due date] is blank, returns " " if so.
2. If [Due date] is before today, returns "Late".
3. If [Due date] is today, returns "Today".
4. If [Due date] is within 5 days, returns "This Week".
5. If [Due date] is in the current month, returns "This Month".
6. New Condition: If [Due date] is in next month (same year), returns "Next Month".
7. If none of the above apply, it defaults to "Future".

It woked for me as you can see in the image.

Hope this helps!

Stephen McDaniel

=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.

Stephen McDaniel

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")))))))

Jess.Peace

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!

Marcela

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:

1. Checks if [Due date] is blank, returns " " if so.
2. If [Due date] is before today, returns "Late".
3. If [Due date] is today, returns "Today".
4. If [Due date] is within 5 days, returns "This Week".
5. If [Due date] is in the current month, returns "This Month".
6. New Condition: If [Due date] is in next month (same year), returns "Next Month".
7. If none of the above apply, it defaults to "Future".

It woked for me as you can see in the image.

Hope this helps!

Marcela

Also, you can see the full Smartsheet Functions List here.

Cheers,

Marce!

Jess.Peace

Yes that is perfect!! Thank you so very much!!

Marcela

https://sl.bing.net/j2rzn3EWBw