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

Exported Excel from a report displays incorrect DateTime value

SA-57120

Summary



Exported Excel from a report displays incorrect DateTime value.

For example,

The DateTime property value is set to 03-April-2018 11:59:59:999 EST (that is, 20180403T045959.999 GMT).

The exported Excel displays 04-April-2018 12:00:00 AM.

This occurs only when the milliseconds value is set to 999.



Error Messages



Not Applicable


Steps to Reproduce

  1. Set the DateTime property value as 20180403T045959.999 GMT.
  2. Change the operator timezone to EST.
  3. Add the property to the report.
  4. Run the report.
  5. Export data to Excel.


Root Cause



This behavior is as per Pega product design.

When the date value is set to 'Mon Apr 02 23:59:59.999 EDT 2018', Excel displays the value as '03-Apr-2018 12:00:00 AM'.

Excel file generation logic generated the DateTime correctly as 'Mon Apr 02 23:59:59.999 EDT 2018'. Apache POI API which is used to generate the Excel document, converted the DateTime to a Double value as '43192.999999988424'. Excel stored the DateTime as floating point values internally. It can retrieve the set value correctly as 'Mon Apr 02 23:59:59 EDT 2018'. However, it is unable to handle the double precision value passed by POI and rounded the double value. Hence, the DateTime value is displayed as '03-Apr-2018 12:00:00 AM'.



Resolution



Perform the following local-change:

Set the milliseconds value to 000.

For example,

Change 03-April-2018 11:59:59:999 EST  to 03-April-2018 11:59:59:000 EST.


 

Published August 24, 2018 - Updated October 8, 2020

Was this useful?

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.

Did you find this content helpful?

Want to help us improve this content?

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