Calculate next anniversary date

Trevor_Jenkins1 · 02-09-2020 09:32 PM

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

MultiTech

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?

Steve

Try:

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

Trevor_Jenkins1

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

Trevor_Jenkins1

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

MultiTech

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.

Trevor_Jenkins1

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

MultiTech

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:

The parenthesis part would give us: 1/19/2020 - 19 days - 1/1/2020
Then progressed forward 12 months with EOMONTH() would give us: 1/31/2021
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.

Trevor_Jenkins1

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.

crossmark

Hmmm... I'm getting an error: Wrong number of arguments to DATE. Expected 3 arguments, but got 1 arguments.

Steve

All of the formulas in the responses above are for use in AppSheet, where DATE() expects only a single argument. Sounds like you're using a spreadsheet formula, where DATE() expects three arguments.

graham_howe

Just to be pedantic 😉 what happens if the birthday/anniversary is on the 29th February? I'm guessing most of the 'change the year' type responses would fail in that instance?