Generate annual date based on original date

Colin_Richman · ‎Jun 27, 2020

I saw a post that showed an answer that delivered almost everything I wanted and I have pasted that in below. That post has been closed hence starting a new one. However please could someone advise how to adjust the formula so that the entry is only removed from the current year and moved to the following year once the day has passed (rather than on the day itself).

For instance, using the formula below, a birthday on 27 June 2020 will disappear from the 2020 calendar on that day and appear on 27 June 2021. I would like to delay that until 28 June so I can still see the current event on the day itself (27 June in this case).

Here is the formula from the previous post that I would like to adapt. Apologies for not crediting the person who posted this formula - I have navigated away from the page and can’t find it at the moment.

I tried messing about with IS_AFTER but I am not sure if this is the right way to do it or how to insert that successfully into the formula if it is.

DATETIME_PARSE(
DAY({Birthdate}) & “-” &
MONTH({Birthdate}) & “-” &
(YEAR(TODAY()) + IF(
DATETIME_FORMAT({Birthdate}, “MMDD”) > DATETIME_FORMAT(TODAY(), “MMDD”),
0, 1)
),
“D-M-YYYY”
)

Justin_Barrett · ‎Jun 27, 2020

Welcome to the community, @Colin_Richman! :grinning_face_with_big_eyes:

IS_AFTER() won’t help in this case, as that is used to compare actual dates. The comparison in this formula that drives the change from one year to another is using a string version of parts of the dates. While it could be modified to compare actual dates, it would actually become more complex in doing so, so we won’t go that route.

First: Change TODAY() to NOW() in your formula. This is more important in the DATETIME_FORMAT() usage than the earlier one, but changing both couldn’t hurt. Here’s why that change makes a difference.

TODAY() returns a datetime object that represents the current day in GMT, with the time formatted to 12:00am (midnight). Depending on where you live relative to GMT, that will push the day value one day ahead of your own local day earlier than when the day actually changes for you.

NOW() does almost the same thing, but sets the time portion of the datetime object such that when you format it based on your own time zone, you’ll see your own local time. In theory, using NOW() will mean that the day in the formula will change at the same time that your own local day changes, instead of changing several hours sooner when the day rolls over in GMT.

The second change that will definitely help is with the date comparison. Right now it’s looking to see if the month and day of the birthday are greater than the month and day of today. If we add one day to the birthday in this comparison, then it definitely won’t change until the day after the birthday.

Here’s the revised version of the formula with both changes applied:

DATETIME_PARSE(
DAY({Birthdate}) & "-" &
MONTH({Birthdate}) & "-" &
(YEAR(NOW()) + IF(
DATETIME_FORMAT(DATEADD({Birthdate}, 1, "days"), "MMDD") > DATETIME_FORMAT(NOW(), "MMDD"),
0, 1)
),
"D-M-YYYY"
)

See Solution in Thread

Justin_Barrett · ‎Jun 27, 2020