LJLogUD & LJLogM Timestamps

The first column in the data file written by LJLogUD or LJLogM is a timestamp of seconds since midnight January 1st (Universal Time), 1904.  This is a very common timestamp format that tells us absolute and relative time.

Following are a couple different ways to present the timestamp in Open Office Calc, which should be about the same in Microsoft Excel.

Open the File

Rename the file to *.ods (or *.xls for Excel).

Double-click the .ods file to open it in Calc.  In the text import window make sure Tab is selected since LJLog saves a tab delimited file.  Click OK to proceed.

Relative Time

Insert a new column B and put in the formula =A1-A$1.

In the image above with B1 selected, we double-clock the little black square at the bottom-right of B1 and that extends the formula down the column as shown below.

Column B now has relative time, in seconds, from the beginning of the file.

Absolute Time

Insert a new column B and put in the formula =(A1/86400)+1462-0.291666667.  This converts seconds since 1904 to days since 1900.  The last term is the difference (in days) between Universal Time and local time (Colorado, USA) so we subtract 0.291666667 days (i.e. 7 hours).

In the image above with B1 selected, we double-clock the little black square at the bottom-right of B1 and that extends the formula down the column as shown below.

Column B now has days since 1900.  That is the native time format of Calc so we can now tell Calc to format the cells as desired.  With the entire column selected, click Format→Cells to get the window below.  Set Category to Date or Time and select the desired Format.  Click OK.

Now column B is displaying time in the MM/DD/YYYY  HH:MM:SS format.