Dynamics NAV and export to Excel: Export dates as true excel dates

When running Send-to Excel functionality and exporting data to MS Excel using style sheets, all data exported form NAV is formatted as either numbers or strings.

That also includes dates, that are exported as strings and not formatted as true dates in excel. As of update 1 for NAV 5.0 SP1, data.xml generated when running export to excel contains data type attribute for each row with data printed. Using ‘Date’ attribute, one can add a fromatting rule in the style sheet, to specify formatting of data of type Date.

Following example illustrates this. Please note, the style sheet below is created and tested on W1 NAV using US regional settings. Though tests have been run on few other combinations of regional settings and date format, you might need to adjust the rule to local date format.

To represent strings as dates in excel, .xml file generated must contain dates in XML datetime format. XML format is defined as:

yyyy-mm-ddT00.00.000Z.

The scenario also assumes years in Dynamics NAV are represented with 2 digits,  and years in range 00..29 are in fact 2000..2029, while years in range  30.. will be formatted as 1930… Dynamcis NAV date format in this example is mm/dd/yy.

In the coded below, substring(@value,1,2)=mm, substring(@value,4,2)=dd and substring(@value,7,2)=yy

Modify the code in example below according to date format in local NAV client.

To modify the style sheet, open the default style sheet for export to Excel using notepad. The default style sheet is NavisionExportToExcel.xslt, and is placed in Stylesheet folder of the Client folder.

Go to line 61 of the default style sheet, or locate the section below using Search/find, and add the lines marked in the code below.

....

<

Style ss:ID="CheckBox">    









<

Style ss:ID="Dateformat">    



<

NumberFormat ss:Format="Short Date"/>    

   

....

****************************************************

....

<

xsl:template match="Control[@type='TextBox']">

<

Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet">

    

      

Dateformat    

   



TextBox

 



<

Data>

<

xsl:choose>      



  

DateTime    

--T00:00:00.000  

  

DateTime  

--T00:00:00.000  

  

Number







String







.....