How can I format a string into a date: time format?

Question

For example: 2021-08-18T22:24:49-06:00

I want to print this to a more readable format like: 8/18/21 10:24pm

I have tried using the built in DateTime function but it returns an error. Can someone point me in the right direction? I have checked other answers but they all relate to using the aforementioned funciton.

Answer 1

Looking at how your data is formatted and it seems your data is formatted "yyyy-mm-ddThh:mm:ss";so, here is my attempt:

Formula in C1:

=--SUBSTITUTE(LEFT(A1,16),"T"," ")

Then I just formatted the resulting datetime-stamp with:

m/d/yy hh:mm AM/PM

So it remains a numeric value to do your calculations with if needed.

Answer 2

The date is in ISO 8601 format. This will parse out the different parts of the date string and convert to a date, assuming that your string is in A1:

=DATEVALUE(LEFT(A1,10))
+TIMEVALUE(MID(A1,12,8))
+TIMEVALUE(RIGHT(A1, 5))
*INT(MID(A1, 20, 1) & 1)

The first part grabs the date, the second part grabs the time, the third part captures the date offset, and the last part captures the sign. If you want to format that, you can do it with the cell formatting or wrap it in TEXT:

=TEXT(
     DATEVALUE(LEFT(A1,10))
    +TIMEVALUE(MID(A1,12,8))
    +TIMEVALUE(RIGHT(A1, 5)) 
    *INT(MID(A1, 20, 1) & 1),
    "yyyy-mm-dd hh:mm:ss"
)

Note that if you need to support UTC, that is indicated by Z instead of a time offset, and you would need to modify the formula slightly. If your data always has the same time offset, you could just hardcode it, instead of parsing it out.

Answer 3

Give a try to below formula-

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"T","</s><s>")&"</s></t>","//s[1]")+FILTERXML("<t><s>"&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A1,"T","</s><s>")&"</s></t>","//s[2]"),"-","</s><s>")&"</s></t>","//s[1]"),"M/dd/yyyy hh:mm AM/PM")