Convert Date/Time format in Excel-CodePudding

I have the following date/time format in many Excel sheets: 2021-11-23T23:49:34.000 0000

However, it is very bulky. It's ugly to use in graphs and you can't really do any date/time math with it. Is there a good wat to format this? Maybe something like 11/23/2021 23:49:34. I've tried different cell formulas as well as general number formatting.

CodePudding user response：

=DATEVALUE(LEFT(A1,10))
 TIMEVALUE(MID(A1,12,8))

The first part grabs the date, the second part grabs the time. If you need the microseconds, use this:

=DATEVALUE(LEFT(A1,10))
 TIMEVALUE(MID(A1,12,12))

If you need to handle the time offset ( 0000) then you can add these parts:

=DATEVALUE(LEFT(A1,10))
 TIMEVALUE(MID(A1,12,12))
 TIMEVALUE(CONCAT(MID(A1, 25, 2), ":", MID(A1, 27, 2)))
*IF(MID(A1, 24,1)=" ", 1, -1)

where the third line formats the offset into a timevalue and the fourth line captures the sign.