How to convert dd/mm/yyyy to mm/dd/yyyy in Excel cells?
In Excel, the standard date format is mm/dd/yyyy, but in some cases, the dates in the files you received or import are in the format dd/mm/yyyy, how can you change these dates from dd/mm/yyyy format to mm/dd/yyyy in Excel as below screenshot shown?
There is a formula that can quickly convert dd/mm/yyyy to mm/dd/yyyy date format.
Select a blank cell next to the dates you want to convert, type this formula =DATE(VALUE(RIGHT(A9,4)), VALUE(MID(A9,4,2)), VALUE(LEFT(A9,2))), and drag fill handle over the cells which need to use this formula.
Note: A9 is the date you use to convert.
Also, you can split the date format into columns by the Text to Columns feature, and then combine with a formula with the new order.
1. Select the dates you want to convert, and click Data > Text to Columns. See screenshot:
2. In Step 1 of Convert Text to Columns Wizard, check Delimited option. See screenshot:
3. Click Next, and check Other, and type / into the next to text box. See screenshot:
4. Click Next, and select a cell to output the data in Destination box. See screenshot:
5. Click Finish. Then the dates have been split into columns.
6. Select a blank cell, and type this formula =C1&"/"&B1&"/"&D1, drag auto fill handle down to the cells needed this formula.
Note: In the formula, C1, B1 and D1 are the delimited cells.
Quickly and easily convert date to other date formatting in Excel
|The Apply Date Formatting of Kutools for Excel can quickly convert a standard date to the date formatting as you need as, such as only display month, day, or year, date format in yyyy-mm-dd, yyyy.mm.dd and so on. click for full- featured free trail in 30 days!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
- How to convert dD.MM.YYYY to date format (mM/DD/YYYY) in Excel?
- How to calculate net work hours between two dates excluding weekends or holidays in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 5 months agoname faizahmad last name timori 1987
To post as a guest, your comment is unpublished.· 1 years agoAbove solution instead of Kutool's is very helpful & easy to understand.
To post as a guest, your comment is unpublished.· 1 years agoThis is the ultimate generic and simple solution -
To post as a guest, your comment is unpublished.· 2 years agoThank you !
This is amazing, you have save me lots of time and effort.
To post as a guest, your comment is unpublished.