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
- Set the DateTime property value as 20180403T045959.999 GMT.
- Change the operator timezone to EST.
- Add the property to the report.
- Run the report.
- 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
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.