TODAY() Automated workflow birthday not calculating correctly

We've automated a work flow for birthday notifications - so we can celebrate folks. The workflow/notification shows an incorrect calculation - age = 20, when it should be 21. Here's the notification:

Screen Shot 2021-08-18 at 10.31.47 AM.png

And here's the data we're pulling from, showing the correct age calculation:

Screen Shot 2021-08-18 at 10.33.57 AM.png

I'm assuming this may have something to do with the TODAY() function?

Any advice is welcome! Here's the age calculation formula we're using: =(YEAR(TODAY()) - YEAR(Birthdate@row)) - IF(AND(MONTH(TODAY()) <= MONTH(Birthdate@row), DAY(TODAY()) < DAY(Birthdate@row)), 1)

Also interesting, the cell changes when various folks open the sheet, or reports connected to the sheet.

Screen Shot 2021-08-18 at 10.45.40 AM.png

Find more posts tagged with

Formulas

Automations

Email Notifications

Accepted answers

Paul Newcome

So I am thinking that it is in fact related to the TODAY function. The TODAY function only updates when the sheet has been activated, but the automation could still be running (which is why the sheet never updated to 21, and the automation sent out 20).

My suggestion would be to insert a date type column (called "Today") then set up a record a date automation. Set the trigger to run every day at 12:00am and the condition should be where the primary column is blank or where the primary column is not blank.

Then replace all of the references to

TODAY()

with

Today@row

Then in your "Happy Birthday" automation, set the trigger to be when rows are changed where the [Age] column changes to any value.

All comments

Paul Newcome

Then replace all of the references to

TODAY()

with

Today@row

Then in your "Happy Birthday" automation, set the trigger to be when rows are changed where the [Age] column changes to any value.

DLM

@Paul Newcome much appreciated, this was the perfect solution!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.