Formatting LabVIEW Date and Time Data for Microsoft Excel

Issue Details

How do I format data returned by the Get Date/Time in Seconds function in LabVIEW so Microsoft Excel can interpret the data correctly?

Solution

On Windows, the default date system for Microsoft Excel is based on the number of days since January 1, 1900. The LabVIEW date system is based on the number of seconds since January 1, 1904. 

Follow the below steps in order to resolve the issue:

1. Change the default settings for Excel to the 1904 date system. To do so in Excel, select Tools»Options, select the Calculate tab, and place a check mark in the 1904 date system checkbox.

2. Convert the LabVIEW seconds data to days and use that data when you write to the spreadsheet.
   • Use the Get Date/Time in Seconds.vi to get the current time as a time stamp data type
   • Use the To Double Precision Float.vi to convert the time stamp data type to a double and divide the seconds output data by 86,400
     • LabVIEW records the data in Universal Time so you must adjust the time data in LabVIEW according to your time zone to make sure Excel displays the right time. Reference the link below to find out the adjustment needed for you time zone.
3. Once you write the data to a file and open it in Excel, use the date/time format you prefer.

Refer to the related links section for a simple example of how this conversion is done in LabVIEW.

Additional Information

The reason why two dates systems exist is related to the fact that 1900 was not a leap year.