Excel - Sort PowerPivot Text Dates: Episode 1678

3,593 views · Published 1 April 2013 · 3:19 · Indexed 31 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Sort Text Dates in PowerPivot.

Welcome to another episode of the MrExcel Podcast. In today's episode, we will be discussing how to sort text dates in PowerPivot. This is a common issue that many Excel users face, as the dates often come up alphabetically instead of in chronological order. But don't worry, we have a solution for you!

This question was sent in response to a previous video we did on sorting dates in PowerPivot. The issue arises when the dates are stored as text instead of actual dates. This means that our usual trick of using the "more sort options" feature won't work. So, in this episode, we will be exploring an alternate solution to this problem.

First, we will copy and paste the data into a new worksheet and create two columns - "OrigDate" and "Sort By". Since all the dates in this example are from the same year, we can simply use the month number (e.g. 4 for April) in the "Sort By" column. However, if we had dates from multiple years, we could use the formula =MONTH(DATEVALUE(A6)) to extract the month number. We can also use the formula =TEXT((DATEVALUE(A6)),"YYYY-MM") to extract the year and month in a sortable format.

Next, we will turn this data into a table and add it to the PowerPivot data model. Then, we will create a relationship between the "text date" column and the "original date" column. This will allow us to sort the dates in the proper sequence.

Now, we can return to our Excel sheet and replace the "text date" column in our pivot table with the "date" column from the "calendar" table. And just like that, our dates are sorted correctly! It's frustrating that PowerPivot doesn't have a simple solution for this, unlike regular pivot tables. But by creating a separate table and using the data model, we can achieve the desired result.

Thank you for tuning in to this episode of the MrExcel Podcast. We hope this solution helps you with sorting text dates in PowerPivot. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Sorting PowerPivot Text Dates
(00:10) Question from viewer about sorting dates in PowerPivot
(00:20) Issue with sorting alphabetically due to dates being stored as text
(00:30) Alternate solution using a separate table and formula
(01:03) Creating a table and adding it to the data model
(02:04) Creating a relationship between the two tables
(02:31) Using the new table to properly sort dates in a pivot table
(02:43) Complaint about PowerPivot's difficulty with sorting dates
(03:09) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Converting text dates to proper dates in PowerPivot
Creating a separate table for sorting dates in PowerPivot
How to sort dates in PowerPivot
Linking text date to original date in PowerPivot
Problems with sorting dates in PowerPivot
Sort PowerPivot Text Dates
Sorting by year and month in PowerPivot
Sorting dates alphabetically in PowerPivot
Sorting pivot tables in PowerPivot
Sorting text dates in PowerPivot


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153500/


In PowerPivot, Dates will show up in Alphabetical order. However, in today's example, Bill shows us that this particular Data Set has Dates that are stored -not as Dates- but as Text. In Episode #1678, we learn how to work with these Text entries as Dates in PowerPivot.

...This episode is the video podcast companion to the book, "PowerPivot For The Data Analyst: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel. http://www.mrexcel.com/store/index.php?l=product_detail&p=185 

For all of your Microsoft Excel needs visit MrExcel.com
Your One Stop for Excel Tips and Solutions.

More from this channel