Alert based on age

Deanna Hutchison

Hello, we are trying to create an alert when a person turns 26. We've got a column in our Smartsheet which automatically calculates ages based on dates of birth. Additionally, we put in a workflow to alert someone when a person turns 26.00. We've tested it numerous times, but have failed to receive the alerts. Is it something to do with the decimal points? Any suggestions?

Paul Newcome

Are you able to provide a screenshot of the alert?

My personal suggestion would be to insert a date type column that adds 26 years to the DOB and then set the automation up to run based on that "26th bday" column.

=DATE(YEAR(DOB@row) + 26, MONTH(DOB@row), DAY(DOB@row))

That should output the date of their 26th bday.

Deanna Hutchison

Hey, your suggestion sounds better than our attempt.

We have a column that calculates the age using the formula

=(TODAY() - [Member Date of Birth]@row) / 365

Here's the screenshot of the notification:

Paul Newcome

The alert looks like it should be working assuming the row(s) meet the criteria for the Conditions section. Another thing to think about... If there is any kind of rounding happening then it may be skewing your data. If you adjust the column formatting to only show two decimal places, it may DISPLAY 26.00, but on the back-end (for example) is stored 25.999 or 26.001 which wouldn't trigger your automation.

Additionally, the TODAY function will not update until the sheet is activated. This means that if it is activated on the first and third of the month but there is a bday on the second, it never actually updated to 26.00 because the TODAY function wasn't updated on the actual date that it needed to be updated on to trigger the automation.

One final thing to think about (and this is completely untested on my part)... If you are the one logged in when the sheet is first opened then I believe that technically you are the one that made the update to the sheet to trigger the automation. If that is the case then it could also be that your personal settings are not set up to trigger automations based on your own actions.

I still recommend having a column to output the date and then have the trigger set up to run based on that date column. This way you don't have to worry about rounding, the TODAY function, or who activated the sheet/triggered the automation. It will just run.

Deanna Hutchison

Thanks, Paul. We've tested it numerous times with different rounding and no alerts have come through. Your untested theory is correct as we've learned if we need to test something, we need the alert to go to another colleague. My colleague, Rachel was imputing the changes with the alerts going to me.

I do like your suggestion and am trying to implement it but I'm getting an invalid error with the formula. Can you see what I may be doing wrong?

Paul Newcome

f you go into your personal settings, you can change it so that your actions do in fact trigger alerts to yourself.

As for the error... There are a couple of them that start with #INVALID. Are you able to tell me invalid what exactly? That can make a difference in where we start with trouble shooting.

My first thought is that either one or both of the columns are not set as date type columns. If they are, exactly how is the birthday entered?

Deanna Hutchison

Yep, changing that column to date worked! Thank you. However, we are still struggling on how to set up an automation based on the age they turn 26. Any thoughts?

Paul Newcome

Change the trigger to "When a date is reached" and then select the option to have it based on a date field. You should be able to then select the 26th bday column.

Deanna Hutchison

Okay, we'll try that. Thank you!

Paul Newcome

Happy to help. 👍️