Power Query* can easily take a column of dates and extract, or convert it into various handy date formats with the click of a few buttons.
For example, the dates in the first column of the table below have been used to quickly create new columns displaying various date information:
The above examples are just a taste of what is possible. There’s a huge list of options in the Date menus available from the Add Column tab, which will keep your original date column and add a new one in the format you choose, or from the Transform tab, which will convert the selected date column.
Below is a preview of the options currently available (note: select the Date column first to enable the menu):
Note: While there are a ton of options for extracting different date formats, Power Query isn't the location to apply formatting as such. For example, if you want to change the date format from dd/mm/yyyy to dd-mmm-yy then this should be done with a custom number format in the Excel grid or the data model.
*Power Query is available for Excel 2010 onwards.
Download the workbook
Enter your email address below to download the sample workbook.
Working with Power Query Date Formats
We’ll use this simple table containing dates and sales amounts as our source data. Notice that the table is called ‘Table1’:
Note: My dates are formatted dd/mm/yyyy.
Load to Power Query
In Excel 2010/2013: Power Query tab > From Table or in Excel 2016: Date tab > From Table:
This will open the Power Query Editor window where you will find the Date tools on both the Transform tab, which changes the format of the selected date column:
Or the Add Column tab, which adds a column based on the selected date column:
Have a play around with the different options available.
Tips:
- Date > Age calculates the age in days from the date column date to today’s date.
- Date > Date Only removes any time element from the Date field and just returns the Date portion.
- On the Transform tab, Date > Earliest returns the earliest date in the selected column, and Latest returns the latest date.
- On the Add Column tab, Date > Earliest and Latest require two date columns to be selected. It then creates a new column containing the earliest/latest date for each row.
- You can also work with time values in the same way. See the Time menu on both the Add Column and Transform tabs:
Older Versions of Power Query
Some of the date menu items are new. For example, if your version of Power Query is missing the Month Name, or Day Name then you can achieve the same results by adding a Custom Column (Add Column tab) and using the Date.ToText function. Here are some examples:
Date to Month Name (MMM format)
Create a new column for the month name (note: this will be a text field).
- Select the Date column
- Add Column tab
- Custom Column
- Give your new column a name and enter this formula (change the [Date] column name to suit your file):
= Date.ToText([Date], "MMM")
- Click OK to enter the formula and create the column
Caution: Power Query formulas are case sensitive. Upper case ‘M’ refers to month format, whereas lower case ‘m’ is for minutes. For example:
= Date.ToText([Date], "MMM")
Will return the text, ‘Jan’ from the date 1/1/2017.
= Date.ToText([Date], "MMMM")
Will return the text, ‘January’ from the date 1/1/2017.
= Date.ToText([Date])
Will simply return the date value as text in the same format it’s in.
Date to Day Name
Likewise, we can use the Date.ToText function to convert the date to the day name:
=Date.ToText([Date], "ddd")
Will return the text value ‘Sun’ from the date 1/1/2017.
=Date.ToText([Date], "dddd")
Will return the text value ‘Sunday’ from the date 1/1/2017.
Tip: Be sure to set the data type for these custom columns containing text names of dates to the Date Type: Text:
More Power Query
Click here for more Power Query tutorials.
And if you want to get up to speed quickly, please check out my Power Query course.
Thank you for your help with the short day (Name) function, print screens were greatly appreciated.
Great to hear, Jayson!
Thanks,
How to make month name locale? i try to change type locale but when i transform to month name, it”s in en-ID. I want in id-ID
You can specify the culture in Date.Month function:
Date.MonthName(#datetime(2011, 12, 31, 5, 0, 0), “id-ID”)
What can I do to best display information where any date today on is ok in date format but anything before I want to show as past due in the same column so that I can make a pivot table showing my demand?
Hi Kayla, you can use Conditional Formatting to highlight overdue dates.
As my company’s excel version is old, i can’t use “Name of month” function straightly. I spend lots of time to search a good resolution online and don’t figure it out until find your article!!!! Thanks a lot!
Glad it was helpful, Rita!
Owsome
Glad you liked it, Prakash!
Hi Mynda,
Still a bit of a Power Query/Get & Transform noob!
I understand how to use the locale to transform US to UK dates. However I have a column of dates (as text) in both US and UK formats (as well as some blanks). Fortunately, I have a corresponding culture column (mainly “en-GB” and “en-US”, with a random “en-IN” – equivalent to “en-GB”).
How do I transform the column text to UK dates using the cultures? I’m guessing I have to delve into the formula bar but wondering if there’s a more obvious method?
Recently discovered your videos on YouTube and must say they’re extremely useful and clearly explained … even the topics I’m unlikely to use!
All the best,
Ian
Hi Ian,
Add a Custom Column and use the Date.FromText function like so:
#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom”, each Date.FromText([Date],[Culture]))
where [Date] is the column with your dates and [Culture] is the column with your culture codes.
Regards
Phil
Thanks Phil. Perfect – worked like a charm.
no worries.
Hello
I have a column named “Customer Visible Date” and would like to filter it to everything equal to today and all the dates less than today in my Excel Power Query.
Hi Barry,
You can use the DateTime.LocalNow Power Query function to filter based on the current date. If you’re not sure where to go from here, please post your question on our Excel forum and share a sample Excel file so we can help you further.
Mynda
When I use the abbreviated month as a slicer, it puts the month in alphabetical order instead of Month order. Is there a way ti force it to show in month order?
Thanks!
Hi Norma,
It’s better to use proper dates in your PivotTables and then use the PivotTable Group functionality to group them into months, years etc. so that Excel knows how to sort the month names. Alternatively, you can use a Custom List to sort the Slicer or use a numeric field e.g. yyyy-mm as explained here.
Mynda
Hi Mynda,
I’m trying to convert DD-MM-YYYY to DD-MMM-YYYY IN Power query editor by adding a new custom column but the date format isn’t being accepted there and throwing an error. How can I display my date fields in for example 01-Jan-2023 format.
Power Query isn’t the place to apply this type of formatting. Power Query recognises data types like date, date-time, text etc. The ‘format’ you see in the Power Query editor is the regional representation of your date, in this date dd-mm-yyyy. If you want to force it to appear as dd-mmmm-yyyy in the Power Query editor you would have to change the data type to Text by adding a column and using the Date.ToText function e.g.:
=Date.ToText([DateColumnName], “DD-MMM-YYYY”)
However, I don’t recommend this because the date is no longer in a format Excel can recognise as a date, which means you can’t sort it as a date, perform math on it as a date etc.
This is why you shouldn’t think of the Power Query editor as the place where you apply formatting. That’s the role of Excel. Therefore, load the data to Excel, and apply a custom number format to the cells to get the appearance you want.
Mynda
Thanks Mynda for the suggestion. I did try with excel and I do get the date fields in dd-mmmm-yyyy format. Is there a DAX query that supports conversion of the date field to dd-mmm-yyyy so as to display dates in 01-Jan-2023 format ?
Thanks
You can set the format in the data model, so no need to use a DAX measure to set the format.
Hi!
What if you don’t have a date column, but want to have one with the date the data was imported?
Thanks
Hi Gonzalo,
You can ‘Add a Column’ and use the DateTime.LocalNow function to timestamp the query, but obviously every time you refresh the query the DateTime.LocalNow would also return the current date, so that may not suit your needs. The alternative is to manually enter that date via ‘Add a Column’.
Mynda
Hello,
Thanks for the post.
My question is regarding the use of PowerPivot. Can I use it data formated as dd/mm/yy or I should first convert it to mm/dd/yy?
My input data comes from files in the following date format:
dd/mm/yy
* I have tried creating a date table manually, by entering pasting dates in format dd/mm/yy but as soon as they are pasted, excel converts them into format mm/dd/yy
* I went to the queries and defined the dates as European local format dd/mm/yy
Yet, when I use the month as slicer on power pivot, I can see dates are recognized as american dates.
Example:
01/01/18 is recognized as January 1st, 2018. This OK, no room for mistake.
02/01/18 is recognized as February 1st, 2018. In fact it is, January 2nd, 2018
03/01/18 is recognized as March 1st, 2018. In fact it is, January 3rd, 2018
I would be very greateful if anyone could help me out.
Sincerely,
Josep Maria
Hi Josep,
You need to remove the ‘Changed Type’ step in the ‘Applied Steps’ pane. Then right-click the date column header > change type > Using Locale. In the dialog box choose data type: Date. Then choose English (UK) from the drop down list. This should format your dates as dd/mm/yyyy assuming your source data has regional settings of dd/mm/yyyy.
Mynda
Hello, is there an easy way to add a number of weekdays to a date? I have a column with the number of days I need to add and a column with the date I need to add them to. I’m using PowerQuery plugin to excel 2010. Thanks!
No easy way, but there is a function already made, you can try it: https://stackoverflow.com/questions/42099667/add-x-number-of-working-days-to-a-date-in-a-custom-column
This worked great – thank you!
You’re welcome 🙂
Hi Mynda,
I’m using Power Query and am having trouble with week of year and figuring out how to start the week on Monday. Any idea how to update the query?
Hi John,
If you look at the M code that’s generated when you insert the Start of Week column you’ll see that it uses the Date.StartOfWeek function. This function has a second argument that allows you to specify the first day of the week. e.g.
More on the Date.StartOfWeek function here: https://msdn.microsoft.com/en-us/library/mt260707.aspx
Mynda
Hi, thanks for your post, but for the name of the day or the name of month, you have :
Name of Day : Date.DayOfWeekName([Date])
Name of Month : Date.MonthName([Date])
You can add the culture (French for me) :
Date.MonthName([Date],”fr”)
And instead of having text value, you have a date value !
Hi Laurent,
I used the GUI to add the day of week etc. and it doesn’t ask for a locale/culture. I only have an English version of Power Query so I’m curious to know if your version has a field in the dialog that asks what region, or do you have to add it manually by editing the M Code?
Mynda
works for me.. Thanks for the tips….
You’re welcome, Peter 🙂