Get(set) Date and Time from MS Excel to LabVIEW

[GriffinRU]

Overview

Import a timestamp from Microsoft Excel into LabVIEW and convert it to the timestamp data.

Description

Simple solution for old problem of converting MS Excel time to LabVIEW time. No more day counting and daylight saving... well let's hope

Concept:

1. Get Excel Application refnum
2. Open Excel Application
3. Get Active Workbook
4. Get Active Worksheet
5. Select Row (A1)
6. Get selected cell current number formatting
7. Write or Read cell's value
8. Close references

     NumberFormatLocal property is used to allow mixed setting for Excel spreadsheets.

Write function:

1. Convert LabVIEW timestamp to "m/d/yyyy h:mm:ss" format
2. Pass string to Value2 property

Read function:

1. Set current cell number format to "m/d/yyyy h:mm:ss"
2. Read text property
3. Set current cell number format back to original
4. Convert text to LabVIEW timestamp
5. Close references

Steps to Implement or Execute Code

1. Open attached Excel document
2. Load Excel Date.vi
3. Run VI

 Note: Text property only works on single cell's; i.e. cannot be acquired as array. Column or row of timestamps must be read one by one.

Requirements

LabVIEW 2012 (or compatible)

Updates

11/15/2010

Bug: AM/PM switch was missed in original code.

Note: Required testing with 24h style and regional settings other than US.

12/10/2010

Bug: If Excel's Cell is not wide enough to accommodate entire Date and Time string, then reported value from "Text" property would be presented as "####", resulting in string scan error.

Fix: Execute "ShrinkToFit" command before "Text".

04/25/2010

Bug: Error converting time

Fix: Format to LabVIEW timestamp directly

**This document has been updated to meet the current required format for the NI Code Exchange. For more details visit this discussion thread.**