Forum Discussion

Copper Contributor

Nov 23, 2023

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

excel

HansVogelaar
Nov 23, 2023
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 Replies

HansVogelaar
MVP
Nov 23, 2023
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.
- MrKasifs
  Copper Contributor
  Nov 23, 2023
  HansVogelaar
  Thank you very much for your response - close.
  - HansVogelaar
    MVP
    Nov 23, 2023
    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.