Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion

    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.

  • Community Champion

    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

    1. A text column named "Year" with the formula below. This will extract the year from the date.
      =RIGHT(DaT@row, 4)
    2. 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)
    3. 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))))))))))))
    4. 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", "")
    5. 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/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6