how to convert a text date to a date

MarkCep

I have a column that brings in a date and time

ex 08/22/2024 05:30:22 PM

I want to extract the date portion into a date column, but I keep getting a #invalid Operations.

DATEONLY will not work because this is not a system generated date, it is coming in from another system.

=LEFT([Inquiry Date]@row, 10)

does not work

Help

Find more posts tagged with

Accepted answers

All comments

Matt Johnson

Hi @MarkCep

Your situation is probably different than the test I just set up but…if the column that's being populated from another system is Text/Number and the column that needs the date is a date column, the LEFT formula you posted worked for me in the date column.

Given your star ranking, I figure something else is going on but just wanted to pass on my test results.

Matt

MarkCep

Thanks Matt, I was having a brain block but figured it out.

Working formula:

=DATE(VALUE(MID([Inquiry Date]@row, 7, 4)), VALUE(MID([Inquiry Date]@row, 1, 2)), VALUE(MID([Inquiry Date]@row, 4, 2)))

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.