This post will guide you how to convert dates between Australian and US American formats in Excel. How to change the data format from your local locale to other locale, such as: US or South Africa. As the different country may be has different data format. So this post will talk that how to change the date Format for different country.

Assuming that you have a list of data that contain dates values and its data format will keep the default format based on your system locale. And you want to change the data format from the current format to Australian data format. How to achieve it. This post will introduce two methods to change the Date Format in your worksheet.

1. Changing Date Format with Format Cell Feature

To change the date format as your need in Excel, you can use the Format cell feature, just do the following steps:

#1 select the range of cells that you want to convert date format.

#2 right click on the selected range, select Format Cells from the drop-down menu list. And the Format Cells dialog will open.

#3 click Number tab to switch the Number tab in the Format Cell dialog box, and select Date category from the Category list box. And select the date format from the type list box. And select English (Australia) from the locale drop-down list box. Click Ok button to confirm the date format setting.

#4 the data format in the selected range of cells have been changed as you need.

2. Change Date Format with Formula

You can also use the TEXT function to create a formula to change the date format for the date value in cell in Excel. For example, you want to change the date format to “yyyy/dd/mm”, just use the following formula based on the TEXT function:

=TEXT(B1,"yyyy/dd/mm")

Type this formula into a blank cell C1, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

You will see that the data format of the date values in the range of B1:B3 haven been changed.

3. Video: Change Date Format

This Excel tutorial, where we’ll unravel the art of changing date formats in your spreadsheets in Excel.

4. Related Functions

• Excel Text function
  The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…