Why are all dates set to 1/0/1900 12:00:00 AM or "########" after exporting to Excel?
Environment:
Agent Desktop, Incidents
Oracle B2C Service
Issue:
I exported report results to Excel and enabled the "Convert all dates into Excel-formatted dates" option. However, in the resulting spreadsheet, all dates are set to 1/0/1900 12:00:00 AM or "########" in my Excel file.
Resolution:
Microsoft Excel 2007, 2010 or 2013 should be installed to the workstation in order to export report data to Excel. And if "Convert all dates into Excel-formatted dates" option is checked, then System date/time format should be the same as the console date/time format because Excel uses system defaults. This checkbox is not compatible on a client machine with a localization that does not match the interface's DTF_SHORT_DATE configuration.
For example:
An interface has the English - European language pack applied. This means the UK date time format will be applied to all dates within the console which is DD/MM/YYYY HH:MM AM. The workstation's system date time format is US which is MM/DD/YYYY HH:MM AM. Excel compiler receives DD/MM format and believes that day is month and vise versa. That creates an exception in the compiler's engine which invalidates all the dates.
Steps to resolve:
There are few options:
1. Change system date/time format to be the same as in a console (please note the DTF_SHORT_DATE interface configuration needs reflect this localization setting). This setting can be found in Control Panel > "Region and Language" option > Select appropriate country Format > restart/start the console.
2. Modify your report and change the date fields format to the one that is used as default System date/time format. For example (For US System date/time format):
date_format(incidents.created, 'MM/DD/YYYY HH:MI AM')
Or:
to_date(date_format(incidents.created, 'DD/MM/YYYY HH:MI AM'), 'DD/MM/YYYY')
For more information please see the following answers:
