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!

Find more posts tagged with

Accepted answers

All comments

Jaime M.

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,

Jaime

Liz Castellaw

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.