Text to Date with variable month/day numbers

AFlint

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.

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))))

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))))

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.