How To Quickly Convert UTC Time To Local Time in excel?-CodePudding

Is there an easy way to convert the UTC time in this format (2020.05.28 15:45) into local time? I have a really long list to do the conversion. For example, 2020.05.28 15:45 coverts into 2020.05.28 23:45; 2020.05.28 16:45 coverts into 2020.05.29 00:45. I don't know how to change the date in excel?

CodePudding user response：

=SUBSTITUTE(A1,".","/") 1/3

CodePudding user response：

Assuming the 1st value is in A1. Do/Put:

=TIMEVALUE(RIGHT(A1,5)) DATEVALUE(SUBSTITUTE(LEFT(A1,10),".","/")) TIME(8,0,0) in B1
r-click cell > format > custom : yyyy.mm.dd hh:mm
drag downwards.

Please share if it works/not/understandable. /(^_^)

CodePudding user response：

=TEXT(TIMEVALUE(A1,5) TIME(8,0,0),"yyyy.mm.dd hh:mm")

8 is the difference in between UTC and your GMT hours.