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

Updated Nov 5, 2020

Environment

Software

  • LabVIEW Report Generation Toolkit

Other

Microsoft Excel®

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

Microsoft Excel uses your current location settings to determine the date format it uses. 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.
 

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 >> select Format Cells >> Date >> change the Locale (location) to the desired format.  This allows you to format the date however you'd like.
 

Convert the Date Representation to Text

Instead of working with data in the date data type, convert it to the text data type. 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

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.

 

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. The snippet from Figure 2 shows the example Excel Date Formating.vi

Figure 2: Excel Date Formating 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.

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). 


Excel® is a registered trademark of Microsoft.