Converting UTC timestamp to a time value?

Question

I'm doing an exercise where I have data with several entries of this type:

  "started_at": 1475499600.287,

Which is defined as:

started_at: when the interval started, UTC Time

Ok, how can I convert it to a meaningful time (using Excel, for example) in some format (e.g. dd/mm/yyyy hh:mm:ss)?

@ScottCraner (does this even ping you?) - When I use that formula, `=(A2-DATE(1970,1,1))*86400` with OP's number (`1475...`), I don't get a date/time, nor a number that I can format as such. — BruceWayne, Oct 12 '17 at 18:23
No you need to use the second answer not the first. That poster did it both ways. `(Unix Timestamp / 86400) + 24107` @BruceWayne — Scott Craner, Oct 12 '17 at 18:24

score 5 · Accepted Answer · answered Oct 12 '17 at 18:05

5

For translating into GMT, you can use

=(((A1/60)/60)/24)+DATE(1970,1,1)

Your example time will become 03/10/2016 13:00:00

Make sure to format the cell dd/mm/yyyy hh:mm:ss

answered Oct 12 '17 at 18:05

BruceWayne

1 Answers1