I get an invalid value error when trying to calculate dates

Options

This is an unusual problem. Based on the effective date, I am creating notify dates for procedures that are due for a review. The review cycles are 6, 12, 18, and 24 months and converted to .5, 1, 1.5 and 2 to simplify the math in the DATE function. The notify date is then used to trigger an automation.

My formula works for every day, month, year except for the first day of the month. For example, an effective date of 2/1/22 throws the invalid value error no matter review cycle value is applied, but an effective date 2/2/22 calculates the notify date correctly. My formula is below. I'm sure it is user error, maybe another set of eyes can see the problem.

=IF([Review Cycle Helper]@row = 0.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row) - 1), DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row) - 1)), IF([Review Cycle Helper]@row = 1, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row), DAY([Effective Date]@row) - 1), IF([Review Cycle Helper]@row = 1.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row) - 1), DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row) - 1)), IF([Review Cycle Helper]@row = 2, DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row), DAY([Effective Date]@row) - 1)))))

thanks

Janet

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Janet Skjerven

    The reason you're receiving an error is because you have a -1 within your DAY functions:

    DAY([Effective Date]@row) - 1)

    This will subtract 1 from your DAY in the Date field, so if you have 2/1/22, it turns it into 2/0/22, which is not a valid date.

    Instead, you can subtract 1 day by taking that day away from the entire date that you've created. So, from your original:

    DATE(YEAR(), MONTH(), DAY(- 1))

    To:

    DATE(YEAR(), MONTH(), DAY()) - 1

    Does that make sense?


    Try moving the -1 outside of one parentheses:

    =IF([Review Cycle Helper]@row = 0.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row)) -1, DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row)) -1), IF([Review Cycle Helper]@row = 1, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row), DAY([Effective Date]@row)) - 1, IF([Review Cycle Helper]@row = 1.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row)) - 1, DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row)) - 1), IF([Review Cycle Helper]@row = 2, DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row), DAY([Effective Date]@row)) - 1))))


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Janet Skjerven

    The reason you're receiving an error is because you have a -1 within your DAY functions:

    DAY([Effective Date]@row) - 1)

    This will subtract 1 from your DAY in the Date field, so if you have 2/1/22, it turns it into 2/0/22, which is not a valid date.

    Instead, you can subtract 1 day by taking that day away from the entire date that you've created. So, from your original:

    DATE(YEAR(), MONTH(), DAY(- 1))

    To:

    DATE(YEAR(), MONTH(), DAY()) - 1

    Does that make sense?


    Try moving the -1 outside of one parentheses:

    =IF([Review Cycle Helper]@row = 0.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row)) -1, DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row)) -1), IF([Review Cycle Helper]@row = 1, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row), DAY([Effective Date]@row)) - 1, IF([Review Cycle Helper]@row = 1.5, IFERROR(DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) + 6, DAY([Effective Date]@row)) - 1, DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row) - 6, DAY([Effective Date]@row)) - 1), IF([Review Cycle Helper]@row = 2, DATE(YEAR([Effective Date]@row) + 2, MONTH([Effective Date]@row), DAY([Effective Date]@row)) - 1))))


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Janet Skjerven
    Options

    Thanks Genevieve, I knew it was a user error, but I just couldn't see it.

    Thanks!

    Janet

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!