Date-Time translation

Content

Submitted by Dr Waisanen on Mon, 08/24/2015 - 22:14

So, I'm looking in the SQL export and see a logtime...of the form 1280375509. Wha? Looking in the log report, I see the date/time of the event is Thu 29 of July, 2010 08:51 Recalling a discussion about Unix represenation of dates as number of seconds past midnight Jan 1970, I play with Excel and discover the translation:

  • 0 (in Excel) is equivalent to Saturday, January 00, 1900, 12:00:00 AM
  • 2209179600 is equivalent to Thursday, January 01, 1970, 5:00:00 AM. This is the reference I need to use for the logtime conversion
  • Conversion: Adding the reference to the logtime, dividing the sum by 86,400 (number of seconds in a day; 3,600*24), and displaying the result as date/time gives me the date/time in Eastern (local) time

For example, (1280375509 + 2209179600)/86400=40388.36932; in dddd, yyyymmdd, HH:mm format = Thursday, 20100729, 08:51 Simple! Okay, so "simple" would be: Function (presuming logtime is in D2): =(D2+2209179600)/86400 Format of cell: yyyy-mm-dd hh:mm