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?
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