SAP Knowledge Base Article - Public

1262820 - Cell format for a date in MS Excel displays as 'custom' for report exported to MS Excel from Crystal Reports

Symptom

  • A report exported to the MS Excel format from Crystal Reports containing date values, displays the date properly within the MS Excel Spreadsheet.
  • Date cell format in the MS Excel spreadsheet displays as custom format and not the date format, why?

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, and add a date to the report.
  2. Export a report that contains date field to MS Excel format.
  3. Open the exported report in MS Excel.
  4. In MS Excel, right click on a date, and select "Format Cell..." in the contextual menu.
  5. In the "Format Cells" window, under the tab "Number", the format is set to "Custom" and not "Date" as expected.

Example:
For a date displayed in MS Excel in the format: YYYY/MM/DD, like 2008/12/25
Will have a  "Custom" format like: yyyy\/mm\/dd, instead of a date format.

Cause

  • By design, the date cell format is set to the "Custom" format within MS Excel to ensure it retains the date format set within the report.

Keywords

CR, xls, export, date format , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

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