Data Import & Export

Convert Australian Text Dates to US Format in Excel

Bushra ParveenBushra Parveen Nov 20, 2026 869 views

Question details

Convert Australian day-month-year dates stored as text into Excel dates displayed in US month-day-year order.

Convert Australian Text Dates to US Format in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains text values such as 6/12/18 that need to be converted from Australian date order to US date format.
Observed behavior
Changing the cell format alone does not convert the text values or change their date order.
Before you start

Confirm that the source values use day-month-year order; for example, 6/12/18 means December 6, 2018. Check that the selected range contains only the date values you want to convert.

Solution 1Recommended

Convert DMY text dates with Excel Text to Columns

Use Text to Columns to interpret the source values as day-month-year dates, then apply a US display format.

1
Select the dates

Select the column or range containing the text dates in Excel.

2
Open the conversion wizard

Go to Data > Text to Columns. Choose Delimited, click Next, clear any selected delimiters, and click Next again.

3
Set the source date order

Under Column data format, select Date and choose DMY. Click Finish to convert the text values into dates.

4
Apply the US display format

With the converted cells selected, open Format Cells, choose Number > Custom, enter mm/dd/yyyy, and click OK.

5
Check the results

Review a known date in the converted range. For example, 6/12/18 in DMY order should display as 12/06/2018.

Convert DMY text dates with Excel Text to Columns
Use the source date order: Choose MDY in the wizard only if the original text is already in month-day-year order. Check the resulting years when source dates use two-digit years.
Convert dates in WPS Spreadsheet

Convert Australian text dates in WPS Spreadsheet

Use a formula in WPS Spreadsheet to turn DMY text with two-digit years into real dates, then apply a US date format.

  1. 1. Open the spreadsheet: Open the file in WPS Spreadsheet and identify the column containing DMY text dates.
  2. 2. Enter the conversion formula: If the first text date is in A2, enter =DATE(2000+VALUE(RIGHT(A2,2)),VALUE(MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)),VALUE(LEFT(A2,FIND("/",A2)-1))) in an empty cell. This formula assumes slash-separated DMY text with a two-digit year from 2000 through 2099.
  3. 3. Fill the formula down: Drag the formula cell's fill handle down alongside the remaining dates.
  4. 4. Format and verify the dates: Select the formula results, open Format Cells > Custom, and enter mm/dd/yyyy. Check that a known value such as 6/12/18 displays as 12/06/2018.
Convert DMY text dates without changing the source columnDisplay converted dates in US month-day-year orderOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface for everyday editing
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't changing the cell format convert my dates?

A date format changes how Excel displays a date value; it does not turn text into a date. Use Data > Text to Columns and select Date > DMY first.

What should 6/12/18 become in US format?

If the source uses Australian DMY order, it means December 6, 2018. After conversion and formatting as mm/dd/yyyy, it displays as 12/06/2018.

Should I choose DMY or MDY in Text to Columns?

Choose DMY when the original text is in day-month-year order. Choose MDY only when the original text is already in month-day-year order; the setting describes the source, not the desired display.

How can I check that Excel converted the text into a real date?

Select a converted cell and temporarily change its format to General in Format Cells. A real Excel date displays as a serial number; you can then reapply the mm/dd/yyyy format.