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

  • Marcela
    Marcela Employee
    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:

    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!

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

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.

  • Stephen McDaniel
    edited 01/28/25

    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!

  • Marcela
    Marcela Employee
    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:

    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!

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Marcela
    Marcela Employee

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

    Cheers,

    Marce!

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

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

  • Marcela
    Marcela Employee

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!