Support Article

Date format changes when performing Export to Excel

SA-74140

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.