by Allen Wyatt
(last updated June 29, 2019)
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 Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to replace the formats applied to some cells with a different format? You can use Excel's Find and Replace tool to ...Discover More
Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.Discover More
Hiding information in one or more cells can be a challenge. This tip presents several different techniques that can help ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.