Text to Date with variable month/day numbers

I have a data source that is exporting date values as text strings. To make it worse, it is using M/D/YYYY as the format. This means that I have getting dates like this:

5/1/2022

5/10/2022

10/1/2022

10/10/2022

I am familiar with how to convert a MM/DD/YYYY string into a date, but I'm not sure how to adapt the formula for the variable digit count for Month and Day. I recall seeing something once that used the FIND function to identify the /, but I can't find it again after quite a bit of searching.

Thank you in advance for any help.

Find more posts tagged with

Accepted answers

Paul Newcome

You are going to want something like this...

=DATE(VALUE(RIGHT([Date Column]@row, 4)), VALUE(LEFT([Date Column]@row, FIND("/", [Date Column]@row) - 1)), VALUE(MID([Date Column]@row, FIND("/", [Date Column]@row) + 1, FIND("/", [Date Column]@row, FIND("/", [Date Column]@row) + 1) - (FIND("/", [Date Column]@row) + 1))))

All comments

Paul Newcome

You are going to want something like this...

AFlint

Thank you @Paul Newcome

The formula worked, but only after I deleted the end ))) and allowed the formula to reset. Small price to pay, and thank you for the help.

Paul Newcome

Happy to help. 👍️

Out of curiosity... How many closing parenthesis were automatically tacked onto the end?

AFlint

Same as yours, but it wouldn't process the formula until I did it

Paul Newcome

That's odd. At least it is working for you now.

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.