# Calculate next anniversary date

Hi,

I’m trying to calculate next anniversary or birthday date from date of original event. The formula used in Google Sheets is:

=EDATE(Anniversary!B4,CEILING.MATH(YEARFRAC(Anniversary!B4,TODAY()),1)*12) (where Anniversary!B4 is cell where original event date is recorded.

Any suggestions on cleanest method in AppSheet greatly appreciated.

Trev

Wouldn’t this just be the original date plus 1 to the year?

I tried just taking ( [Date] + 365 ) but that doesn’t include leap years, and ( [Date] + 365.25 ) is invalid; so instead I just constructed the date by extracting each element, concatenating them together again, then forcing it to the Date type.

``````DATE(Concatenate(
month([Date]), "/",
day([Date]), "/",
year([Date]) + 1
))
``````

Might need to switch the month/day around depending on your local, but this returns a usable date (meaning it’s an actual Date type) 1 year in the future.

I’m curious if there’s a cleaner way to accomplish this that someone else has a suggestion to try?

1 Like

Try:

``````(EOMONTH(([Date] - DAY([Date])), 12) + Day([Date]))
``````

Thanks for your response. This does add 1 year to the original date but not what I am trying to achieve. If original date (DOB) is e.g. 13/11/1930 it returns 13/11/1931 as next birthday whereas it should be 13/11/1920.

Your example does help me with something else though so thanks.

Trev

Also adds 1 year to original year rather than next occurrence?

Wow… I read that one wrong. I got you now; same thing, just change the year to the year of today():

`````` DATE(Concatenate(
month([Date]), "/",
day([Date]), "/",
year(TODAY())
))
``````

## Here’s a formula that works no matter your local or the format of the date

``````DATE(
Substitute(
[Date],
year([Date]),
year(today())
)
)
``````

That’s the one.

1 Like

Nearly there Works perfect if anniversary date has not occurred in current year but if it has already passed still shows last date rather than next (year) date. e.g. 23/1/1991 shows next birthday as 23/1/2020 but should be 23/1/2021.

I am grateful!!

1 Like

IF(
TODAY() < DATE(Substitute([CreationDate], year([CreationDate]), year(today()))),
DATE(Substitute([CreationDate], year([CreationDate]), year(today()))),
DATE(Substitute([CreationDate], year([CreationDate]), year(today()) + 1))
)

Hmmm… now we’re getting into some weeds. There’s got to be a cleaner way.

Would this give you the right date? I’m thinking…
(EOMONTH(([Date] - DAY([Date])), 12)
would give the end of the month. So if we took my b-day (1/19/2020) and tried this we’d get:

1. The parenthesis part would give us: 1/19/2020 - 19 days - 1/1/2020
2. Then progressed forward 12 months with EOMONTH() would give us: 1/31/2021
3. Then add back the 19 days: 2/18/2021

Yes? But you’d still need to put in a condition checking to see if we’ve already passed the b-day of this year.

1 Like

This works perfectly. I don’t mind the weeds for now

Steve - your option is dependant on reference date being last birthday - I need reference date to be the actual date including year of birth e.g. 1/23/1991.

I’m good with solution quoted above. Thanks both for your support.

2 Likes