Date IF formula providing #INVALID VALUE error
Hi, I'm in the process of setting up a sheet to alert myself and my team 3 months before something is due based on specific criteria. The formula works for some cells but not all of them.
Formula being used for 'Review Target - 3months':
=IF(GAMP@row = 1, DATE(YEAR([Last Activity]@row) + 5, MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), IF(GAMP@row = 3, DATE(YEAR([Last Activity]@row) + 4, MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), IF(GAMP@row = 4, DATE(YEAR([Last Activity]@row) + 3, MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), IF(GAMP@row = 5, DATE(YEAR([Last Activity]@row) + 2, MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row))))))
Formula being used for 'Review Target':
=IF(GAMP@row = 1, DATE(YEAR([Last Activity]@row) + 5, MONTH([Last Activity]@row), DAY([Last Activity]@row)), IF(GAMP@row = 3, DATE(YEAR([Last Activity]@row) + 4, MONTH([Last Activity]@row), DAY([Last Activity]@row)), IF(GAMP@row = 4, DATE(YEAR([Last Activity]@row) + 3, MONTH([Last Activity]@row), DAY([Last Activity]@row)), IF(GAMP@row = 5, DATE(YEAR([Last Activity]@row) + 2, MONTH([Last Activity]@row), DAY([Last Activity]@row))))))
Not sure why its working for some cells and not others?
Best Answer
-
It is because of the month portion of your DATE functions and the date itself. You are basically saying to use the month of (1 - 3) which is negative two. Negative Two is not a valid month number. Try this instead...
=IFERROR(DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))), MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))) - 1, MONTH([Last Activity]@row) + 9, DAY([Last Activity]@row)))
Answers
-
It is because of the month portion of your DATE functions and the date itself. You are basically saying to use the month of (1 - 3) which is negative two. Negative Two is not a valid month number. Try this instead...
=IFERROR(DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))), MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))) - 1, MONTH([Last Activity]@row) + 9, DAY([Last Activity]@row)))
-
thanks @Paul Newcome , this worked!
And I can change the values of the reminder for the month on these parts right?
=IFERROR(DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))), MONTH([Last Activity]@row) - 3, DAY([Last Activity]@row)), DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))) - 1, MONTH([Last Activity]@row) + 9, DAY([Last Activity]@row)))
e.g. for 1 month, it would be:
=IFERROR(DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))), MONTH([Last Activity]@row) - 1, DAY([Last Activity]@row)), DATE(YEAR([Last Activity]@row) + IF(GAMP@row = 1, 5, IF(GAMP@row = 3, 4, IF(GAMP@row = 4, 3, IF(GAMP@row = 5, 2)))) - 1, MONTH([Last Activity]@row) + 11, DAY([Last Activity]@row)))
Thanks,
Jack
-
That is correct. The numbers should add up to 12 but the first should be minus and the second should be plus.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives