Support Article
Date format changes when performing Export to Excel
Summary
The Date format changes when data is exported to Excel. In Excel, the Date format differs from the date in the report. The Date format chosen in the report is 'dd-MMM-yy'. The Date format, in the report, is correct.
In the Excel file, the Date displays in the default format (as in the below image).
Error Messages
Not Applicable
Steps to Reproduce
- Run a list view with Date type column.
- Perform Export to Excel on the report result.
Root Cause
A defect in Pegasystems’ code or rules.
The list view exported to Excel does not consider the out-of-the-box Export format controls.
Resolution
Perform the following local-change:
- Create a custom export control by performing a Save As of the out-of-the-box Export_Date control (for example, Export_Date_Custom).
- Use the following as the HTML source for the custom control (set the 'numberformat' value in the Style tag to the desired Date/Time format).
<pega:onlyonce name="exceldate">
<style>.excel-date{vnd.ms-excel.numberformat:dd-MMM-yy}</style>
</pega:onlyonce>
<div class="excel-date">
<%
ClipboardProperty curProp = tools.getActive();
if ((curProp.getType() == PropertyInfo.TYPE_DATE || curProp.getType() == PropertyInfo.TYPE_DATETIME) && curProp.size() > 0 && curProp.hasValidValue() && curProp.toDate()!=null)
{
java.util.Date theDate = curProp.toDate();
String strTimeZone = tools.findPage("pxRequestor").getString(".pyUseTimeZone");if (theDate !=null)
{
tools.putSaveValue("result", PRDateFormat.format(null, "GMT",PRDateFormat.DEFAULT_DATE_SHORT, theDate));
}
else
{
tools.putSaveValue("result", tools.getActiveValue());
}
}
else
tools.putSaveValue("result", tools.getActiveValue());
%>
<pega:reference name="$save(result)" />
</div> - Specify this custom control as the 'Export format' on the 'Display Fields' tab of the list view:
The exported Date/Time value will be in the specified format.
Published March 9, 2019 - 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.