TODAY() Automated workflow birthday not calculating correctly

DLM

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:

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

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.

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.

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.

DLM

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

Paul Newcome

Happy to help. 👍️