Dates are fundamental to pretty much every report. No matter what industry you work in, at some stage you're going to work with dates in your reporting. This might be in the form of Semesters, Quarters, Seasons, Weeks, or just good old fashioned Dates.
If you're working with Power Query or Power BI, you should have a Date Table. In this post, I'm going to demonstrate how to work with Dates that can be tricky to format.
We're going to address a few different challenges:
- Challenge 1A: Use Excel to format dates in the correct (regional) format
- Challenge 1B: Use Power BI to format dates in the correct regional format
- Challenge 2: Use Excel to enter and store dates in the correct regional format
As you can see, most of these challenges stem from regional formatting differences, and all are related to ensuring our dates are stored as dates (in both format and data type where relevant).
Challenge 1: Format dates in the correct (regional) format
This challenge applies to all of us, whether you struggle with regional formatting or not. It's most applicable when you export data from another system.
For example, you may export data weekly or monthly from your financial software (such as Xero, QuickBooks, MYOB, Sage, Zoho or even PayPal, Stripe, etc), from your time log systems, from your Learning Management System (aka LMS for short), and so on.
I think you get the idea - this challenge applies to those of us who are exporting data that contains dates from a system into Excel. Sometimes it just works. However, many times we run into one or both of the challenges below:
- The dates are formatted as text, and changing the date format from 'General' to 'Date' doesn't fix it.
- The dates are in the wrong regional format (for example your export data is in US format but your Excel only understands NZ format).
If you're struggling to get dates to format as dates with data exports / extracts, then text to columns might just be your new best friend.
Solution A) Use Excel's Text to Columns to Fix Date Formatting
In the short video below, I demonstrate how to use Excel's text to columns to format dates as dates - in the correct regional format and not as text.
The Text to Columns feature is a great solution for one-off exports or fixes to Excel data that will continue to live and be updated within Excel.
If you are going to be exporting the same data on a regular basis, this Text to Columns fix will get tiresome quickly. I already got annoyed at having to fix each column individually. If I had to do that each week (or even monthly) I wouldn't have any time to write these blogs or do other stuff I enjoy (or need to get done!).
Solution B) Use Power Query to Fix Date Formatting
In the short video below, I demonstrate how to use Power Query's 'Using Locale' to change the data type for dates.
This enables us to define the regional format of our source data dates. Remember that long list of systems I spouted out at the start of this article (it's okay if you skimmed past them - just think of your system now). We need to choose the regional format for our system we're getting the data from. Don't worry about what format you want the dates in - Power Query will figure that out based on your computer and Power Query settings.
Change Regional Settings in Power BI for date outputs
If you do want to change what format your dates end up in, you can find that in the Power BI options.
Click File > Options and Settings > Options.
Under 'Current File' select 'Regional Settings' and change your options:
Challenge 2: Use Excel to enter and store dates in the correct regional format
Both options above work great when we're working with structured data that has a consistent date format for the entire column. Unfortunately, that isn't always the case. It's far too easy to create a new SharePoint site (either from SharePoint or Teams) without considering the consequences. New Teams and SharePoint sites are created using the default regional settings of United States. This means that if we're editing Excel files online or via Teams, any dates we enter will be assumed to be in the US format. If we're editing Excel files on desktop, that's a different story. The result? An Excel file with a mish-mash of date formats and regional settings that can be impossible to sort out.
This challenge is difficult to overcome for past data. The best advice I have for you is to get it right from the start.
Words of wisdom:
- Start entering dates in a format that includes a minimum of 3 letter abbreviation for Month. This ensures it's crystal clear which date you've entered - you'll know that you and Excel are on the same page.
- Whenever you set up a new Team or SharePoint site, check the regional settings. You can see how to do this in my video below.
