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.
After opening the CSV file in Excel:
Let's say the relevant values are in D1 and down.
In another column in row 1:
=LET(p,TEXTSPLIT(D1," "),DATEVALUE(SUBSTITUTE(INDEX(p,2),",","-")&INDEX(p,1)&"-"&INDEX(p,3)))
Format the cell with this formula as a date.
In yet another cell:
=LET(p,TEXTSPLIT(D1," "),TIMEVALUE(INDEX(p,4)&" "&INDEX(p,5)))
Format the cell with this formula as a time.
Fill down.
