Change Dates from MM-DD to DD-MM Format and Vice Versa

One of the most troubling problems while working with Dates is it’s Format. Often you’ll run into scenarios where you’ll need to change the date from MM-DD format to DD-MM format or vice versa.

In this tutorial, I’ll share different ways to change the Date format but using Power Query. If you are more interested in ways to work this out with Excel, hop over to one my dated blog posts – Change Date Format dd-mm to mm-dd using Excel (point 4)

Let’s begin shall we?

 

Watch a Video to begin with ?

 

MM-DD to DD-MM Format – Dates stored as Text

If you look through the dates below you’d find dates until December and thereafter the 13th month starts!!? But hey, we don’t have a 13th month, which means these dates are in MM-DD format and need to be converted to DD-MM format.

1. Look carefully – All these dates are stored as text values.
2. I am sure you already know but the Dates in Excel are positive numbers and need to be in a proper Date format (and not Text) in order to carry out any calculations or apply Date filters.

Let’s pull these dates into Power Query and work from there.

Once the dates are loaded in Power Query..

1. Click on the Data Type Icon
2. Choose the option Using Locale
3. In the Using Locale Box – Set the data type to Date
4. And the Country to English (United States)

And BaBam! The Dates are now in the correct format. Sweet

Notice the Subtlety – Since my laptop settings have been set to DD-MM (Indian Date) format all the dates changed to January Dates in dd-mm format. Which means that Power Query read the dates in the mm-dd (US format) but converted them into the local date format set in my computer.

Sometimes changing the date format isn’t so simple, see this case.

 

MM-DD to DD-MM Format – Dates stored as Date

Again the same Dates (in mm-dd format) but only this time these dates are in the Date format.

1. Notice that some dates are right aligned (i.e. changed to date) but others are left aligned (i.e. in a text format)
2. Since my laptop is set to Indian Date format (DD-MM), it wrongly interpreted the dates until December and there after for the 13th Month onward it shows text, since it cannot be converted to a valid date.
3. Well first off all these Dates are Jan dates and secondly all Dates until 12-Jan-2008 can be written in both DD-MM and MM-DD format hence the dates are wrongly interpreted.

 

Let’s load them in Power Query. The change type to Local US Date format ins’t going to work straightaway on this, so let’s try something different.

1. Notice – At the Source Step the data type automatically is set to any.
2. Since some of values were in Date format so those Dates show up as a Date Time format in Power Query.
3. If you try converting the Dates in English-US format, only the Dates stored as a Text (those left aligned) will change and not other Dates (which are right aligned in Date Time format) – Yes this behavior is quite strange!

 

Step 1 – First let’s convert all the Dates to Text and also remove the Time Stamp

I will add a new column. Add Column Tab >> Custom Column and write the following formula..

= Text.From ( try Date.From ( [Dates] ) otherwise [Dates] )

1. Even though the data type indicates any, all these dates are now text without the time stamp.
2. And that is exactly what we want.

 

Step 2 – Convert the Text Dates to MM-DD Format

Now if I change the type to Date and set it as English-US format using Locale option, it would work!

 

MM-DD to DD-MM Format – For the M Code Savvy!

If you have cringed about adding a helper column to fix the date format problem, most likely you are savvy with M code. Alternatively I can write this short M code to make the same this work (without adding a column of course!)

let
    Source = Excel.CurrentWorkbook() { [ Name='Date' ] } [ Content ],
    DatestoText = Table.TransformColumns ( Source, { 'Dates', each Text.From ( try Date.From ( _ ) otherwise _ ) } ),
    #'Changed Type' = Table.TransformColumnTypes ( DatestoText, { { 'Dates', type date } }, 'en-US' )
in
    #'Changed Type'

Conclusion and be cautious about..

To convert Dates from MM-DD to DD-MM (or vice versa) format requires just a simple built-in option to read the date in other country’s local format (i.e. Using Locale). But the problem arises when all Dates in Power Query aren’t stored in the Text format.

That is when you should use the trick to first get all the Dates into a text format and then set them to either DD-MM or MM-DD format.

 

If there was something else that you were looking for and your problem is still unresolved, drop me a comment and I’d do my best to help ya out.

Cheers

 

More on Power Query

1. Change Date Format Using Excel
2. Calculate Age in Years and Months in Power Query
3. Calculate Fiscal Year and Qtr in Power Query
4. Dynamic Column Selection
5. Dynamic Column Renaming