How to add a year to my date

AppSheet AppSheet Q&A
1

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?

2

@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
3

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

2 Likes
4

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

5

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

6

Yes I meant to add the value by 1.

7

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.

2X_8_8cc3d28fe6f597e806b96c151a159324c5f6542b.png
2X_8_8cc3d28fe6f597e806b96c151a159324c5f6542b.png293×643 3.66 KB

1 Like
8

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

2 Likes
9

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

10

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