Have an idea?

Visit Sawtooth Software Feedback to share your ideas on how we can improve our products.

Converting start and end time stamps to dates

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.
asked Jan 8 by Paul Moon Platinum (61,355 points)

1 Answer

+1 vote
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.
answered Jan 8 by Zachary Platinum Sawtooth Software, Inc. (79,150 points)
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.
...