Excel Online date format can not be changed

Hello,

Does anyone know how to change the date format to display as dd/mm/yyyy (from mm/dd/yyyy) for Excel Online? I checked my Regional Settings and they were already set to dd/mm/yyyy.

There is no Format Cells option in Excel Online so the only option I have is Short Date or Long Date which does not help. I am using Excel Online in Dropbox and have discovered in the forums that everyone else (ie Australia, UK, Denmark, etc) that wants dd/mm/yyyy also can not do it either.

Thanks,

Stephen

Hello there,

Thank you for posting on Microsoft Community.

In order to resolve this issue you may refer to developeratgriffith’s post from the following Microsoft Answers thread:

http://answers.microsoft.com/en-us/office/forum/officeonline-excel_online/can-you-change-the-date-format-in-microsoft-excel/96f86784-e120-42a9-96c3-fc6c571a276a?page=1

Hope the above information helps.

Thank you.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

No sorry this issue is about Excel Online not Microsoft Excel. I've already checked all the previous threads and none of them resolve my issue. 

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

There are some answers forum posts that should be helpful to you as well.  Here is one example:

http://answers.microsoft.com/en-us/office/forum/officeonline-excel_online/how-do-i-enter-dates-in-excel/16417c11-0b1e-4f3f-bba3-e90da1b7a95a

Unfortunately, there is no way to set a specific date format in Excel Online other than the one defined by your Microsoft Account settings.  If you open the workbook in Excel on your PC/Mac, you could set a specific Date format, and that format will be respected in Excel Online.  Explicit date formats are those that do not have an asterisk next to them in the Format Cells dialog in Excel.

Thank you for using Excel Online and providing feedback.  Your feedback is instrumental in helping us to make the service work better for you.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The date format was already correct in Microsoft Excel so that doesn't answer my question unfortunately. It is a problem with Excel Online that needs to be addressed ASAP. 

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Same issue. It would be very useful if there were an option to display the date and time of an Excel time value in ISO 8601 format (q.v.) irrespective of the locale setting except perhaps for the time zone.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated July 17, 2024 Views 17,075 Applies to: