Academic Company Events Community Support Solutions Products & Services Contact NI MyNI
47 ratings:
 2.80 out of 5     Rate this Document

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



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

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

Solution:
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 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.

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.

  • 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/2008' in cell A1 into '31/12/2008', 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")

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

    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, this snippet 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 zip folder attached below contains an example Excel template where column A is set to the date format DD/MM/YYYY, and an example file Excel Date Formatting.vi which is the same as the snippet above.



Related Links:
KnowledgeBase 3EJEC9NS: Report Generation Toolkit Compatibility with Microsoft Office and LabVIEW

Attachments:
Date Formatting.zip




Report Date: 05/10/2004
Last Updated: 11/19/2014
Document ID: 399K1MNH

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