How to add a year to my date

[analorios]

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?

[LeventK]

@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

[WillowMobileSys]

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

[analorios]

Yes I meant to add the value by 1.

[Lynn]

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

[analorios]

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

[Suvrutt_Gurjar · Accepted solution]

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.

[analorios]

Suvrutt_Gurjar:

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

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

[WillowMobileSys]

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

[analorios]

I created the feature request. Please vote it in the link. Maybe this way they implement it soon. Thank you!