I get an invalid value error when trying to calculate dates

Janet Skjerven

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

Genevieve P.

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