How to convert UTC to typical date format in Excel?

Question

I have this UTC column called Timestamp. It looks like this.

2019-06-27T23:01:12.1024861Z

How do I convert it so it looks like this in Excel?

06/27/2019 23:01:12

If there is a formula that would be best please leave it below as it must be applied to all cells in column

Answer 1

Let's assume the input time is in cell A1. then you can convert it to Excel date and time value using the following formulae,

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

What this is doing is getting the date portion of the input using LEFT(A1,10) and converting it into DATEVALUE and getting the time portion using MID(A1,12,8) and converting it into TIMEVALUE. Add them together and you get date and time.

You can use Excel custom format and use the following:

mm/dd/yyyy HH:mm:ss

Answer 2

You can use REPLACE to remove the T and Z:

=--REPLACE(REPLACE(A1,28,1,""),11,1," ")

Then format as desired.

Answer 3

for german excel users that should help:

cell F6: 2022-10-05T05:58:43.833Z

=DATWERT(LINKS(F6;10))+ZEITWERT(TEIL(F6;12;8))

formate cell: TT.MM.JJJJ hh:mm:ss

result: 05.10.2022 05:58:43

further to get unix timestamp:

=(M21-DATUM(1970;1;1))*86400 

formate cell as regular number

result: 1664949523