How to change the date format in Excel Online
The default date format in Excel has always been a mystery to newcomers and its stranger in Excel Online (the new name for Excel Web Apps).
To change the default date format for display and date entry for Excel Online, go to your Microsoft account:
- Go to https://account.live.com/ . You can open a separate window/tab to do this, leaving the Excel worksheet or OneDrive open elsewhere.
- Login using the same account as for OneDrive / Excel Online
- Go to Overview | Personal Info
- Under the heading ‘Home’ change the country/region to the one you want as the date/currency default.
- While you’re on this page you might want to change the Time Zone setting as well as the equivalent Work address settings.
- There’s no need that we can see to include full addresses. Country/Region and Time Zone is sufficient.
- Click Save
Reopen the Excel worksheet and the date cells should change their look.
Changing the County/Region under ‘Home’ worked in our tests in April 2013. However other people report that instead changing the ‘Work’ country is necessary. Yet other reports say that changing the default language is required from the bottom line of Microsoft account:
Most likely, there’s more to the Excel Online defaults than Microsoft is telling and/or the system is changing over time and without notice.
Unlike in Windows or Mac, there’s no option to customize the date format. You have to accept the regional defaults set by Microsoft. It isn’t clearly documents what those defaults are.
Background
In its infinite wisdom, Microsoft has decreed that Excel’s default for date and currency formats is set, not in Excel, but by the operating system. In Windows, go to Control Panel | Region to change the way dates look in Excel. On Mac’s System Preferences | Language and Region | Advanced | Dates.
A worksheet on a computer set to US region will shows dates in mm/dd/yy format like 11/13/2014 but if you open the same worksheet on a computer with UK region the dates will appear in dd/mm/yyyy or 13/11/2014 format. Or you can set a custom date format, as we recommend on page 124 of Office 2013:the real startup guide.
That makes limited sense. Sure, there needs to be a default and using the one in Windows is logical. It’s also useful if the worksheet moves to another computer with a different date format. When that happens the same date cells will appear in the format set for that computer.
However that’s not always convenient. A user might be working on a computer with a different region setting from the one they prefer. Or you simply want to enforce a particular date format for consistency and regardless of the computer settings. It can be confusing for people trying to enter dates and sometimes the wrong date is entered when Excel accepts “12/3/13” as 12th March 2013 instead of 3rd Dec 2013.
Alas, there’s no simple way to set a region at the worksheet level and Microsoft has been deaf to any such requests.
The workaround is to set a custom format for dates but a worksheet-wide setting would be so much more useful and appropriate.
See Also
- Amazon price scraping to Excel
- Date formatted charts in Excel
- Additions to Office Web Apps
- Dates in Excel 2003 – Part 3
- Dates in Excel 2003 – Part 2
- Dates in Excel 2003 – Part 1
