Academic Company Events Community Support Solutions Products & Services Contact NI MyNI
5 ratings:
 2.20 out of 5   

Properly Reading the Date From a Cell in Excel into LabVIEW



Primary Software:
Primary Software Version: 8.0
Primary Software Fixed Version: N/A
Secondary Software: LabVIEW Toolkits>>LabVIEW Report Generation Toolkit for Microsoft Office

Problem:
I have an Excel spreadsheet that contains several cells formatted for the date datatype. I am trying to read these dates into LabVIEW using the Excel Get Data.vi that is part of the Report Generation Toolkit for Microsoft Office. However, when I read the data contained in one of these cells, LabVIEW displays a number like 37460 instead of the date contained in the cell.

Solution:
When the Excel Get Data.vi reads from a cell that is formatted to contain a date, it returns a number such as 37460. 37460 actually corresponds to the date 7/24/2006...it is the number of days that occurred between 1/1/1904 and 7/24/2006. LabVIEW's timestamp system determines past, present, and future dates with reference to 1/1/1904. There are two steps that need to be taken to correct this problem:
  • See Knowlegebase 2VHFUSQJ: Formatting LabVIEW Date and Time Data for Microsoft Excel for instructions on how to format Excel to follow the 1904 date system. Note that by changing your spreadsheet to this system, Excel will automatically add 4 years to each date listed in the spreadsheet. These dates will have to be adjusted back to their proper year.
  • Now you will have to add some additional code to your block diagram so that LabVIEW correctly interprets the number returned from Excel as a date, and not the number of days that have elapsed between the date in the cell and 1/1/1904. See the attached screenshot. You will need to wire the data output of the Excel Get Data.vi into a multiplication function and multiply it by the constant 86401. (86400 is the number of seconds in 24 hours, the extra second is to put the date past midnight). Then wire the output of the multiplication function into the time stamp input of the Get Date/Time String vi. Now the date string output of this vi will contain the same date that is entered in the Excel cell you are reading from.




Related Links:
nowledgeBase 31ADH05Z. Incorrect Values Given When Converting Seconds to Absolute Time
Knowlegebase 2VHFUSQJ: Formatting LabVIEW Date and Time Data for Microsoft Excel

Attachments:


Date from Excel code.JPG


Report Date: 07/25/2006
Last Updated: 09/02/2011
Document ID: 3ZOBNKDF

Your Feedback! poor Poor  |  Excellent excellent   Yes No
 Document Quality? 
 Answered Your Question? 
  1 2 3 4 5
Please Contact NI for all product and support inquiries.submit