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"
)
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"
)
Hi Justin,
This works perfectly. Thank you so much for your detailed explanation and for including the formula in full.
Very much appreciated and definitely not something I could have figured out on my own.
