SAP Knowledge Base Article - Public

2498557 - Date format is inconsistent between CSV exports from Table reports / Canvas reports

Symptom

  • While exporting Ad Hoc / Table reports in CSV format (online or offline runs), the date format is not displayed properly in Microsoft Excel. However, exports in XLS and XLSX display the dates in the expected format.
  • Leading zeros are not showing correctly on the dates for report extract.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental

Environment

  • SAP SuccessFactors HXM Suite
  • Report Table
  • Report Canvas

Reproducing the Issue

  1. Login to the instance and create an Table report that includes date columns.
  2. Export the report in CSV format.
  3. Open the file in Microsoft Excel.
  4. The date format is not as expected in certain locales.

    Example for a user whose local system's date format is set to mm/dd/yyyy format.

    locale_comapre.jpg



Cause

XLS and XLSX formats include the dates and also details about the format in which they have been saved.
Excel_details_compare.jpg

But CSV is simply a database query's output in comma separated format and does not include details about the date format in which they should be displayed.

As a result, irrespective of the format the dates are stored in, Microsoft Excel assumes that they are in the local system's date format and attempts to display them in the same.
Refer the previous section's example.

Resolution

The behavior is expected, as long as there is a mismatch between the date format in which the report was exported and the date format set in the local system.
Hence for human usage, we suggest exporting the report in XLS or XSLX formats.

Work-arounds:

Note: 
The work arounds have been provided convenience purposes only.
SAP does not control, monitor or guarantee the information contained in the external sites.

Keywords

CSV, date, format, inconsistent, locale, Ad, Hoc, report, table, center, excel, xls, xlsx, yyyy, mm, dd

, KBA , LOD-SF-ANA-ADH , Adhoc Reports & Report Builder , Problem

Product

SAP SuccessFactors HXM Suite all versions