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. :laughing: 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. :wink:

1 Like

Nearly there :wink: 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

:slight_smile:

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 :smiley:

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