Convert excel datetime text to a sort-able standard datetime string

Here are a few excel datetime strings in format like “m/d/yyyy h:mm:ss am/pm”, so the length of this string is different in different date/time:

  • 2/3/2017 8:2:32 AM
  • 12/23/2017 1:20:32 PM

Searched a little bit seems there’s no result to convert these examples to a standard sortable string in format like: “yyyy-mm-ddThh:mm:ss”, have to make one

Original DateTime Converted DateTime Formula
2/17/2017 7:13:00 AM 2017-02-17T07:13:00 =TEXT(DATEVALUE(TEXT(DATE(MID(A2,FIND(" ",A2,1)-4,4),MID(A2,1,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1)),"yyyy-mm-dd"))+TIMEVALUE(MID(A2,FIND(" ",A2,1)+1,LEN(A2)-FIND(" ",A2,1))),"yyyy-mm-ddThh:mm:ss")
Advertisements