Google Sheets Date Format Australia: Set DD/MM/YYYY

To use Australian dates in Google Sheets, set the spreadsheet locale to Australia under File > Settings, then select the date cells and choose Format > Number > Custom date and time to apply dd/mm/yyyy. The locale helps Sheets interpret dates you enter; the cell format controls how a date value appears.

What Australian date format means

In Australia, dates are commonly written day, month, year: for example, 3 April 2026 is written as 3/4/2026, or 03/04/2026 when each part has two digits. This differs from the month/day/year order commonly used in the United States, where the same date is written as 4/3/2026. That difference makes short numeric dates easy to misread when a spreadsheet is shared across countries.

Google Sheets keeps these two ideas separate:

Locale sets regional defaults, including how Sheets interprets many date and number entries and which formats it offers by default.
Cell number format determines how a date value is displayed in a particular cell or range.

A date can therefore be stored as a valid date but shown in an unexpected order, or it can look like a date while actually being plain text. Knowing which situation you have is the key to fixing it reliably.

Set the spreadsheet locale to Australia

In Google Sheets in a web browser, open the spreadsheet and choose File > Settings. On the General tab, choose Australia in the Locale list, then save the settings. This changes the regional settings for the spreadsheet, rather than just changing the appearance of one selected column.

The locale is a spreadsheet-level setting. It can affect how date, number, and currency values are interpreted or displayed by default, and it applies to people working in that spreadsheet, not only the person who changed it. If several people enter data, agree on the locale and date convention before they begin. Otherwise, two collaborators may type the same short date expecting different days and months.

Locale and time zone are separate settings in the same settings area. Australia has multiple time zones, so choose the appropriate time zone if the sheet contains times, timestamps, or date-time calculations. Setting the locale to Australia does not automatically choose the correct Australian time zone, and changing the time zone is not a substitute for setting the date order.

Format existing cells as DD/MM/YYYY

To display existing dates with two digits for the day and month and four for the year, select the relevant cells or column. Choose Format > Number > Custom date and time, then select or enter a pattern such as dd/mm/yyyy and apply it. A valid date value for 3 April 2026 will then appear as 03/04/2026.

The format changes the display, not the underlying date. For example, changing a cell from 3 April 2026 to a format that shows only the month and year does not remove the day from its stored value; it is simply hidden. This matters when sorting, calculating elapsed days, or using the date in another formula.

Choose a pattern that suits the people reading the sheet. Common options include:

d/m/yyyy for 3/4/2026, without leading zeros.
dd/mm/yyyy for 03/04/2026, with consistent two-digit day and month fields.
d mmmm yyyy for 3 April 2026, which is clearer when a sheet may be read internationally.
A date-and-time pattern when the exact time matters, rather than a date alone.

For reports, filenames, or forms, a written month can prevent day-month confusion. For compact data tables, a consistent numeric pattern may be easier to scan.

Enter dates without ambiguity

After setting the locale, enter dates in day/month/year order. A four-digit year is useful because it avoids uncertainty about whether a two-digit year means 1926, 2026, or another year. For instance, enter 3/4/2026 to mean 3 April 2026 under the Australian convention. If you are unsure how Sheets interpreted an entry, select the cell and check its displayed value after applying a clear date format.

Short entries such as 4/5 are risky. They leave out the year and can be interpreted differently depending on the spreadsheet settings and the way the value is entered. For important records, enter the full date and verify a sample before filling a large range. When a date is particularly sensitive to interpretation, writing the month name, such as 4 May 2026, makes the intended order obvious to a person; confirm that Sheets has stored it as a date rather than text.

You can also create a date from separate year, month, and day numbers. With the date intended for cell A2, enter =DATE(2026,4,3); the result is the date 3 April 2026. Apply the Australian display format to A2 to show 03/04/2026. The DATE function takes year, month, and day in that order even though the displayed date may put the day first.

Dates that are text or imported from another file

Applying a date format will not turn text into a date. If a cell contains text such as 03/04/2026, changing its number format may have no visible effect because Sheets has no numeric date value to reformat. Text often arrives through copied web content, CSV files, or data exported from software using a different regional convention.

Check a questionable cell by trying a date format and seeing whether the display changes as expected. Also notice alignment: text is often left-aligned by default, while numbers and dates are often right-aligned, although manual formatting can override alignment, so this is only a clue. A formula that performs date arithmetic or a sort that places entries in a strange order can also reveal that values are text or were interpreted incorrectly.

Before importing or pasting a large dataset, check the spreadsheet locale and inspect a few known dates after import. There are two different failure cases:

If the dates remain text, convert or re-import them using a process that interprets the source order correctly; changing the display format alone is not enough.
If Sheets already converted an ambiguous entry to the wrong date, changing the format only changes how that incorrect date is shown. Correct the underlying value using the original source or a verified year, month, and day.

Do not assume that every string containing slashes is a date, or that a visually plausible result is correct. When converting a column, test the conversion on a small copy first and compare it with dates whose meaning you know.

How dates work in formulas and sorting

Sheets treats a true date as a numeric value that can be used in calculations. This is why a valid date can be sorted chronologically and used to find days between events, regardless of whether it is displayed as 03/04/2026 or 3 April 2026. A text string that merely resembles a date does not behave the same way in date calculations.

Formatting is especially important when sharing formulas or charts. A formula may return the correct date value while the result appears in a different regional style because the destination cell has another number format. Set a clear format on the output cells rather than relying on the way a date happens to look in the cell where it was first entered.

For date-time values, remember that a cell can contain both a date and a time even if the display shows only the date. If you need to compare events that happen on the same day, use a format that reveals the time as well. Also check the spreadsheet time zone when collaborating across regions; it is a distinct concern from whether the date is written day first.

Common problems and practical fixes

If dates continue to appear in month/day/year order, first confirm the spreadsheet locale in File > Settings. Then select the cells and explicitly apply the custom pattern dd/mm/yyyy. Locale sets regional defaults, but an existing cell can retain a previously applied format, so changing only the locale may not alter the display you want.

If 04/05/2026 appears as 05/04/2026 after you change settings, do not assume that the locale change repaired it. The value may have been interpreted as 4 May or 5 April when it was first entered. Compare it with the original record, correct the underlying date if necessary, and then apply the desired format. An ambiguous date cannot be safely corrected from its appearance alone.

If only some rows behave differently, inspect the whole column for mixed types and formats. Some cells may be real dates, others text, and still others dates with a format copied from another sheet. A uniform column format helps with presentation, but it does not convert text or verify the dates. Clean up those values separately and test sorting before relying on the data.

Google Sheets and Excel regional settings

Google Sheets uses a spreadsheet-level locale setting, while Excel can use regional settings associated with the workbook, operating system, or application, depending on the task and version. The broad principle is the same in both: date display and date interpretation are related but separate. A format can make a valid date look Australian without guaranteeing that an ambiguous imported value was interpreted correctly.

If a file moves between Sheets and Excel, check both the date values and the display format after opening or exporting it. A format may travel with the file, but a collaborator’s regional settings or import method can still affect how ambiguous text is understood. For shared datasets, use four-digit years, document the expected order, and consider a month-name display for dates that people must interpret without context.

Frequently Asked Questions

How do I make all dates in Google Sheets DD/MM/YYYY?
Select the date column or range, then choose Format > Number > Custom date and time and apply dd/mm/yyyy. Set the spreadsheet locale to Australia under File > Settings as well, especially if people will enter new dates.

Does changing the locale fix dates already in the sheet?
Not necessarily. It changes the spreadsheet’s regional defaults, but cells can keep their existing display format, and dates already interpreted incorrectly remain incorrect. Check the actual date value against a reliable source, then fix the value and format.

Why does 04/05 show as May 4 instead of 4 May?
The value may have been entered or imported while the sheet was using month/day order, or it may be text. Check the locale, inspect the cell, and verify the intended date before changing it; the visible order alone cannot prove which date was meant.

Can I display dates as 3 April 2026?
Yes. Select the cells and use Format > Number > Custom date and time to choose a pattern such as d mmmm yyyy. This displays the month name while retaining a valid date value.

Does setting Australia change the spreadsheet for collaborators?
Yes. The spreadsheet locale is a shared setting, so it can affect defaults for everyone using that file. Let collaborators know the agreed date order, and use an explicit cell format where consistent display matters.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.