UnSupported Date Format

[MrKasifs]

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 to write something to cut this into two fields to make it work ? Or can you add a New Data Format ? Thanks,

[#2]

@MrKasifs 

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.

[#3]

@HansVogelaar 
Thank you very much for your response - close. 

 

[#4 · Accepted solution]

@MrKasifs 

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.

[#5]

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 ?

[#6]

@MrKasifs 

Power Query might be able to do it, but I'm not an expert on that.