Appendix G. Importing Files into Excel
([Year]–1900)*365+1+Int(([Year] –1901)/4)+[Day]+Int([HHMM]/100)/
24+([HHMM]/100-Int([HHMM]/100))*100/60/24+[Sec]/60/60/24
Section of formula: Results:
[Year]–1900)*365+1+Int(([Year]-
1901/4
Delivers the number of days since the beginning of
the 20
th
century through the end of last year taking
into account leap years
+[Day] Adds the Julian date to the above
+Int([HHMM]/100/24 Converts the hour portion of the HHMM time field
to a fraction of a day
+([HHMM]/100-
Int([HHMM]/100))*100/60/24
Converts the minutes portion of the HHMM time
field to a fraction of a day
+[Sec]/60/60/24 Converts the seconds field in to a fraction of a day
Once you have entered the formula for one cell you can apply it to multiple
cells using Excel’s Fill function. Selecting the cells and using Format Cells
can set the display format of the timestamp.
G.2 Table-Based Data File Import
Step 1 of 3
Select the Delimited option from the Original Data Type group box. Using the
arrow buttons to the right of the Start Import at Row field, select the number of
the first row of data to be imported. If your data file has headers included, you
can import those or start the import at the first row of data (typically row 3).
Select the Next button.
G-4
To Next Page