Skip to main content

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>

Suggest Edit

Published January 31, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us