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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!