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.
Best Answer
-
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))))
Answers
-
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))))
-
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.
-
Happy to help. 👍️
Out of curiosity... How many closing parenthesis were automatically tacked onto the end?
-
Same as yours, but it wouldn't process the formula until I did it
-
That's odd. At least it is working for you now.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!