Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

DateTime property is not in correct format in Excel

SA-39183

Summary



User has configured two DateTime properties (say 'Planned start date' and 'Planned end date') which is used in Report definition(RD).

Only Date is extracted using custom control. Or use out of the box(OOTB) pxDateTime control with Date format "dd-MM-yyyy".

When the report definition is executed, datetime properties appear is correct format. However, on using out of the box Export to Excel functionality from the RD, the DateTime properties appear in wrong format.

Error Messages

Not Applicable

Steps to Reproduce



1. Create a DateTime property.
2. Use it in Report definition.
3. Create the control to extract only date from the DateTime property
(OR)
3. Use OOTB pxDateTime control with Date format "dd-MM-yyyy"
4. Run the ReportDefinition and check the output.
5. Perform Export to Excel and compare the value with the outcome of RD.

Actual behavior: Format for DateTime property is not same as it appears in the RD.

In RD:



In Excel:

Expected behavior: Format for DateTime property has to be same as it appears in the RD.

Root Cause



No locale is specified at operator level, so PRDateFormat is calculated as per server instance, here, it is US format. To the downloaded excel markup, date is passed in dd-MM-yyyy format from Pega platform, but, as per US format excel-date is sent as M/d/yyyy(.vnd excel-date.numberformat). So excel is converting the date again to MM-dd-yyyy because US format takes month first then date.

If locale is specified at operator level as "en_IN"(based on user location) then excel date format(.vns excel-date.numberformat) is passed as dd/MM/yyyy, so in this case downloaded excel shows date as dd-MM-yyyy that is Date first then month as per en-IN standard.

This is not an issue with Platform(PRPC is sending formatted value to browser), rather compatibility with the locale from which excel is sent and on the system where excel is viewed.

Resolution



Here’s the explanation for the reported behavior:

Use datetime/date/time format styles as per the server timezone standard(in this case US format M/d/yyyy that is, month first then date)

(OR)

Apply the local change as per below Support article:
https://pdn.pega.com/support-articles/report-date-time-format-custom-dd-mmm-yyyy-wrong-excel​
Suggest Edit

Published July 27, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

0% found this useful

Have a question? Get answers now.

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

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