Formula used to populate date column won't trigger date based automations

I am using a formula-generated date in a "Date column" to run an "alert someone" automation as a reminder for follow-up.


I am using this automation setup set to alert me when the date is today.


When I hand enter a date in the column the automation works as expected.

I believe this is a result of the automation still recognizing the value in the cell as text and not a date.

I have tried several helper columns pulling in the value as "=DATE" and "=VALUE" but nothing has worked so far.

I even tried using an "IF" statement to give me a check box if "Date = "TODAY()" and it would not recognize that I was looking at 2 dates.

Any help is appreciated.

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Smanning

    you need to use the DATE function to convert the information into a 'Date'.

    the date function requires each Part of the date (Y,M,D) to be brought in separately as a number.

    To do this, for each section you use the 'VALUE' function top convert a section of the text into a number.

    Below is your formula:

    =DATE(VALUE(RIGHT([Date Reminder]@row,4)),VALUE(MID([Date Reminder]@row, FIND(",", [Date Reminder]@row) + 1, 2)),VALUE(MID([Date Reminder]@row, FIND(",", [Date Reminder]@row) + 4, 2)))

    Please note this assumes the layout of 'Date Reminder' column always stays the same

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Smanning

    you need to use the DATE function to convert the information into a 'Date'.

    the date function requires each Part of the date (Y,M,D) to be brought in separately as a number.

    To do this, for each section you use the 'VALUE' function top convert a section of the text into a number.

    Below is your formula:

    =DATE(VALUE(RIGHT([Date Reminder]@row,4)),VALUE(MID([Date Reminder]@row, FIND(",", [Date Reminder]@row) + 1, 2)),VALUE(MID([Date Reminder]@row, FIND(",", [Date Reminder]@row) + 4, 2)))

    Please note this assumes the layout of 'Date Reminder' column always stays the same

  • Worked like a charm. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!