How to add a year to my date

Hello Community,

I am trying to add a year to a date and I don’t find the right function for doing this. ¿Could anyone help me?

@analorios
I’m not aware how your date column is formatted but you can try:

TEXT([YourDateColumn], "MM/dd/yyyy") //or whatever date format you need

Hi @analorios is [Date]+365 any use to you?

2 Likes

I think maybe she means to increase the year value by 1? This is a good question. How does one do this?

But I think this won’t work with leap years.

Yes I meant to add the value by 1.

Hi @analorios,

Please explore if following expressions help

An expression something like below will give exact same day next year (Input 12/24/2019 will give 12/24/2020 as output)

IF(OR(AND(MOD(YEAR([DateColumn]), 4)=0,MONTH([DateColumn])<3),AND(MOD(YEAR([DateColumn]), 4)=3,MONTH([DateColumn])>2)), [DateColumn]+366,IF(AND(MOD(YEAR([DateColumn]), 4)=0,DAY([DateColumn])=29),[DateColumn]+364, [DateColumn]+365))

An expression something like below will give just the preevious day next year (Input 12/24/2019 will give 12/23/2020 as output)

IF(OR(AND(MOD(YEAR([DateColumn]), 4)=0,MONTH([DateColumn])<3),AND(MOD(YEAR([DateColumn]), 4)=3,MONTH([DateColumn])>2)), [DateColumn]+365,IF(AND(MOD(YEAR([DateColumn]), 4)=0,DAY([DateColumn])=29),[DateColumn]+363, [DateColumn]+364))

I tested it for border cases like 28 Feb, 29 Feb, 1st March, leap , no leap year scenarios as shiwn below but the expressions could still be tested more, especially if there are some critical calculations based on the date.

In the testing sample below, [RepairDate] is input and NextYear and NextYear2 are outputs based on the two expressions mentioned above.

1 Like

Yes! This solution works great even with the border cases. Thank you so much

2 Likes

Yikes! We definitely need a better way to increase the individual date components. Sounds like a feature request. Who volunteers to open one?