One of the things which continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they can still catch us out from time-to-time. Even with Power Query (Excel’s tool for manipulating data), we can run into issues when trying to change a column into a date type. Normally this occurs when working with different country date formats.
The date format used in the US is month – day – year. Yet, it is day – month – year in the UK. Then there is the ISO 8601 format, which is year – month – day. Personally, I prefer the year – month – day format, but I can’t imagine the UK government is about to change anything on my account.
Sometimes, Power Query can get a bit confused if we use data containing dates in a format different to our local settings. For example, if the cell value is 01/02/03, it could be:
- 2nd January 2003 – the US format
- 1st February 2003 – the UK format
- 3rd February 2001 – the ISO 8601 format
In this post, we will look at how we force Power Query to understand dates, even if different country date formats are used.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file:0043 Power Query – date formats.zip
Example of the problem
Look at the screenshot below. It shows dates in five different formats (without any data types applied). I have included the format in the header so you can see what the input date format is.
Using Power Query, I will translate each of the columns into a date data type.
- dd.mm.yyyy – as expected, Power Query is happy to translate a date in my local region format
- mm.dd.yyyy – the first row is an error because the value 07.23.2017 as a UK date would be the 7th day of the 23rd month. This is not a valid date. In the second row, the value is 09/07/2017. As a UK format, this would be the 9th day of the 7th month, which is a valid date. However, even though the format is valid, the result is incorrect. The original date is in a mm.dd.yyyy format, so the 7th day of the 9th month would be correct.
- yyyy.mm.dd – due to the year having a 4 character format, Power Query has correctly guessed that the remaining digits are month and day.
- yy.mm.dd – the data for this example includes the year with just two digits. Power Query can’t work out which digits relate to the year, so it has applied the local settings. This mixes up the day and year, meaning every value is incorrect. This is the most dangerous result, as if we don’t notice and keep going, it could cause havoc later on.
- yyyymmdd – there are no separators in this example. This is not recognized as any date, so each value displays as an error.
There are options that we can apply to solve each of these date formats.
If the date is a valid country format, we can explicitly tell Power Query which country to use.
Click the data type icon and select Using Locale… from the menu.
From the Change Type with Locale dialog box, select the following options:
- Data Type: Date
- Locale: The region for the date format (for mm.dd.yyyy, I’ve selected English (United States))
Then click OK to close the window.
Power Query has now updated to show the correct date.
In the previous section, Power Query already got the correct result for the yyyy.mm.dd format, so we can move onto the next column.
To change the yy.mm.dd format, we can select English (Canada) as the locale date format. At the bottom of the window, it displays a sample of formats which will be correctly recognized.
The yyyy-mm-dd format is the first item in the list of sample inputs (shown in the purple box above). This order matches with our example data. Selecting this option will generate the correct values for this example. Power Query is intelligent enough to recognize the separators and create the correct date.
This may take a bit of exploration to find out which locale (if any) match with the format we’ve got.
While the yyyymmdd format is in the same order as English (Canada), due to the lack of separators, Power Query doesn’t recognize this as a date. We’ll come back to this example in a moment.
Local settings – workbook
If a workbook is going to have a lot of dates in a specific region format, we can change the Locale settings for the whole workbook.
Click File -> Options and settings – Query Options
In the Query Options dialog box, select Regional Settings -> [Required Locale] -> OK
This setting applies to the current workbook only.
Once the locale has been set, we only need to use the standard date data type and Power Query will apply that locale setting to each field.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Dealing with unknown date formats
We were left with the tricky yyyymmdd format. There are a few options we can try here. The two we are going to look at are:
- Power Query Formulas
- Column From Examples
Using Power Query formulas
Our date is in a standard format, so we can apply basic M code formulas to calculate the result.
Click Add Column -> Custom Column
In the Custom Column dialog box, I have entered the following formula.
=Text.End(Text.From([yyyymmdd]),2) & "/" & Text.Middle(Text.From([yyyymmdd]),4,2) & "/" & Text.Start(Text.From([yyyymmdd]),4)
This is constructed using 4 functions:
- Text.From() – converts the value in the field to text
- Text.Start() – similar to Excel’s LEFT function to return the first characters from a text string
- Text.End() – similar to Excel’s RIGHT function to return the last characters from a text string
- Text.Middle() – similar to Excel’s MID function to extract characters from the middle of a text string
With this formula, we’ve constructed a text string that generates the date in a UK format. Power Query can now easily recognize this as a date.
This formula is specific for this scenario. Change the formula to match with your requirements.
Using Column From Examples
Column From Examples is a feature that helps to standardize formats with complex structures.
Select the column, then click Add Column -> Column From Examples (drop-down) -> From Selection
In the column on the far right, we can start typing the date we want to see, then press Enter. For the value 20170723, I have entered 23/07/2017 as shown by the screenshot below.
Power Query will do its magic and create values for all our rows. Check that these are right. If not, correct any that are wrong; each time Power Query will keep adjusting the transformation.
This is an advanced tool. If there isn’t enough standardization in the value, Power Query can easily calculate the incorrect results. But for our scenario, it is reasonably straight forward.
Once happy with the format, click OK.
That’s it. We’ve managed to get standardized dates from a variety of different formats using Power Query. No matter where the data comes from, you’ll be able to use it in your local country.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: