Invalid Value Error with New Year

kmcdonald

Hello!  I have created a formula for 4 months after a specified date column.  The problem is, when that 4 months goes into next year, I receive the "#INVALID VALUE" error.  For example, if my formula is using the date of 08/31/19, I receive the proper formulated response of 12/31/19...but if I change the date to 09/01/19, I receive the Invalid Value error (that should give the formulated response of 01/01/20 but changing years is when I receive the error). 

Anybody know how to fix this?  This is my formula:

=DATE(YEAR([ECM Implementation]34), MONTH([ECM Implementation]34) + 4, DAY([ECM Implementation]34))     

 

Paul Newcome

The problem is that you are not adding four months to a date, you are adding four to the month number. Therefore an error is thrown after month 8 because of course there isn't a month number of 13, 14, 15, or 16.

 

To account for this we will use an IFERROR statement. This along with a slight tweak to your above formula will basically say that if there is an issue with the date when you add four to the month number, then we want to instead add one to the year and subtract eight from the month number.

 

The basic breakdown...

 

=IFERROR(DATE(year, month + 4, day), DATE(year + 1, month - 8, day))

 

If there is an error with the PRIMARY formula, then run the SECONDARY formula.

 

=IFERROR(DATE(YEAR([ECM Implementation]34), MONTH([ECM Implementation]34) + 4, DAY([ECM Implementation]34)), DATE(YEAR([ECM Implementation]34) + 1, MONTH([ECM Implementation]34) - 8, DAY([ECM Implementation]34)))