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

• 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

• SAP SuccessFactors HXM Suite
• Report Table
• Report Canvas

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.
   
   
   
   
   
   

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

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.

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:

• A quick work around is to change the local system's date format. But this will have an impact on the other functionality as well.
  
  
• Another approach is to create a new spreadsheet file in Microsoft Excel and import the CSV file to this.
  The following article from Microsoft explains the steps:
  https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba
  
  While importing data from the CSV file, the Text Import Wizard allows choosing the date format for desired columns and this will help set the correct date format to display.

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

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