2

I have a excel sheet where in column (G) there is a timestamp but is in epoch format, how can i convert epoch time to readable time format in excel.

I have already tried =TEXT(G2,"DD/MM/YYY HH:MM:SS") but it didn't work.

Viper
  • 67
  • 1
  • 1
  • 8

1 Answers1

13

Try this:

=TEXT((G2/1000 + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400,"DD/MM/YYYY HH:MM:SS")
  • 1
    What's the point of `"1/1/1970"-"1/1/1900"+1`? Doing it that way causes the formula to fail if using 1904 date system – barry houdini Jan 28 '18 at 09:47
  • 1
    @barryhoudini the epoch time started from 01/01/1970 thats why i have mentioned it into the formula. –  Jan 28 '18 at 09:48
  • Yes, but why subtract 1/1/1900? As pnuts says you can get the answer with `="1/1/1970"+G2/86400000` to give valid time/date or use that wrapped by TEXT function to get the result as text – barry houdini Jan 28 '18 at 09:51
  • @barryhoudini that can also be another way but result would be the same. –  Jan 28 '18 at 09:57
  • 1
    No, if you are using 1904 date system you will get an error – barry houdini Jan 28 '18 at 10:03
  • 1
    @barryhoudini for 1904 date we would not have epoch time right?? –  Jan 28 '18 at 10:06