Converting Excel Timestamps to LabVIEW

[Evan R.]

From time to time, you may have the need to load timestamps from excel. You'll find out quickly that LabVIEW and Excel do not agree on a way to represent date/time!

LabVIEW Timestamp

http://zone.ni.com/devzone/cda/tut/p/id/7900

The LabVIEW timestamp is a 128-bit data type that represents absolute time. You can interpret this data type as a signed, 128-bit fixed-point number with a 64-bit radix.

{
   (i64) seconds since the epoch 01/01/1904 00:00:00.00 UTC (using the Gregorian calendar and ignoring leap seconds),
   (u64) positive fractions of a second
}

Excel Date and Times

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Excel stores dates and times in the format: "X.Y" where X is the number of days since January 1, 1900 and Y is the fractional number of days since midnight of the date being represented. (0.0 represents midnight January 1 1900).

As a result, some serious data conversion needs to take place in order to get an Excel timestamp to be represented in LabVIEW.

I have written a VI which should do the trick. The operations performed are as follows:

1. Convert the X portion of the Excel date to seconds since 1/11900.

2. Adjust by the number of seconds between the Excel epoch and the LabVIEW epoch (126298800)

3. Add the number of seconds since midnight

4. Figure out how many fractional seconds have elapsed since the most recent second represented in the excel timestamp

5. Convert from fractional seconds to u64 as specified here: http://zone.ni.com/devzone/cda/tut/p/id/7900

6. Bundle the seconds and fractional seconds into a cluster which is compatible with the LabVIEW timestamp

7. Typecast the cluster into a LabVIEW timestamp

Voila.

If you notice any errors with this code, please leave a comment!

Evan

[Kjell-E]

Neat. . It would have been even nicer if you had a utility to go the other way, to take a LV timestamp and output a number formated as per the Excel 1900 spec (or the optional Excel Mac spec as a 2nd choice). That would be handy for times when you want to store TSV or CSV files for use in Excel and you want an Excel compatible time-stamp.

[moses_montoya]

Thanks Evan, this is very useful!  I'm surprised NI has allowed this to remain such a tedious operation.

[GriffinRU]

Very good write-up and coding, couple thoughts:

• To support different regional settings it would be better to split by system decimal symbol, or treat as float and extract prime number...
  
• With Excel 2007+ "1904 date system" is now linked to workbook, rather than global Excel preference, so can back fire if work with various spreadsheets
  

-Artur