Symptom
- Blank date appears as 1/0/1900
- When exporting to Excel, empty date displays as 1/0/1900
- When exporting to PDF format, the blank date, exports as blank date.
- When exporting a report that contains blank dates from Crystal Reports to MS Excel format, the blank date displays as 1/0/1900
Environment
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- In Crystal Reports, create a report off any data source.
- Add a date field that contains blank dates, like.
05/01/2017
04/03/2016
<-- Blank date
07/06/2014
- Export the report to MS Excel format.
- When opening the report in MS Excel, it displays the blank date as: 1/0/1900, like:
05/01/2017
04/03/2016
1/0/1900
07/06/2014
Cause
- It is MS Excel that automatically set the blank date to: 1/0/1900.
Resolution
- To ensure MS Excel does not convert the blank date, convert the date in text format by using a formula:
- Create a formula that converts the date to text as follows:
If IsNull({Date_Field}) OR {Date_Field} = Cdate(0,0,0) Then
""
Else
ToText({Date_Field},'MM/dd/yyyy')
- Insert the formula on the report instead of using the date field.
Keywords
cr null empty xlsx , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016