Viewing the history.dbf file in Excel
Posted by Adrien de Croy (Import) on 24 January 2007 07:58 PM
When you view the history.dbf file in Microsoft Excel the time is shown in Unix time, being the number of seconds elapsed since midnight on the 1st of January 1970. Obviously most people are not going to be able to read this so it will need to be converted.
To convert the STARTTIME value to a readable format:
1. Online Conversion
2. Excel Conversion
Use the formula:
=(x/86400)+25569 where x = the STARTTIME value, 86400 = no. of seconds per day and 25569 = the base for 1 Jan 1970.
*If your calculation is an hour out, then you can adjust it similar to this: =((x+3600)/86400)+25569
When you apply the formula you will get another value, you will now need to format the cell to a date/time format.
eg. STARTTIME = 1124826307
applying the formula in Excel =(1124826307/86400)+25569
gives the value 38587.823
right click the cell and select format
select 3/14/98 13:30 gives 8/23/05 19:45
To change the format of the entire column:
Select a new (empty) column
Click the formula button (=)
Enter the formula =(I2/86400)+25569 where I2 is the first-value containing cell from the STARTTIME column
Click ok, the value should change
Click the lower right corner of the new cell and drag to the bottom of the column, the entire column should populate with the new format
Click the top of the column to highlight the entire column
Right click the column and select format
Change the format to the required date format, the column should now show as date/time.