CSV Date format output changes in the Reports and Athlete History Modules
When a csv is run from the Reports page or from the Athlete history page, Excel will try to open the date fields based on the specific country that a user is in. This has meant that files being opened in US countries are interpreting the dd-mm-yyyy format as mm-dd-yyyy and the dates are incorrect in the csv spreadsheet.
To combat this we created the ability for you to select the format that want when you run a csv report as, and as long as US users select either of the data formats that start in the American format (e.g. that have month before day) the files will be read correctly by a US version of excel.
It is important that you also keep in mind that even if you format the csv as a yyyy-mm-dd it may still open as mm-dd-yyyy as this is the default for Excel. If you are unsure of the exact date format, please open the downloaded file directly from the downloads using Note Pad, and that format that appears in Note Pd is the csv format that Smartabase is exporting the data as; we have no control over how Excel displays the data.
When a csv is generated from the Reports or the Athlete History modules the data is exported as a dd-mm-yyyy format. However, for users in countries that read the date format as month before day, this format has not being pulling into excel correctly
US based users can now overcome this limitation by selecting a format that outputs the date as mm/dd/yyyy or yyyy/mm/dd
A new Date Format output has been added to the Excel CSV export feature on the reports and Athlete History Modules. This means US users can select the formats which have the month before the day, and vice versa for other countries.
The file is exported according to the format that you select. An example of a dd-mm-yyyy format
An example of a mm/dd/yyyy format
However, in this instance, the file was exported as yyyy-mm-dd, but when it is opened it appears in a non-US format
The date is displayed incorrectly because excel interpreted the date format incorrectly for non-US users. If you need to double check the output from Smartabase, do this by downloading the file and opening it in Notepad first (as shown in the images in the steps below).
For this example, we are generating outputs of all of the different formats and opening them with NotePad directly from the downloads folder; this prevents Excel from auto-formatting the date field based on the country and shows the true output from Smartabase
dd-MM-yyyy format opened in NotePad
dd/MM/yyyy format opened in NotePad
MM/dd/yyyy format opened in NotePad
yyyy/MM/dd format opened in NotePad
yyyy/dd/MM format opened in NotePad
N.B. If non-US users chooses a mm-dd format, even if they reformat the date on the spreadsheet, these will not appear in the correct dd-mm format.
0 Comments
Add your comment