Power Query Date Formats

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):

*Power Query is available for Excel 2010 onwards.

Download the 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).

1. Select the Date column
2. Add Column tab
3. Custom Column
4. Give your new column a name and enter this formula (change the [Date] column name to suit your file):

= Date.ToText([Date], "MMM")

5. 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.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.