While Excel is convenient for many things, it’s not yet convenient for predicting our preferred date format. In this post we’ll be discussing how you can change your default date format for the web version of Excel, as well as why you may want to. As a bonus, we’re also covering how you can change your date formats in Google Sheets.
Solution:
- Visit https://account.live.com/
- Sign in to your Excel account
- Access “Your info”
- Select regional formats
- Select your preferred region
- Optionally further format date
- Optionally further format time zone
After following the steps given above, your date format should now be the one you favour. That being said, there is an alternate method for changing the date format in an Excel file you’re editing through the web version of Excel:
- Open the Excel file in which you want to change the default date format
- Select the column where the dates are contained > right click and select Number Format…
- Choose the correct locale > Check the Set this locale as the preferred regional format
- Click OK to save the changes. The date format change should be immediately reflected in this file and all other Excel files.
We’ve also created a YouTube video that walks you through the steps outlined in this blog post. You can watch it here on our YouTube:
Below is an image assisted walkthrough of how you can set your default date format in the Excel web app. If you feel you need further assistance, please follow along for more detailed support.
Quick links:
- Updating formats in Excel (Web)
- Updating formats in Sheets
- Why you should update your date formats accordingly
Updating formats in Excel (Web) to set default date format
Step 1 – Sign in to your Microsoft account
- The sign in button is located by the top right corner of the page. Use it to begin the sign-in process. Be sure to login to the account you use Excel with. Failure to do so will nullify the effects of future steps.
Step 2 – Proceed to “Your info”
- Select your info from the blue navigation bar at the top of the page. This will load a page we can use to alter our account’s personal information. This is because our regional, date and timezone formats are determined via the configuration options on this page.
Step 3 – Select “Regional formats”
- Clicking this field will produce a pop-up needed to continue. You may need to scroll to the bottom of the list to find this option. It is typically found within the “Language info” category,
Step 4 – Configure options
- Please note all fields here are not required. You need only alter the first field. However, if you would like more specific customisation, the secondary fields are changeable with reversible effects.
Updating formats in Sheets to set default date format
Step 1 – Select “File”
- Open up your spreadsheet, and select “File” from the navigation bar. This will produce a dropdown menu of options.
Step 2 – Select “Spreadsheet settings”
- Locate “Spreadsheet settings” at the bottom of the dropdown. Click it to proceed via the pop-out that will appear.
Step 3 – Configure the “Locales” field
- Configure the locales field by selecting the Country/region in accordance with the formatting you wish to utilise.Optionally also configure your time zone the same way.
Why you should update your date formats accordingly
What are date formats?
A date format refers to the way we present our dates. Varying date formats exist as different countries/regions display their dates in different ways. This means the date format you’re used to may not align with the date format in use by colleagues when relocating to another region.
How format inconsistency can be problematic
Collaboration works best when communication is without abstraction. If one colleague perceives your “10/08/21” as the 8th of October 2021, when you had meant the 10th of August 2021- an error in communication has occurred. When this date is registered without being reformatted, there is further potential for confusion and potential mistakes. Take for instance Excel perceiving your date as a string because it perceived the 31 in 31/01/2021 to be referencing a non-existent month. As opposed to a day in a month. Your spreadsheet is now faulty.
Our related articles:
- How to fix Excel error Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet – Contrary to popular belief, it’s possible for large spreadsheets to be problematic, even for Excel’s 16,384 x 1,048,576 workspace. Albeit, you need not worry. This blog post offers to help you work around the issue you may be facing.
- Excel won’t let me type: Causes and Fixes – Making spreadsheets is easy. Until you can’t type that is. Fortunately for you however, that issue is much easier to resolve (unlike recreating it). This blog post aims to aid you via a step-by-step walkthrough of the solution, as well as a brief look at the potential cause.
- How to stop Excel rounding numbers – What’s better than data you ask? Accurate data. It’s recently been brought to our attention that some of you are struggling with Excel’s automated number rounding. We’d like to help with that. So this blog post will cover decimilazing cells to match it’s values decimal place and additionally, formatting cell widths for easier reading of values.
- How to hyperlink to hidden sheets in Excel – Tired of toggling sheet visibility to access nested sheets? The focus of this blog is to teach you how to hyperlink between your initial spreadsheet, and your nested hidden sheets. All without leaving Excel.