Written by Allen Wyatt (last updated February 20, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
John wonders how he can get the date in Excel to default to the Australian format (dd/mm/yy) instead of the US format (mm/dd/yy). He finds it annoying and time-consuming to have to correct it each time.
There are a few things at play here when it comes to dates in Excel. The first thing (and, quite honestly, the most important thing) is how you have Windows configured to handle dates. How you can make this change is best left to our sister site at WindowsTips: Understanding Regional Settings. Changing the regional settings in Windows affects how dates appear in Excel, by default.
Notice I said, "by default." It is possible to format a worksheet so that just that worksheet uses a particular area's date format. If John gets a workbook from a friend overseas (say, from someone in the US or Canada), it may be possible that the formatting in that worksheet has been configured for that area, and what John has set in Windows will take a back seat to how the worksheet is configured. Here's how you can modify the date formats used; it could be that this was the way that the workbook John received from overseas (if, indeed, he did receive such a workbook) was formatted:
Figure 1. Setting Australian date formats in Excel.
Note that if you select all the cells in a worksheet and use this approach, you can end up messing up all the other formatting in the worksheet. For instance, if some cells were formatted using a Number format, a Currency format, or an Accounting format, then changing the formatting for those cells will modify how the information in those cells is displayed. This is why it is best to apply the Date format to only those cells that need the format you want used.
Speaking of applying formats to cells, you could also create a custom date format any time you want. Just display the Format Cells dialog box, as done in the earlier steps, and click Custom at the left side of the dialog box. You could even, if desired, create a style that utilizes your custom date format.
Finally, you'll probably want to make a few changes in how Office interprets international information. Follow these steps:
Figure 2. Specifying a language for use in Office programs.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13649) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When you are formatting your worksheet, Excel lets you easily merge adjacent cells together. If you want to wrap the text ...
Discover MoreIf you need to easily change the font colors in a group of cells, one of the esoteric commands Excel provides is the ...
Discover MoreIf you have a range of numeric values in your worksheet, you may want to change them from numbers to text values. Here's ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-07-06 17:32:13
John Mann
Like GStroeder, I have also been using the ISO number format for many years, back into theCa 1980s or maybe sooner (I really don't remember). In Canada, where I live, we see both the UK/Australian format 6/7/2019 and US/French format 7/6/2019 in use, which is why |I switched to the ISO format - it's not ambiguous. Any time I can control the date format used in any document I will either use the ISO format or a format which includes the month name, and a 4-digit year.
2019-07-02 10:11:55
GStroeder
"I would love to see a universal date format implemented..." As it turns out, there is one. As Rod pointed out, it is ISO 8601. I've been using this format since the early 1980's but only learned of the ISO standard in this century. I advocate for it every chance I get. Just google ISO 8601 and read. It also get into time zones, time formats and Week numbers.
As for dates, we have Days, Months and Years. There are 6 permutations (I'm ignoring 2 digit years). No one (that I know of) puts Year in the middle, so those 2 versions are gone. Of the 4 remaining, I have never seen or heard of YYYY-DD-MM (as it really makes no sense), so we can ignore that one. That leaves us with the common MM-DD-YYYY and DD-MM-YYYY and the ISO 8601 standard that is YYYY-MM-DD. The 'common' formats are dangerous to use with international dealings if the DD is 12 or less. One can get days and months reversed. Therefore, use the YYYY-MM-DD format.
Why? DESCENDING TIME UNITS. Biggest to smallest. One can even keep going down into Hours, Minutes, Seconds.
Year must be 4 digits to avoid any 'which century' confusion. The separator is either a dash, or nothing. No slashes or dots or spaces etc.
While using an abbreviation for month is tempting, this leads into language issues. ISO 8601 sticks to all numerals for dates.
Looking thru "Date format by country" is interesting. I'm in Canada and we are (were?) guilty of accepting / using both of the 2 common formats and the ISO 8601 format. (I guess we can't make up our mind!) Officially, we are now following ISO 8601 and I have noticed that there is a trend towards more use of this format.
Aside - Have you noticed that airlines no longer use AM and PM times? Again, potential confusion, especially for those that are from an area where the norm is the 24 hour clock.
It's about clarity of communication. And the world just keeps getting smaller. I work with a small company, but we deal internationally. One has to break out of local thinking. And in this case, local can mean country.
2019-07-02 08:55:55
Rod Grealish
There is an International standard which addresses the representation of dates and times for information exchange - "ISO 8601 Data elements and interchange formats – Information interchange – Representation of dates and times" (see the Wikipedia article entitled "ISO 8601" for the details).
For national date formats see the Wikipedia article "Date format by country".
It always seems illogical to me that we are happy to accept the formats DMY and MDY for dates. Would we be happy with SMH or even MSH for times? What about a vehicle odometer showing the distance travelled with the digits (thousands, hundreds, tens, units) listed in some arbitrary manner eg units, thousands, tens, hundreds?
2019-07-01 04:41:33
Graham
Today's date 07/01/2019 would be understood as July 1st 2019 in USA but as 7th January 2019 in many (most?) other countries.
I believe that the best solution is to use a custom format that uses an abbreviated month name, either mmm-dd-yyyy or dd-mmm-yyyy.
This way both Jul-01-2019 and 01-Jul-2019 should be understood by everyone, regardless of their 'standard' method for displaying dates.
Note – This only affects the displayed date. The actual entry of a date is controlled by the language setup of the user's computer. For example in the USA, a user would still enter 07/01/2019 even if it is formatted as dd-mmm-yyyy and therefore displayed as 01-Jul-2019.
In some languages the use of 'mmm' may give a 4 or more letter abbreviation, for example in French the abbreviation for January (Janvier) is 'Janv".
This situation does not only apply to Excel, but also other Office applications. It also occurs on the web in general. For example as a European I would be confused booking a hotel room if the date is only displayed as 07-01-2020, for July next year. I would think that it was 7th January 2020!
2019-06-29 11:51:06
Will
I would love to see a universal date format implemented that satisfies chronological sorting in instances outside of spreadsheets.
I use yyyy.mm.dd nearly always outside of spreadsheets and sometimes inside of spreadsheets. It's logical (to me).
2019-06-29 07:52:09
Will
I would love to see a universal date format implemented that satisfies chronological sorting in instances outside of spreadsheets.
I use yyyy.mm.dd nearly always outside of spreadsheets and sometimes inside of spreadsheets. It's logical (to me).
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments