CODEWITHSUNDEEP
excel
Unbreakable
Unbreakable

Reputation: 8084

How to convert UTC to datetime in Excel

I am trying to convert UTC to datetime in EXCEL SHEET.

Below is the input

2020-04-10T22:15:40.5577209Z

So far to convert I have put below formula

=(SUBSTITUTE(A2,"T"," "))

This gives me output as 

2020-04-10 22:15:40.5577209Z

Now I need to trim the .5577209Z part.

Final output should be

2020-04-10 22:15:40

Can someone help me with this?

Upvotes: 2

Views: 2458

Answers (1)

Variatus
Variatus

Reputation: 14373

The result you want would be produced with a formula like this one. (The original string is in A2)

=DATEVALUE(LEFT((SUBSTITUTE(A2,"T"," ")),18))+ TIMEVALUE(LEFT((SUBSTITUTE(A2,"T"," ")),18))

The formula takes the first 18 characters of the received string. If you are sure that there are no letters in the next couple of digits you could extend this to 20 or more and get a better rounding of the seconds.

Another approach would be to work around the offending "T".

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

Again the final 8 in the formula could be extended for better rounding of seconds. Or, if it's always a "Z" it could simply be removed like you already removed the "T".

Either solution gives you a true Date/Time value - a 5-digit number with lots of decimals. This you convert into a legible date by means of the cell format. Apply a custom format like yyyy-mm-dd hh:mm:ss

Upvotes: 3

Related Questions