Excel Formula to convert dd/mm/yyyy to mm/dd/yyyy

dd-mm-yyyy to mm-dd-yyyy
Table of Contents

Convert dd/mm/yyyy to mm/dd/yyyy in Excel (with Images)

What is Excel Date Format?

By default Excel Date format is mm/dd/yyyy. There is a bigger challenge due to this default format!

What if you save a date in dd/mm/yyyy format 1/7/2014 (1st July 2014) but Excel will read it as January, 7, 2014 (1/7/2014).

The first option that comes to mind would be Right click > Format Cells > Date and select the desired date format, but that will not work here.

Because Format Cells option will only change the date format Type and it will not convert the date format, so the best way to covert date format in excel is to use the below mentioned excel formula.

In this post we are discussing about the excel formula that will help you to convert Date Format in excel from dd/mm/yyyy to mm/dd/yyyy.

Eg: January, 7, 2014 (1/7/2014) to July, 1, 2014 (7/1/2014)

Why is it?

This Excel formula is very helpful when you have a huge data in dd/mm/yyyy format which was supposed to be in mm/dd/yyyy format.

As mentioned earlier fortunately or unfortunately the default option in Excel reads mm/dd/yyyy so the July 1st Excel reads as January 7th.

Since the data is huge you cannot change it one by one, In such cases you can use the below formula!

How to convert dd/mm/yyyy to mm/dd/yyyy in excel?

If you have the Date in Cell B1 then copy paste the below formula in Cell C1 and change the Cell C1 format to ‘Date’. That’s it!!!

				
					 =IF(B1="","",IF(DAY(B1)>12,DAY(B1)&"/"&MONTH(B1)&"/"&YEAR(B1),VALUE(DATE(YEAR(B1),DAY(B1),MONTH(B1)))))
				
			

Dd/Mm/Yyyy To Mm/Dd/Yyyy

Lazy to read? See the YouTube video on how to convert dd/mm/yyyy to mm/dd/yyyy in excel:

Watch How to Convert dd/mm/yyyy to mm/dd/yyyy

Let me know how helpful was this formula or if you know any other better ways to convert the date format in excel from dd/mm/yyyy to mm/dd/yyyy.

I’m sure you work on lots of Excel Functions and Formulas, so I Strongly Recommend you to take the below Excel Course which will teach you more than 100 essential Excel Formulas! Its a worth Investment in Yourself! DO IT NOW!

Learn Excel formulas with this comprehensive course taking you from the fundamentals to writing advanced formulas

Anson Antony
Anson Antony
Anson is a contributing author and founder at www.askeygeek.com. Learning anything new has always been his passion, askeygeek.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

11 Responses

  1. I need timestamp as well in converted column

    05/12/2021 11:30:33 AM convert to 12/5/2021 11:30:33 AM,
    Can yu please help me on this?

  2. Thanks for the info.

    I am stuck with below error while using this formula. I am trying to convert below dates, however I get the error #Name
    I need timestamp as well in converted column

    5/21/2021 11:30:33 AM convert to 21/5/2021 11:30:33 AM,
    Can yu please help me on this?

  3. =SI(B1=””,””,SI(DIA(B1)> 12,DIA(B1)&”/”&
    MES(B1)&”/”&AÑO(B1),
    VALOR(FECHA(AÑO(B1),DIA(B1),MES(B1)))))

    1. Hi Vijay,

      Thanks letting me know this!

      You can use the below formula to covert dates in dd/mm/yyyy to mm/dd/yyyy even if the day is more than 13.

      =IF(B1=””,””,IF(DAY(B1)>12,DAY(B1)
      &”/”&MONTH(B1)&
      “/”&YEAR(B1),VALUE(DATE(YEAR(B1),
      DAY(B1),MONTH(B1)))))

      I have updated the formula in the post!!

      Thanks,
      Anson

      1. I COULDNT SEE ANY CHANGES IN THIS NEW FORMULA. IT IS NOT WORKING IF THE DAY IS MORE THAN 12. PLEASE HELP ME TO SOLVE.

Leave a Reply

Your email address will not be published. Required fields are marked *

Congratulations!
You Made It,
Don't Close!
Enter your chance to win 100,000 UberTTS Character Credits
Thank you for your visit!

This popup won’t show up to you again!!!

UberTTS 100K Easter Egg
Share to...