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.
