Skip Navigation
Expand
All dates set to 1/0/1900 12:00:00 AM or "########" after exporting to Excel
Answer ID 8671   |   Last Review Date 07/22/2019

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:

Answer ID 1884: Functions used in reports

Answer ID 2545: Exporting report data to Excel