ARRAYFORMULA to calculate DATE that is one year later than the given date?

I have dates in Column X

=IF(X2<>“”, DATE(YEAR(X2)+1,MONTH(X2),DAY(X2)), “”)

gives me the date one year later as I want it (must be in a date format, so I can show in calendar view)

BUT I get an error with:

=ARRAYFORMULA(IF(X2:X<>“”,DATE(YEAR(X2:X)+1,MONTH(X2:X),DAY(X2:X)),“”))

What am I doing wrong?

Or, can I do this without using an ARRAYFORMULA? (I was able to produce the date in text format using math and template columns, but could not display the date in calendar view)

You can do this in the Glide Data Editor using Date Math. I can’t give you the formula without causing myself an aneurysm, but I’m sure @Jeff_Hager will help you out :slightly_smiling_face:

3 Likes

Check your quotes. When I copy/pasted your original, it contained “smart quotes”.

This works for me:

=ARRAYFORMULA(IF(D2:D<>"", DATE(YEAR(D2:D)+1,MONTH(D2:D),DAY(D2:D)), ""))

But regardless, much better to do this in the GDE (unless you actually need the data in the Google Sheet).

Here’s the glide way. Just plug in 1 year.

2 Likes

Thank you! I was able to calculate one year later and 11 months later :sweat_smile:

2 Likes