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:
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.
Best Answer
-
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.
Answers
-
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 much appreciated, this was the perfect solution!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!