Convert Date Format in Excel with LabVIEW Report Generation Toolkit

When writing data to a Microsoft Excel® workbook using LabVIEW software, Excel uses your current location settings set in your Windows OS regional settings to determine the date format it uses. There may be situations where you would like to reformat your date/time from your location's default (e.g. MM/DD/YYYY) to another format (e.g. DD/MM/YYYY). 

Excel does not have a function call to programmatically change the Locale (location) setting for a cell. Because this functionality does not exist in Excel, LabVIEW and the Report Generation Toolkit cannot programmatically change the date format in Excel. However, there are workarounds to write dates to Excel in the format DD/MM/YYYY.

In this tutorial, you will learn some workaround methods to change the date format when writing data using LabVIEW and the Report Generation Toolkit to Microsoft Excel. This same method could be applied to other Microsoft Office applications.

 
 

 

Manual Change Data Format in Excel (After Writing Data)

This is a simple method that you should use if you don't mind doing post-processing in Excel after writing your data. 

You can change the date format after completion of writing data to Excel by selecting a different locale. Excel chooses the default regional date format (i.e. MM/DD/YYYY, DD/MM/YYYY, etc.) based on your current location setting.  In Excel, you can manually change the format of a particular cell by following Windows' instructions. 
 

Convert the Date Representation to Text 

If you prefer accomplishing the task programmatically, you can utilize the Excel TEXT() function to covert the date into the desired date format and input the date as a text string. 

1. Get the current time in seconds since 12:00 a.m., Friday, January 1, 1904
2. Divide by 86400 to get the number of days
3. Covert LabVIEW epoch into Excel Epoch by adding 1461 days
   1. The LabVIEW Epoch is tracked in seconds since 12:00 a.m., Friday, January 1, 1904
   2. The Excel Epoch is tracked in days since January 1, 1900
4. Construct a string to use the Excel TEXT() function

This will change the way Excel handles the cell data from the date format to a simple text string.

To use the LabVIEW snippet below, create an Excel document and update the File Path control to point to this Excel document.

=TEXT(current date format,"dd/mm/yyyy")

Use an Excel Template

If you will be writing multiple data sets 
You can manually change a cell's format in Excel, and save the worksheet as a template. If you create your Excel report in LabVIEW from this template, the cells will use the formatting you set. To do so, please follow below steps:

1. Open a new worksheet in Excel. Select the cells that will hold dates and set the date format you want. 
2. Save the worksheet as an Excel template.   
3. In LabVIEW, place a New Report.vi function. 
4. Create a control on the template (empty) input and select the Excel template you created above.
5. Create a constant on the report type input and select Excel.
6. Place an Excel Easy Text.vi function.
7. Create a control on the text input to enter your date.
8. Enter your date in the format =DATE(YYYY,MM,DD)   
9. Place an Excel Bring to Front.vi function to open Excel once the worksheet has been edited.
10. Connect all report and error inputs and outputs.   

When run, the snippet attached below will place the Date input into cell A1 of the new worksheet. This worksheet will use the same date format for cell A1 that was set in the Excel template. The snippet from Figure 2 shows the example Excel Date Formating.vi

Figure 2: Excel Date Formatting Snippet

Note: This image is a LabVIEW snippet, which includes LabVIEW code that you can reuse in your project. To use a snippet, right-click the image, save it to your computer, and drag the file onto your LabVIEW diagram.

Excel® is a registered trademark of Microsoft.