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

Ask for Help
1

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)

2

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
3

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

4

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

2 Likes
5

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

2 Likes