Support Article
When exporting to Excel date format is in YYYYMMDD
SA-15804
Summary
When attempting to export a date field to Excel, output is expected to be in the format 'MM/DD/YYYY'. Instead the content is presented in the format 'YYYYMMDD'
Error Messages
Not Applicable
Steps to Reproduce
- Upload a file to the webwb folder
- Use the MSOGenerateExcelFile control and pass the file as a parameter
Root Cause
A third-party product issue. Excel reads the HTML stream produced by the activity using specific formatting.
Resolution
Please view the following PDN article on altering the HTML stream format: https://pdn.pega.com/support-articles/on-export-to-excel-date-format-inconsistently-as-mm-dd-yyyy
1. Open the “Export_DateTime” HTML Property rule instance on your system.
2. Perform a “Save As” of the HTML Property, specifying a new “Stream Name” and custom Ruleset and Ruleset version.
3. In the new control set the date formate as PRDateFormat.DEFAULT_DATE_SHORT_YYYY
4. Update the new HTML property to add the code below to the existing code.
(In the below div tag the style must be the format which we want to export to excel.)
<div class="excel-date" style="vnd.ms-excel.numberformat:@">
{existing code}
</div>
5. Open the report definition rule you wish to update and add the new custom html property in the column Format values for the corresponding date time property field.
6. Save the rules, execute the report definition and perform an Export to Excel.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.