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?
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())
))
DATE(
Substitute(
[Date],
year([Date]),
year(today())
)
)
That’s the one.
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!!
Trevor_Jenkins:
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.
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.
Steve:
(EOMONTH(([Date] - DAY([Date])), 12) + Day([Date]))
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:
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.
MultiTech_Visions:
IF(> TODAY() < DATE(Substitute([CreationDate], year([CreationDate]), year(today()))),> DATE(Substitute([CreationDate], year([CreationDate]), year(today()))),> DATE(Substitute([CreationDate], year([CreationDate]), year(today()) + 1))> )
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.
Hmmm… I’m getting an error: Wrong number of arguments to DATE. Expected 3 arguments, but got 1 arguments.
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.
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?