Forum Discussion
Solved
UnSupported Date Format
Hello, I have a data file where the date " field"of the CSV file contains the Data and Time : Sep 9, 2022 12:13:48 PM EDT obviously Excel would not support such a format - so i am guessing I will have...
The formula works in Excel in Microsoft 365.
For older versions:
For the date:
=--(MID(D1,FIND(" ",D1)+1,FIND(",",D1)-FIND(" ",D1)-1)&"-"&LEFT(D1,3)&"-"&MID(D1,FIND(",",D1)+2,4))
For the time
=--MID(D1,FIND(",",D1)+7,12)
Don't forget to format the cells as date and time, respectively.
The formula works in Excel in Microsoft 365.
For older versions:
For the date:
=--(MID(D1,FIND(" ",D1)+1,FIND(",",D1)-FIND(" ",D1)-1)&"-"&LEFT(D1,3)&"-"&MID(D1,FIND(",",D1)+2,4))
For the time
=--MID(D1,FIND(",",D1)+7,12)
Don't forget to format the cells as date and time, respectively.
- I did not expect you to develop the *answer*, but thank you very much.
Rather tedious to pick out each piece of the date portion...
( Imagining to myself )
You would think that it would not be That difficult for MS to have developed a system that works on a * Mask* that you could use to define what your Date / Time Field "Looks like" and based on that Edit mask it could understand Month, Date HH MM SS YYYY portions of your data ?Power Query might be able to do it, but I'm not an expert on that.