Converting a date/time text format to a simple date format

Hey everyone - I am trying to convert a text field that reads 2024-09-25 15:35:27 to a simple date format, either 2024-09-25 or 9/25/24. I've tried using a helper column to just pull out the text date string - =LEFT([Registration date]@row, FIND(" ", [Registration date]@row) - 1) in the hopes that would be convertible to a date format to use in formulas, but I'm having no luck on this second part of having it seen as a date. I thought this would be a simple easy task, but alas….

Any thoughts are appreciated!!

Find more posts tagged with

Formulas

Accepted answers

Kerry St. Thomas

If you aren't able to convert by changing the column type, you can parse out the data in your datetime string. Put this in as a column formula in a column of type Date, and you should be able to move forward as usual. (The formula assumes all dates are 4 digits long, and all month and days are 2 digits long each.)

=DATE(VALUE(LEFT([Registration Date]@row, 4)), VALUE(MID([Registration Date]@row, 6, 2)), VALUE(MID([Registration Date]@row, 9, 2)))

Good luck!

All comments

Kerry St. Thomas

If the text input can't be converted with a column type change, you can use a column formula INTO a date colum that'll do it. Using this formula, you won't have to just strip off the time. Basically, treat the datetime as a text string, and then isolate the year, month, and day separately. There's an underlying assumption here - year is always 4 digits, and month and day are always 2 digits.

=DATE(VALUE(LEFT([Registration Date]@row, 4)), VALUE(MID([Registration Date]@row, 6, 2)), VALUE(MID([Registration Date]@row, 9, 2)))

Good luck!

Kerry St. Thomas

Good luck!

Kristin Stoddard

Thanks so much @Kerry St. Thomas!!! Worked beautifully!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.