1969241 - Blank date displays as 1/0/1900 when exporting a report to MS Excel from Crystal Reports

SAP Knowledge Base Article - Public

1969241 - Blank date displays as 1/0/1900 when exporting a report to MS Excel from Crystal Reports

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

  1. In Crystal Reports, create a report off any data source.
      
  2. Add a date field that contains blank dates, like.
      
         05/01/2017
         04/03/2016
                               <-- Blank date
         07/06/2014
         
  3. Export the report to MS Excel format.
       
  4. 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:
      
    1. 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')
          
    2. 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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016