• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Date Formats

You are here: Home / Power Query / Power Query Date Formats
April 26, 2017 by Mynda Treacy

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:

Power Query Date Formats

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

date options

*Power Query is available for Excel 2010 onwards.

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

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:

load to Power Query

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:

Transform tab

Or the Add Column tab, which adds a column based on the selected date column:

Add Column tab

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:

Time tab

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")
  1. Click OK to enter the formula and create the column

date to month name

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

change data type to 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.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ā€˜if or’ and ā€˜if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:Row/Column ShortcutsExcel Row and Column Shortcuts
Next Post:Debugging VBA Codedebugging vba code

Reader Interactions

Comments

  1. Jayson Chabot

    April 4, 2022 at 8:25 pm

    Thank you for your help with the short day (Name) function, print screens were greatly appreciated.

    Reply
    • Mynda Treacy

      April 5, 2022 at 11:36 am

      Great to hear, Jayson!

      Reply
  2. Darwis Yudi Brata

    January 5, 2022 at 2:31 am

    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

    Reply
    • Catalin Bombea

      January 5, 2022 at 5:43 am

      You can specify the culture in Date.Month function:
      Date.MonthName(#datetime(2011, 12, 31, 5, 0, 0), “id-ID”)

      Reply
  3. Kayla Gnapp

    March 11, 2021 at 8:36 am

    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?

    Reply
    • Mynda Treacy

      March 11, 2021 at 11:03 am

      Hi Kayla, you can use Conditional Formatting to highlight overdue dates.

      Reply
  4. Rita Ren

    March 5, 2021 at 11:00 am

    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!

    Reply
    • Mynda Treacy

      March 5, 2021 at 11:22 am

      Glad it was helpful, Rita!

      Reply
  5. Prakash

    August 27, 2020 at 3:54 am

    Owsome

    Reply
    • Mynda Treacy

      August 27, 2020 at 8:42 am

      Glad you liked it, Prakash!

      Reply
  6. Ian Wakeham

    August 20, 2020 at 10:35 pm

    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

    Reply
    • Philip Treacy

      August 21, 2020 at 11:12 am

      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

      Reply
      • Ian Wakeham

        August 21, 2020 at 6:28 pm

        Thanks Phil. Perfect – worked like a charm.

        Reply
        • Philip Treacy

          August 22, 2020 at 2:06 pm

          no worries.

          Reply
  7. Barry Mahbouby

    May 31, 2020 at 2:45 am

    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.

    Reply
    • Mynda Treacy

      May 31, 2020 at 9:57 am

      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

      Reply
  8. Norma Dowell

    March 24, 2020 at 4:55 am

    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!

    Reply
    • Mynda Treacy

      March 24, 2020 at 9:01 am

      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

      Reply
  9. Gonzalo

    March 18, 2020 at 9:50 pm

    Hi!
    What if you don’t have a date column, but want to have one with the date the data was imported?
    Thanks

    Reply
    • Mynda Treacy

      March 19, 2020 at 11:37 am

      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

      Reply
  10. Josep Maria

    August 1, 2019 at 12:51 am

    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

    Reply
    • Mynda Treacy

      August 1, 2019 at 9:16 am

      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

      Reply
  11. Jon

    July 6, 2018 at 5:07 am

    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!

    Reply
    • Catalin Bombea

      July 7, 2018 at 3:43 pm

      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

      Reply
  12. Rosalyn Arntzen

    April 1, 2018 at 12:56 pm

    This worked great – thank you!

    Reply
    • Mynda Treacy

      April 1, 2018 at 1:34 pm

      You’re welcome šŸ™‚

      Reply
  13. John Knapp

    January 31, 2018 at 6:01 am

    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?

    Reply
    • Mynda Treacy

      January 31, 2018 at 8:44 am

      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.

      Date.StartOfWeek(dateTime, Day.Monday) 

      More on the Date.StartOfWeek function here: https://msdn.microsoft.com/en-us/library/mt260707.aspx

      Mynda

      Reply
  14. Laurent SANCHEZ

    May 7, 2017 at 11:50 pm

    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 !

    Reply
    • Mynda Treacy

      May 8, 2017 at 9:37 am

      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

      Reply
  15. Peter

    May 4, 2017 at 7:20 pm

    works for me.. Thanks for the tips….

    Reply
    • Mynda Treacy

      May 5, 2017 at 7:52 am

      You’re welcome, Peter šŸ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.