Date in date column not recognized as a date

I have a formula date column that converts string date information to a date. However, Smartsheet does not recognize the result of the formula as a date.

Original string example in column labeled SUG Class Date: 2023-07-11T14:00:00.000Z

Formula: =LEFT(REPLACE([SUG Class Date]@row, 1, 5, ""), 2) + "/" + LEFT(REPLACE([SUG Class Date]@row, 1, 8, ""), 2) + "/" + LEFT(REPLACE([SUG Class Date]@row, 1, 2, ""), 2)

Formula result: 07/11/23

Also, I tried nesting left & replace within the date function (year, month, day) but get an invalid data type error. Here's a copy of that formula: =DATE(LEFT([SUG Class Date]@row, 4), LEFT(REPLACE([SUG Class Date]@row, 1, 5, ""), 2), LEFT(REPLACE([SUG Class Date]@row, 1, 8, ""), 2))

I know the result isn't recognized as a date because when I click on "Restrict to date only" in the formula column I get an warning that some rows contain non-date values.

Please help!


  • Jaime M.
    Jaime M. ✭✭✭
    edited 07/25/23

    Hi Liz,

    Do you want just the date to appear, or do you need the time as well? Simplest way to get just the date is to first make your "Class Date" column a "Text/Number" column. In this column add the formula:

    =LEFT([SUG Class Date]@row, 10)

    Then convert the formula to a column formula. Finally, change that column type to Date, and select the style of date you want.

    Hope this helps,


  • Liz Castellaw
    Liz Castellaw ✭✭
    edited 07/25/23

    Thank you for your response! I need Smartsheet to recognize the date as a date. This is necessary because I need to use the date in a formula where I count the number of courses scheduled for the future.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!