Date IF formula providing #INVALID VALUE error

Jack Parry

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?

Paul Newcome

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)))

Paul Newcome

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)))

Jack Parry

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

Paul Newcome

That is correct. The numbers should add up to 12 but the first should be minus and the second should be plus.