How to change American date format in Excel?

Different country has different date format. Maybe you are a staff of a multinational corporation in America, and you may receive some spreadsheets from China or other countries, you find there are some date formats that you are not accustomed to using it. What should you do? Today, I will introduce you some ways to solve it by changing other date formats to American date formats in Excel.

Change date formats with the Format Cells feature

Change date formats with formula

Quickly apply any date formats with Kutools for Excel

One click to convert mm.dd.yyyy format or other date format to mm/dd/yyyy with Kutools for Excel


One click to convert mm.dd.yyyy or other date format to mm/dd/yyyy format in Excel

Click Kutools > Content > Convert to Date. The Kutools for Excel's Convert to Date utility helps you easily convert mm.dd.yyyy format (such as 01.12.2015) or Thursday, September 11, 2014 or other special date format to dd/mm/yyyy format in Excel. See below screenshot:

doc america date1

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now


Change date formats with the Format Cells feature


With this Format Cells function, you can quickly change other date formats to America date format.

1. Select the range you want to change date format, then right-click and choose Format Cells from the context menu. See screenshot:

2. In the Format Cells dialog box:

  • Select the Number tab.
  • Choose Date from the category list.
  • Specify which country’s date formats you want to use from Locale (location) drop down list.
  • Select the date format from Type list. See Screenshot:

3. Click OK. It will apply the date format to the range. See screenshots:

doc american date


Change date formats with formula

You can use a formula to convert the date format according to following steps:

1. In a blank cell, input the formula =TEXT(A1, "mm/dd/yyyy"), in this case in cell B1, see screenshot:

2. Then press Enter key, and select cell B1, drag the fill handle across to the range that you want to use, and you will get a new column date formats. See screenshot:

As they are formulas, you need to copy and paste them as values.


arrow blue right bubbleQuickly apply any date formats with Kutools for Excel

With the Apply Date Formatting utility, you can quickly apply many different date formats.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select the range you want to change date formats in your worksheet, then click Kutools > Format > Apply Date Formatting, see screenshot:

2. In the Apply Date Formatting dialog box, choose the proper date format you need. and then click the OK or Apply button. See screenshot:

doc america date1

Now all selected dates are changed to the date format you specified.

If you want to have a free trial of this utility, please go to free download the software first, and then go to apply the operation according above steps.


arrow blue right bubble One click to convert mm.dd.yyyy format or other format to mm/dd/yyyy with Kutools for Excel

For some special date format such as 01.12.2015, Thursday, September 11, 2014 or others you need to convert to mm/dd/yyyy format, you can try the Convert to Date utility of Kutools for Excel. Please do as follows.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select the range with dates you need to convert to dd/mm/yyyy date format, then click Kutools > Content > Convert to Date. See screenshot:

2. Then the selected dates are converted to mm/dd/yyyy date format immediately, in the meanwhile a Convert to Date dialog box pops up and lists all conversion status of the selected dates. Finally close the dialog box. See screenshot:

doc america date1

Note: For the cells you don't need to convert, please select these cells in the dialog box with holding the Ctrl key, and then click the Recover button.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now


Related articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +1 Judy Trainer
I have just installed Kutools for Excel in the hope that it would change the Americanised dates to Australian format however I must have done something wrong as I can't seem to get the formatting into the preview screen and even if I apply from the left hand list it doesn't change.

HELP Please

Judy
2013-11-07 06:29 Reply Reply with quote Quote
Permalink 0 Jay Chivo
Quoting Judy Trainer:
I have just installed Kutools for Excel in the hope that it would change the Americanised dates to Australian format however I must have done something wrong as I can't seem to get the formatting into the preview screen and even if I apply from the left hand list it doesn't change.

HELP Please

Judy



Please try to type in these dates in the Column A, and then try to use this utility to convert their formatting.

11/07/2013
11/02/2013
08/09/2013

If you can convert above dates, it means your date are not formatted in date formatting.

thanks in advance.
2013-11-07 09:09 Reply Reply with quote Quote
Permalink 0 Judy Trainer
Thank you for your reply but before I asked for your help I did check that my date column was formatted as dates and it still did not convert them.

Judy
2013-11-08 03:12 Reply Reply with quote Quote
Permalink 0 Ajit
How to convert date format 10.02.1986 to 10/02/1986 using formula?
2013-11-28 10:44 Reply Reply with quote Quote
Permalink +1 ragesh
[quote name="Ajit"]How to convert date format 10.02.1986 to 10/02/1986 using formula?[/quote name = "Ragesh"]
2015-11-24 17:24 Reply Reply with quote Quote
Permalink +1 Praveen
Thanks dude, you solved a big problem in my BIG excel sheet...
thanks alot..
2014-03-15 09:12 Reply Reply with quote Quote
Permalink +3 Preeti
How to Convert date format 10012014 to 10/01/2014 using formula.
Date/No-(100120 14)have no any separation.
2014-04-11 11:13 Reply Reply with quote Quote
Permalink +3 mickey
hello,
when I set date format. that time there have some problem some date change but some date not change..why
2014-05-10 10:06 Reply Reply with quote Quote
Permalink 0 Damaris Neyra
I have the exact same problem. I already tried erasing the excel keys from the windows registry, I have tried to reset excel, and lastly run an office repair but I still cannot make it work, some dates change and some don't even though all of the dates are on the exact same column with the exact same formatting. Did you ever find a way to fix it?
2014-06-10 14:12 Reply Reply with quote Quote
Permalink +1 mickey
when I using date formula then some dates are change but some date are not changing.
2014-05-10 10:10 Reply Reply with quote Quote
Permalink +2 Darren
I find that it does not work when the excel spreadsheet is in a particular format where the month showing is incorrect. E.g European format dd/mm/yyyy with the month showing as a number greater than 12. There are probably better ways of doing it - but I found below works for me if it is entered into the column to the left of the date to be changed. This was for changing american format to european. You can then copy and paste the results back in
IF(ISERROR(FIND("/",C2)),TEXT(C2,"MM/DD/YYYY"),CONCATENATE(RIGHT(LEFT(C2,5),2),"/",LEFT(C2,2),"/",RIGHT(C2,4)))
2015-09-11 17:36 Reply Reply with quote Quote
Permalink 0 Colleen
None of these options worked for me.
2014-09-12 18:18 Reply Reply with quote Quote
Permalink 0 sharonyap
How to change "Thursday, September 11 2014" to "11/09/2014" with Excel formula. Please help
2014-09-30 14:38 Reply Reply with quote Quote
Permalink 0 chandraprakash
Dear All,

Whenever I am trying to download the data from Database. All the fraction numbers i.e., 1/4 (or) 2/3 is converting to date format as 4-Jan or 3-Mar in excel(CSV). I am trying to fix this issue since two days......

Any body can help me out for the above issue...
2014-10-09 11:39 Reply Reply with quote Quote
Permalink +2 vinay
I am copy the date from the web portal to excel, the date format is in 12-24-2015, however i require in 24-Dec-15, Please suggest
2015-04-30 09:14 Reply Reply with quote Quote
Permalink 0 Protap Choudhury
Go to Formate Cell
then select date tab
2016-06-25 05:41 Reply Reply with quote Quote
Permalink 0 VISSU
HI
I WANT CHANGE DATE FORMAT FROM 01.12.2015 TO 01/12/2015
2015-12-15 12:35 Reply Reply with quote Quote
Permalink 0 renuka
I want to change date format from European style to US

8/3/2016 to 3/8/2016

If I use the date format and choose UK settings it just change to

3 August 2016. This date has not happened yet???
2016-03-18 18:46 Reply Reply with quote Quote
Permalink 0 Manny
I have a macro workbook that i have filled and need to create a CSV file from. But when I click create file the date resets itself. To change the date I have to change date format. But the workbook has locked off all the tools. I cannot use the format cell option to do this simple task. Any help would be appreciated.
2016-09-27 00:12 Reply Reply with quote Quote
Permalink 0 Manny
macro workbook is resetting my dates. How can i restore the right one since most tools are locked on the workbook i was sent. Basically i need to create a csv file but the date keeps ruining my file. Any help would be great. Please note i am a novice.
2016-09-27 00:17 Reply Reply with quote Quote
Permalink 0 Russ Moreland
How can I convert dates from 01/01/2017 to 20170101 ?
2016-12-28 16:32 Reply Reply with quote Quote

Add comment


Security code
Refresh