Changing to a Non-US Date Format

by Allen Wyatt
(last updated June 29, 2019)

6

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:

  1. Select the cells you want formatted. (You can, if you desire, select all the cells in a worksheet. You may not want to do that, though, for reasons discussed shortly.)
  2. Display the Home tab of the ribbon.
  3. Click the small icon at the lower-right corner of Number group. Excel displays the Number tab of the Format Cells dialog box.
  4. Click Date at the left side of the dialog box.
  5. Use the Locale drop-down list to choose the area you want. (The Locale drop-down list is visible only after you complete step 4.) In John's case, he should choose English (Australia). Excel then changes what date formats are available in the Type list, just above the Locale drop-down. (See Figure 1.)
  6. Figure 1. Setting Australian date formats in Excel.

  7. Select the desired date format from the Type list.
  8. Click on OK.

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:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
  2. Click Language at the left side of the dialog box. (See Figure 2.)
  3. Figure 2. Specifying a language for use in Office programs.

  4. If English (Australia) is not shown in the top portion of the dialog box, use the [Add additional editing languages] drop-down list to choose it.
  5. Select English (Australia) in the list of editing languages.
  6. Click the Set As Default button.
  7. In the area at the bottom of the dialog box, you should be OK leaving the settings as "Match Microsoft Windows," provided you set Windows to use English (Australia). If you didn't then use the controls to specify the correct language and then click on Set As Default under each list.
  8. Click OK.

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 Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Viewing Your Work Full-Screen

Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the ...

Discover More

Clearing the Contents of a Table

Want to get rid of information within a table, but not the table itself? Here's a guide to understanding the effects that ...

Discover More

Comments in Text Boxes

If you use text boxes in your documents, you may sometime want to place a comment in the text box, the same as you can do ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Partially Blocking Social Security Numbers

Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...

Discover More

Creating 3-D Formatting for a Cell

The formatting capabilities provided by Excel are quite diverse. This tip examines how you can use those capabilities to ...

Discover More

Adding Diagonal Borders

Borders on all sides of a cell are easy to do in Excel. You can also create diagonal borders that run right through the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 5?

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).


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.