Forum Discussion
Date format conversion Jan 1, 2021 12:43:51 AM PST
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
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.