Forum Discussion
UnSupported Date Format
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.
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 ?
