Date Range in Date Column + Automation
Hello!
I have an automation that currently sends users alerts based on the date in a column called "End Date". A majority of the time, the end date is a single date. However, sometimes the end date can be two or more days (ie- May 7th-8th). While I can write into the date column ="May 7th-8th", that will prevent my automation from running off the end date since now it's a text field.
Any way to write a formula in the end date that can capture a date range still in date format so my automation can run as currently set up?
Answers
-
No. Your date column will need to house a single date. You can create a secondary text/number column to enter multiple dates in addition to the first date in the date type column.
-
Hi @M_FLIGHT
If your date range has a standard format, and assuming that your date column is named Date, you can use this formula
=MID(Date@row, FIND("-", Date@row) + 1, LEN(Date@row) - FIND("-", Date@row) - 2)
But this will return you only the numerical date, without any month or year, which will again fail the automation. When you type in May 7th-8th, you are in fact not putting in a date in the date column. Smartsheet considers that as a simple text.
As a workaround, you first need to ensure that the DT column contains the month, date and year, in a standard format always. For example, May 7th-8th, 2025.
You then need a few helper columns as below
- A text column named "Year" with the formula below. This will extract the year from the date.
=RIGHT(DaT@row, 4)
- A text column named "Month" with the formula below. This will extract the month from the date. Ensure that the month name is written full, not abbreviated.
=LEFT(DT@row, FIND(" ", DT@row) - 1)
- A text column named "MonthNumber". This will convert the Month text into month number
=IF(LOWER(Month@row) = "january", 1, IF(LOWER(Month@row) = "february", 2, IF(LOWER(Month@row) = "march", 3, IF(LOWER(Month@row) = "april", 4, IF(LOWER(Month@row) = "may", 5, IF(LOWER(Month@row) = "june", 6, IF(LOWER(Month@row) = "july", 7, IF(LOWER(Month@row) = "august", 8, IF(LOWER(Month@row) = "september", 9, IF(LOWER(Month@row) = "october", 10, IF(LOWER(Month@row) = "november", 11, IF(LOWER(Month@row) = "december", 12, 0))))))))))))
- A text column named "Day". This will extract the day number.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(RIGHT(DT@row, LEN(DT@row) - FIND("-", DT@row)), FIND(",", RIGHT(DT@row, LEN(DT@row) - FIND("-", DT@row)) + ",") - 1), "th", ""), "st", ""), "nd", ""), "rd", "")
- Finally, a date column that contains the converted date.
=DATE(VALUE(Year@row), VALUE(MonthNumber@row), VALUE(Day@row))
The last column should be a Date column and you can format it to mm/dd/yyyy or any other format based on your needs.
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ - A text column named "Year" with the formula below. This will extract the year from the date.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!