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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!