Tuesday, August 03, 2010

Converting Unix timestamps to something readable in Excel

Our company uses log files in our machines to log raw data in real-time so that we have a history of what's happened on jobs that can be analyzed and, if necessary, used to prove to inspectors that our machines are doing what they say they are. We have our own log file parsing and analysis package for performing various analysis, but sometimes we need to look at the raw signals themselves in the logs, and this is where Excel comes in handy. The timestamps that are put on the signals are Unix timestamps, so seconds since the Epoch (Jan. 1, 1970 at 00:00:00 hours), which are not very useful in Excel, because Excel has its own Epoch (Jan. 1, 1900). To make things just a little bit more complex, we have machines in different timezones, which we need to account for as well. Thanks to a post at this site, I managed to come up with a modified version of their formula that will convert the unix timestamps to Excel format, and adjust them for timezone offsets :

=(ROUNDDOWN(A10116,0) / 86400) + 25569 - TIME(6,0,0)

No comments: