Search code examples
exceldateformat

How to swap date and month digits in excel

I have a date format of mm-dd-yyyy (03-12-2018). Excel reads it as 3rd December 2018. The actual date is 3rd March 2018, I want to convert it into 12th March 2018 in dd-mm-yyyy format.

Let's assume our data is in cell B2 and it reads 03-12-2018 i.e 3rd December 2018 I have tried =Date(right(B2,4),mid(B2,4,2),left(B2,2)). It gives 12-02-3440 as output due to excel date-time code.

Solution

  • This most likely will not solve your underlying problem, but to swap month-day in an Excel date, you can use:

    =DATE(YEAR(B2),DAY(B2),MONTH(B2))

    Then format the cell to show whatever date format you want.

    However, if your underlying problem stems from opening a csv or txt file, you'll need to change that method to an import method to sort out your various date issues.