Date format conversion Jan 1, 2021 12:43:51 AM PST

Occasional Visitor

I have a CSV file in which the date is formatted as Jan 1, 2021 12:43:51 AM PST, I would like to convert it to a MM/DD/YYYY or MM/DD/YY format. Using the ref

1 Reply


Let's say you have such a value in A1. You can use the following horrible formula:


=DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-2)&"-"&LEFT(A1,FIND(" ",A1)-1)&"-"&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-FIND(" ",A1,FIND(" ",A1)+1)-1))


Format the cell with the formula as mm/dd/yyyy or whichever format you prefer.


It's probably easier using PowerQuery.