4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings

Bottom Line: Learn 4 different ways to fix date data type errors in Power Query, including with locale, regional settings, and custom formulas with Column From Examples.

Skill Level: Intermediate

Video Tutorial

Watch video on YouTube

Download the Excel File

The file that I use in the video can be downloaded here if you want to practice these tips on your own.

Power Query Date Locale.xlsx (120.4 KB)

Fixing Date Errors in Power Query

Sometimes in Power Query, when you attempt to format data as a date, you will receive error messages. This is because Power Query is unable to recognize the data. The most common occurrence for this is when the original format of the date is from a different region.

The U.S. uses the MM/DD/YYYY format for dates, while most of the rest of the world uses DD/MM/YYYY. This can create issues with date conversions in Excel and Power Query.

There are four different ways to address this issue of date errors.

1. Locale in Data Type Menu

The first way to fix a date error that stems from location differences is to choose the Using Locale setting in the Data Type drop-down menu for whichever column/query has the errors.

Using Locale
  1. Click the Data Type box in the top-left corner of the column that contains the dates, then choose Using Locale…
  2. This will open the Change Type with Locale window. Here you can choose Date as the Data Type, and then for the Locale field you can choose the location where the data originated.
  3. Press OK to apply to change the data type to a date with the proper formatting.
Change Type with Locale

This formatting will need to be done on each query that returns errors for date columns. If you are working with a query that has several date columns or a file with several queries, then you might want to use the next tip instead.

2. Locale in Regional Settings

You can also change the regional settings for your entire file. To do this:

  1. Go to the File menu and select Options and settings, then Query Options.
  2. This brings up the Query Options window. Select Regional Settings, and then select the Locale for where the data originated from.
  3. Press OK to save the changes.
Query Options Window

For any queries in this file that have particularities to a region, now the formatting will show correctly when you change the data type of a column to Date. You do NOT have to use the Using Locale option explained in #1 above.

Note: This setting stays with the file. So if you share the file with someone from another country they won't have to change the locale to match the source data. If they add new queries with dates in their local format, then they will need to change this or use the Using Locale option (#1 above).

3. Operating System Regional Settings

If you are looking to apply regional settings to more than just one file at a time, you can change the region settings on your operating system.

An example of this might be if you work remotely from one country, but the files you deal with each day come from a company in another country. Instead of changing the regional settings for each individual file, you're essentially telling your computer that you are located in the other country. That way, it will display all of your data in the formatting of that region/locality.

To change your settings in Windows:

  1. Go to the Start menu and begin typing the word “regional.” This will open the Settings window and navigate to the Region settings.
  2. In the Regional format drop-down you can choose the location/format that most of your data comes from.
  3. You might need to restart Excel for changes to take effect.
Change region settings for windows operating system
Click to enlarge

Keep in mind that changing this setting could have implications in other applications besides Excel. For example, your system clock might convert to 24-hour (military) time formatting, etc.

4. Custom Formula with Column From Examples

This last fix is for dates that are oddly formatted. For example, it's not just a matter of American vs. British preferences in formatting, but maybe you exported data from a system that spit out the data in a funky way.

If that's the case, you can use the Column From Examples feature in Power Query to do a little magic. This feature essentially does pattern recognition based on examples of how you would change the dates from the current format to your desired format.

To use the example from my video, if my dates are currently formatted as [25.11–2018, 29.5–2018, and 7.5–2018], I can tell Power Query that those dates should be [11/25/2018, 5/29/2018, and 5/7/2018]. From there, the Columns From Examples feature will guess the remainder of the data because it can pick out the pattern in my changes.

To use the Column From Examples feature:

  1. Go to the Add Column tab in the Query Editor and select the Columns From Examples drop-down.
  2. Assuming that you already have the column you want to change selected, you can click on From Selection. If you choose From All Columns, Power Query will include all of the columns in it's evaluation of what you type next. We only want to evaluate the date column for this example.
  3. This will open up a new column, and that's where you will type in the date using the correct formatting. After two or three entries are typed in, Power Query will start to guess at the correct formatting for the remainder of the data. Once you can tell that it's correct, you can click on OK.
  4. The new column with the dates is added to the end of the table.
  5. Change the data type of the column to Date and rename the column.
Column From Examples in Power Query
Click to enlarge

Now that you have a date column in the correct formatting, you are welcome to remove the old column, if you wish.

Related Posts

If you liked this post about fixing dates in Power Query, you might be interested in some of my other Power Query posts:

Conclusion

So there you have it: 4 ways to fix errors when you're converting date data. I hope they are useful for you. If you have questions, please leave them in the comments below!

  • On a related topic, is there any way to convert times from standard format to military format within Power Query (i.e. change 2:00 PM to 14:00)?

    • Hi Leslie,
      Great question! There are a few ways to go about it.

      Unfortunately, using the Locale settings for Time data types doesn’t work the same way it does with dates. If you change the data type for a Time column with the Using Locale option and choose English (United Kingdom), the time will not show the 24-hour time format.

      So, the first way is to change the number formatting of the time column in the output Table. This can be done by selecting the column, then right-clicking > Format Cells. Choose a time type with a 24-hour format. That will only change the output table. You will not see the 24 hour format in the Power Query Editor window.

      If you want to see the 24-hour format in Power Query or you don’t want to have to change the number formatting in the output table, then you can change the operating system settings. Open the Region settings in Windows, as I show in the video. You have two options here.

      1. You can change to a different region that has 24-hour formats like English (United Kingdom).
      2. You can scroll down in that window and click “Change data formats”. Then change the format for the short time and long time drop-downs to a 24-hour format. This will change the default time format in Excel and Power Query to the 24-hour format.

      Option #2 above will be your best bet if you want to keep your dates in your local regional format and have the times always appear in the 24-hour format.

      I hope that helps. Thanks again and have a nice day! 🙂

  • Jon: Thanks this is good and appreciate the different ways to correct. What happens when you have a column of different types of dates? i.e. dd/mm/yyyy, mm/dd/yyyy, mm/yy, mmm/yy, mm.dd.yyyy etc?

  • Hi Jon, thank you for the informative video. Silly question perhaps, how do you get to show the format of the column in your headers of each column? Example – Column B is ORDER DATE with a small calendar graphic. Hope this makes sense.

    • Hi Robert,
      Great question and not silly at all! This is the data type for the column. In some cases Power Query will run a step to automatically detect data types, and you will see a Changed Type step in the Applied Steps list.

      If it does not run that step then you can run it manually from the Transform tab > Detect Data Type. You will want to select all columns first.

      You can also just click the icon in the header row and then select the data type manually for each column. This is useful when you create a new column of dates or numbers. Power Query will not recognize the data type automatically for those new columns and you will need to change them manually.

      I hope that helps.

  • I cannot follow along as the data is unavailable. It seems it refers to csv files that you didn’t supply.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >