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.
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!
Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...Discover More
The formatting capabilities provided by Excel are quite diverse. This tip examines how you can use those capabilities to ...Discover More
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
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.