Support Article

Date Format displays incorrectly when exporting data in Excel

SA-15326

Summary



Listview is configured with datetime field as one of the source columns and "Date" as the export format style. 
After generating the excel sheet for the report, the result is obtained in "dd/mm/yyyy hh:mm a" format whereas the expected format should be "mm/dd/yyyy".


Error Messages



Not Applicable
 

Steps to Reproduce



1. Create a listview with datetime property as one of the field configurations.
2. Add "Date" as the format style in Export format column for that field.
3. Run the report and click export excel button.
4. Fields in datetime column display data in "dd/mm/yyyy hh:mm a" format instead of "dd/mm/yyyy".


Root Cause



A software use or operation error. The property type for the fields generated as "mm/dd/yyyy hh:mm a" in excel sheet is configured as "DateTime" instead of "Date". Therefore, at runtime, the property level configuration takes precedence over the provided excel format. But user wanted that property to be DateTime and in export excel it should be generated as as the format defined in export format column.

Resolution



Perform the following local-change: 

Create a custom format with the following code and use the format in property configuration.

<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) {
String sDatetime="";
String type = tools.getParamValue("DateTimeFormat");
String strTimeZone = tools.findPage("pxRequestor").getString(".pyUseTimeZone");

sDatetime = PRDateFormat.format(null, strTimeZone, PRDateFormat.DEFAULT_DATE_SHORT, curProp.toDate());

tools.putSaveValue("result",sDatetime);
}
else {
tools.putSaveValue("result",tools.getActiveValue());
}

%>
<pega:reference name="$save(result)" />
</div>

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.