Changing Date Format

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.

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.

change data format2

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

change data format1

#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.

change data format3

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

change data format4

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.

change data format5

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

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)…
Related Posts

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Change Date to The Day of Week in Excel
How to Change Date to The Day of Week 6

In excel, we can type a date properly if set cell format as ‘Date’. If there is a list includes multiple dates, is there any way to convert these dates to the days of week accordingly? Actually, there are some ...

How to Create Increment Number with Texts in Excel
Create Increment Number with Texts 11

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel
Generate Random Time 7

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...

How to Combine Text and Date into one Cell in Excel
combine text and data into one cell2

This post will guide you how to combine text and dates in the same cell in Excel. How do I combine text and date values into one Cell in Excel 2013/2016. Combine Text and Date into One Cell Assuming that ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Convert Military Time to Standard Time in Excel
convert military time to standard5

This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel.  How do I Convert Standard time to military ...

How to Delete or Remove Year from a Date in Excel
delete year from date2

This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel. Remove Year from a Date Assuming that you ...

How to Convert Date to YYYY-MM-DD format in Excel
convert date format yyyymmdd5

This post will guide you how to convert the current date to a specified date format in Excel. How do I convert date to YYYY-MM-DD format with Format Cells Feature in Excel. How to convert date format to a specific ...

How to Concatenate Cells and keeping Date Format in Excel
concatenate cells and keep date format1

This post will guide you how to concatenate cells and keeping data format in Excel. How do I concatenate cells and retain the certain Date format with a formula in Excel 2010/2013/2016. Concatenate Cells and Keeping Date Format Assuming that ...

Sidebar