How to Convert MM/DD Date Format to DD/MM in Excel with Report Generation Toolkit?

Updated Jan 3, 2018

Reported In

Software

  • LabVIEW Report Generation Toolkit

Other

Excel

Issue Details

How can I programmatically convert MM/DD/YYYY (12/31/2017) date format into DD/MM/YYYY (31/12/2017) date format using National Instruments Report Generation Toolkit for Microsoft Office?

Solution

Unfortunately, Excel does not have a function call to programmatically change the Locale (location) setting for a cell, but there are a couple of work-arounds to write dates to Excel in the format DD/MM/YYYY.
 
  • Manual change in Excel
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 right clicking on any cell and selecting Format Cells, then selecting Date, and finally changing the Locale (location) to the desired format.  This allows you to format the date however you'd like.
 
  • Convert the Date Representation to Text
This will change the way Excel handles the cell data from the date format mentioned above to a simple text string.  For example, if you're trying to convert the date '12/31/2017' in cell A1 into '31/12/2017', you can use the following text input for the Excel Easy Text.vi to display the date in the dd/mm/yyyy format:

=TEXT(A1,"dd/mm/yyyy")

Figure 1: Time Format Snippet
 
  • Use an Excel Template
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.

Figure 2: Excel Date Formating Snippet
 
The snippet from Figure 2 shows the example Excel Date Formating.vi

Additional Information

Microsoft Excel handles date and time as a floating point decimal representation.  The integral value (to the left of the decimal) represents the number of days since the beginning of the 20th century.  The decimal value represents the time of day from midnight in seconds divided by 86,400 (i.e. 6:00:00PM = 0.75). 

 

WAS THIS ARTICLE HELPFUL?

Not Helpful