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
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives