Converting start and end time stamps to dates

Question

In the past, I believe Brian McEwan passed on an Excel formula that converted the start and end system time stamp variables from numeric to date.

I seem to have misplaced it? Apologies.

Hoping someone could please pass on the same formula.

In the meantime, I will keep up the hunt.

Thank you.

Answer 1

The timestamp is the number of seconds since January 1, 1970, so this should be the formula you want:

=A1/86400+DATE(1970,1,1)

Then set that cell's format to date.

Comments

Thanks Zachary for your prompt response.

Yes, I'm well aware of the number of seconds since January 1, 1970 issue. Thanks for pointing this out just the same.

I've tested it out and it appears to work well in converting to a date.

Can we convert the time stamps to this date / time format or something similar ...

06 Jan 2018 - 16:53:04

Many thanks.

---

Excel has multiple formats available for date cells.  I see two of which include H:MM.  Are either of those sufficient?

---

Thanks Zachary for your prompt reply.

I hunted down the date-time format under the Customer category within Excel. I was thinking it was under the Date category?

To accommodate the Aussie timezone, I had to modify the formula to: =(D2+36000)/86400+DATE(1970,1,1)   [adding on 10 hours]

And when we are in daylight savings Down Under, the formula became: =(D2+39600)/86400+DATE(1970,1,1)   [adding on 11 hours]

Thanks for pointing me in the right direction once again.

You're a champion mate. Good on ya.