TODAY() Automated workflow birthday not calculating correctly

DLMDLM ✭✭✭✭✭
08/18/21
Accepted

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([email protected])) - IF(AND(MONTH(TODAY()) <= MONTH([email protected]), DAY(TODAY()) < DAY([email protected])), 1)

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


Best Answer

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

    [email protected]


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

    thinkspi.com

Answers

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

    [email protected]


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

    thinkspi.com

  • DLMDLM ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.