Add 10 years to a date

[beckythecoolest]

Hi Everyone!

I was wanting to find a way to add 10 years to a date and found a simple way that works! In my formula, the manufacture date is the start date and I'm using this in a field to calculate the 10-year shelf life.

Here it is:

(EOMONTH([Manufacture Date],120))-(HOUR(EOMONTH([Manufacture Date],0) - [Manufacture Date]) / 24)

A lot of other expressions I tried ended up being super complicated and didn't return the right values. 

Hopefully this helps someone else too 🙂

[Marc_Dillon]

I'm very curious. What are your exact requirements for your +10 years calculation that led you to this exact expression?
What other expressions did you find and how did they not match, or how were they too complicated?
What about: [Date] + 365*10 ?
Or: MONTH([Date]) & '/' & DAY([Date]) & '/' & YEAR([Date])+10

[Suvrutt_Gurjar · Accepted solution]

Another version of expression could be 

EOMONTH([Date], 119)+DAY([Date])

Hi Marc: Just a small point that I think the expression MONTH([Date]) & '/' & DAY([Date]) & '/' & (YEAR([Date])+10)  may give error on one edge case of 29th Feb in a leap year. Addition of 10 years will not be a leap year and it will give date as 29th Feb in a non leap year.

The expression EOMONTH([Date], 119)+DAY([Date])  will roll over to 1st March for the 10 year after date if the [Date] is 29th Feb in a leap year as there is simply no 29th Feb in a non leap year.

[beckythecoolest]

Hi Marc, I have some products that have a 10-year shelf life so I have a column "Shelf life ends" with this formula that calculates 10 years from the date I add in the "Manufacture Date" column. But, I only want the year to change, not the month and day.

So, if something is manufactured 3/11/2022, I want the shelf life ends date to show 3/11/2032. 

Most expressions I found calculated using EOMONTH() or other variations that would change the month/day.

This isn't a perfect formula for adding 10 years exactly, but it adds 10 years to the year.

I hadn't tried the second you suggested but I can!

[Sirfyaad]

DATE(YEAR([Manufacture Date]) + 10, MONTH([Manufacture Date]), DAY([Manufacture Date]))

In this expression, [Manufacture Date] represents the start date or the date of manufacture. The expression calculates the adjusted date by adding 10 to the year of the manufacture date while keeping the same month and day.

[beckythecoolest]

Oh that's so much simpler. Thank you!!!

[Neeraj_Malik]

Hi @Sirfyaad 

I'm using the following similar expression in line with what you suggested

Date(YEAR([Investment_Date]) + [Tenor], MONTH([Investment_Date]), DAY([Investment_Date]))

However, it's not working for me and the error of Date function is used incorrectly. Can you help on this pls?

[Marc_Dillon]

@Neeraj_Malik 

See my response here

[Neeraj_Malik]

Thanks for pointing to right place @Marc_Dillon, following expression based on recommendation from @Nantha worked for me

DATE((YEAR([Investment_Date]) + [Tenor]) & "-" & MONTH([Investment_Date]) & "-" & DAY([Investment_Date]))

[SkrOYC]

---

@Suvrutt_Gurjar wrote:

EOMONTH([Date], 119)+DAY([Date])

---

💯

[marinho]

I got an error when using the function:

DATE(YEAR([Manufacture Date]) + 10, MONTH([Manufacture Date]), DAY([Manufacture Date]))

but your solution worked.
eu precisava somar 5 anos a uma data de fechamento de um certificado e subtrair da data atual para obter um leadtime.
Usei a seguinte formula:

HOUR((EOMONTH([Data de FECHAMENTO],60))-(HOUR(EOMONTH([Data de FECHAMENTO],0) - [Data de FECHAMENTO]) / 24)- TODAY()) / 24

[Marc_Dillon]

---

@marinho wrote:

I got an error when using the function:

DATE(YEAR([Manufacture Date]) + 10, MONTH([Manufacture Date]), DAY([Manufacture Date]))

---

Because that's the syntax for the DATE() function in GSheets. Appsheet cannot do that. It should not be marked as the solution to this thread.

[Nantha]

I was just facing a similar issue and tried using ChatGPT and it gave me this. Seems to be working for me.

DATE(
(YEAR([Depreciation Start]) + 10) & "-" & MONTH([Depreciation Start]) & "-" & DAY([Depreciation Start])
)

[SkrOYC]

EOMONTH(
  [DATE],
  119
)+
DAY([DATE])

This hasn't work for some reason to you all?

[Marc_Dillon]

Yah, this should be the actual marked solution on this thread. @lizlynch or @Roderick  , can you help us out and mark @Suvrutt_Gurjar solution here as the solution for this thread, and remove sirfyaad's, to avoid any further confusion?

[lizlynch]

Done! Let me know if it doesn't reflect what you expected!