This is a cross-posting from the
Dropbox Forum. I tested a some cases, here is what I found out:
Dropbox Web Preferences -> Language: Direct effect on Excel Online date format
Dropbox Web Preferences -> Date format: No effect on Excel Online date format
Chrome (Browser) Preferences -> No effect on Excel Online date format
Dropbox Display Language = German -> Excel date format = TT.MM.JJJJ (= DD.MM.YYYY)
Dropbox Display Language = Russian -> Excel date format = ДД.ММ.ГГГГ (= DD.MM.YYYY)
Dropbox Display Language = Chinese (Simpl.) -> Excel date format = YYYY/MM/DD
Dropbox Display Language = English (UK) -> Excel date format = MM/DD/YYYY
Dropbox Display Language = English (US) -> Excel date format = MM/DD/YYYY
This leads to the assumption that it is the Dropbox language setting that causes UK/AU/CA users to see wrong date formats. I also tested on SharePoint Online (Office 365) with the following results:
SharePoint Site Regional Setting = German -> Excel date format = TT.MM.JJJJ
SharePoint Site Regional Setting = Russian -> Excel date format = ДД.ММ.ГГГГ
SharePoint Site Regional Setting = Chinese (China) -> Excel date format = YYYY/MM/DD
SharePoint Site Regional Setting = English (UK) -> Excel date format = DD/MM/YYYY
SharePoint Site Regional Setting = English (US) -> Excel date format = MM/DD/YYYY
SharePoint Site Regional Setting = English (CA) -> Excel date format = YYYY/MM/DD
It seems that it is possible to tell Excel Online the exact regional format.
Here is the Excelsheet I used for testing: https://dl.dropboxusercontent.com/u/4737890/RegionalSettings.xlsx
You can change the Excel date display format but the Excel date input format is determined by the regional settings of the environment Excel is running in (Web/Windows/Mac/Mobile). If you want to display date cells correctly, use the date detection in the sheet
above and then use the formula "=TEXT([YourDateCellReference],C8)" to format date cells in Excel.
With English (Australian) settings on Windows the formula "=TEXT(38000,C8)" shows "14/02/2004".
The date format problem is one of the weaknesses of Excel. If you want to create internationalized worksheets that work in every country you have to detect the local regional settings and internationalize all formatting, input parsing and formula generating
formulas.