In Excel, the standard date format is mm/dd/yyyy format. But sometimes the dates in the files you received or import are displayed in dd/mm/yyyy format. You can easily change this date format from the cell formatting option. Here, we will show the alternate method, to convert the date format from dd/mm/yy to mm/dd/yy format in Excel using formulas. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- If you want to convert the date format in Excel, you can use the below formula.
=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))
Syntax Explanations:
- DATE – In Excel, the DATE function will combine three different values and returns them as a date.
- RIGHT – This function helps to extract the characters from the right side of a text string. Read more on the RIGHT function.
- MID – The Excel MID function is used to extract the number (starting from the left side) or characters from the given string
- LEFT – It will extract the first character (starting from the left side) or characters from the given string based on the number you specified. Read more on the LEFT function.
- A1 – It represents the input time on your worksheet.
- Plus operator (+) – This symbol is used to add the values.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Practical Example:
Refer to the below example image.
- Here, we will enter the input dates as dd/mm/yy format in Column B.
- Now we are going to convert them into mm/dd/yy format.
- Select any cell and type the above-given formula.
- Finally, press ENTER to get the result out, if you need, drag the fill handle over range to apply the formula.
Closure:
So this is how we convert the date format from dd/mm/yy to mm/dd/yy in Excel. Hope you like this blog post. Don’t forget to share your worthwhile feedback and any queries about this article. I reply to queries frequently.
Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **
Top comments (0)