edited 12/09/19

Hi all

I am trying to automatically add a 6 month review date to a start date, it is working fine until a start date is past June, to which I get a #invalid value error.   The formula I am using is:

=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))

So 1/6/19 becomes 1/12/19 but if I try with 1/7/19 I get #invalid value instead of 1/1/20.

Can anyone help????

Many thanks

Issy

• ✭✭✭✭✭✭

@Andrew Speer I have actually developed a slightly different approach. It is very similar but with a few tweaks to make it easier to scale and adjust.

=IFERROR(DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)), DATE(YEAR(A2) + 1, MONTH(A2) - 6, DAY(A2)))

• ✭✭✭✭✭✭

@azerty64 18 months is basically 1 year and 6 months. You should be able to use the solution I posted on 23 June 2020 when I tagged Andrew but add 1 to each of the YEAR functions.

• ✭✭✭✭✭✭

Try replacing A2 with [Title of Header for that column]2

Smartsheet refers to columns by their header title.

• ✭✭✭✭✭✭

That is because the DATE function is trying to produce the date of 1/13/19 with the 13 coming from the month 7 plus 6. You will need to use an IFERROR statement. Something along the lines of...

=IFERROR(DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)), =DATE(YEAR(A2) + 1,(MONTH(A2)+6) - 12,DAY(A2)))

Basically this says to run your normal formula. If there is an error (caused by incorrect month numbers, it will run the other formula. The other formula basically subtracts 12 from the product of month number plus six which in your above example turns 13 into 1. It then adds those 12 months to the year which kicks it into next year.

There are a few other ways to accomplish this, but this was the first one I thought of. Hope this helps.

• Hi Paul, thanks for your advice, I'm trying to achieve the same as IssyA.

Unfortunately I'm still coming up with error #INVALID OPERATION for months 7-12 based on this formula.

=IFERROR(DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)), =DATE(YEAR(A2) + 1,(MONTH(A2)+6) - 12,DAY(A2)))

Is there a slight error in the formula somewhere, or is it just me?

• ✭✭✭✭✭✭

@Andrew Speer I have actually developed a slightly different approach. It is very similar but with a few tweaks to make it easier to scale and adjust.

=IFERROR(DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)), DATE(YEAR(A2) + 1, MONTH(A2) - 6, DAY(A2)))

• ✭✭✭✭

@Paul Newcome What if I wanted to add 24 months to a date?

• ✭✭✭✭✭✭

@MikeS If you want to add 24 months to a date then you just need to add two years.

• ✭✭✭✭

Thanks @Paul Newcome. I guess it to was too late for me to keep working last night. That worked perfectly this morning.

• ✭✭
edited 09/22/22

Hello and Sorry @Paul Newcome But : What if I wanted to add 18 months to a date?

• ✭✭✭✭✭✭

@azerty64 18 months is basically 1 year and 6 months. You should be able to use the solution I posted on 23 June 2020 when I tagged Andrew but add 1 to each of the YEAR functions.

• ✭✭

Perfect Thank you !

• ✭✭✭✭✭✭

@azerty64 Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!