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

[Snaseer]

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

[#2]

@Snaseer 

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.