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!
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
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
If you notice any errors with this code, please leave a comment!
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.
Very good write-up and coding, couple thoughts: