Change date to American date format in Excel (Easy tutorial)
Working in a multinational corporation often involves dealing with data from various countries, each with its unique date formats. If you're in America and receive spreadsheets formatted with non-American date styles, it's crucial to convert these to formats you're familiar with. This article will guide you through the process of adjusting various international date formats to the American style in Excel, ensuring your data remains consistent and easily understandable across your organization.
Change date formats with the Format Cells feature
Quickly apply any date formats with Kutools for Excel
Change date formats with formula
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:
To create custom date format for your own, in the Format Cells dialog box, click the Custom option in the Category listbox, and then type the date format you want into the Type box, see screenshot:
● Date Format Codes
Here are some common codes used in Excel to format dates:
Format Code | Description | Example |
---|---|---|
d | Day of the month as numbers without leading zeros | 1, 2, 10, 31 |
dd | Day of the month as numbers with leading zeros | 01, 02, 10, 31 |
ddd | Day of the week as a three-letter abbreviation | Mon, Tue |
dddd | Full day of the week name | Monday, Tuesday |
m | Month as a number without a leading zero | 1, 12 |
mm | Month as a number with leading zeros | 01, 12 |
mmm | Month as a three-letter abbreviation | Jan, Dec |
mmmm | Full month name | January, December |
yy | Last two digits of the year | 23 (for 2023) |
yyyy | Full four-digit year | 2023 |
● Example Custom Formats
- mm/dd/yyyy: This is a typical American format, displaying the month, day, and year in numerical form with slashes.
- dd-mm-yyyy: Common in many parts of the world, this format uses dashes instead of slashes.
- dddd, mmmm dd, yyyy: This format spells out the day of the week and the month, followed by the numeric day and the full year, which is useful for printed documents where clarity is essential.
Quickly apply any date formats with Kutools for Excel
Unlock the power of Excel date formatting with ease using Kutools for Excel! Are you tired of manually changing date formats in your spreadsheets? With Kutools, seamlessly switch between different date styles with just a few clicks. Experience unmatched efficiency as Kutools for Excel lets you apply customized date formats instantly across multiple cells and sheets.
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:
Now all selected dates are changed to the date format you specified.
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.
One click to convert non-date formats to real date formats
Transform your data with a single click using the Convert to Date feature from Kutools for Excel! Struggling with non-date formatted data scattered across your spreadsheets? Kutools streamlines your data management by instantly converting text-based dates into real, actionable date formats. Say goodbye to the hassle of manual conversions and errors that slow down your workflow. Detailed information of Kutools for Excel... Free trial...
The guide details methods to convert international date formats to American standards in Excel. It introduces Excel's Format Cells feature, formula and the Convert to Date utility from Kutools for Excel. These tools allow users to adapt date formats quickly. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Related articles:
- Quickly convert birthdate to age in Excel
- Change multiple dates to day of week
- Convert date to weekday name or month name
- Remove time from date
- Remove year from date
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!