I get an invalid value error when trying to calculate dates
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve, I knew it was a user error, but I just couldn't see it.
Thanks!
Janet
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!