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

Converting 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.  This means that if you need to send a date through the Report Generation Toolkit that is in a format different from the current location setting, you will need to 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 sting.  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 code:

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

If you try to keep the cell formatted as a date, Excel will interpret '24' as a month and '12' as a date, which results in an incorrect date.  There is no easy way to programmatically convert this text string back into a date without manually changing the regional date format of the cell.



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

Attachments:
SwitchMMDD.zip




Report Date: 05/10/2004
Last Updated: 03/13/2012
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