Get a list of pending tasks and sort by due dates

A common task – Get and sort a list of tasks that are not yet due according to due dates

Got the following question:

Suppose on column A I have entries and on column B i have due dates for the entries. Is it possible to have excel automatically rearrange the ROWS such that the nearest dates appear at the top and the furthest dates at the bottom? Thank you in advance

First thing on my mind: This can be done easily by sorting column B in ascending order.

Then I stop and rethink… maybe the reader wants to list the tasks that are not yet due… and probably on a separate table.  And even more, new tasks with new due dates will be added from time to time and Excel should be able to get him updated list of tasks not yet due “automatically”… That makes sense and I believe it’s a common task for many people.

The following screen cast visualize the request:Excel Tips - Due date final

And this is achieved with a simple helper column + Pivot Table.  Here’s the step by step instruction:

You may download a to Sample File – Sort Task according to due dates  to follow along.

Let’s start with a list of 10 tasks (note: I did the following screenshots last week, so let’s assume today is Dec 2, 2017)

Excel tip - due date

1. Turn the range into an Excel Table.Excel tip - due date 1



2. Add a helper column

In C1, input “Days to due”, then you will see the table expand automatically.

Excel tip - due date 2

3. Input a simple formula to show number of days to the due date.

In C2, input “=”, then press left arrow key once (or use your mouse to point to cell B2), you will see the formula changed to [@[Due date]] automatically.  Don’t panic!  This is a wonderful feature of Excel Table.  Simply continue the formula with “– today()“.  You will see the same formula “[@[Due date]] – TODAY()” filled the column automatically.

Excel tip - due date 4

To make the results look more meaningful to you, change the format to “General”.

Excel tip - due date 5

4. Insert Pivot Table.

Follow the steps shown below.  Note before you insert PivotTable, select any cell into the Table, then you will see Excel detects the Table name correctly for you.  For easy reference, let’s place the PivotTable to E1 of the same worksheet.

Excel tip - due date 6

Move the “Task” and “Days to due” to Rows and Value respectively.

Excel tip - due date 7

As this point, you will see a PivotTable that is basically the same as the original table.

Excel tip - due date 8

Since we want to focus on tasks that are going to due, i.e. “Days to due” > 0, we can filter the Pivot Table as follow:

Excel tip - due date 9

Note: We can do a few tricks here depending our needs.  If we want to include task that is due on current day, filter “Great Than Or Equal To 0” instead.  If we want “overdue” tasks, we may filter “Less Than 0” here.

Now you should get the following:

Excel tip - due date 10

Sort the “Sum of Days to due” A to Z

Excel tip - due date 11

Now the result is basically what we want. (Tip: You may rename the column header)

Excel tip - due date 12

To make the result more reader-friendly, I would put “Due date” to Rows (under Task).

Excel tip - due date 13

By default, PivotTable is shown in Compact Form.  Let’s change it to “Tabular Form”.

Excel tip - due date 14

Remove all “Subtotals”.

Excel tip - due date 15

Remove the automatically added “Months”.

Excel tip - due date 16

Note: If you are using Excel 2010, you won’t have the “Months” added by Excel so that you won’t need this step.  (Not sure about 2013 as I have never used it ;p )

Finally remove “Grand Total”

Excel tip - due date 17

5. Here we go

Excel tip - due date 18

Wait… What if I have new tasks added to the list?

One of the beauties of Excel Table is automatic expansion:  When new data are added to the bottom of the table, the formula in Column C will fill in automatically.   On top of it,  do you remember that the Pivot Table was created using the Table name, not range, as the source.  That means the Pivot Table will get the expanded range as the source.  Simply put, you don’t need to worry about updating the Pivot Table source every time it is updated.

See it in action:

Excel Tips - Due date final

Make sense?

Well… it’s not 100% automatic as you will still need to “Refresh” the pivot table every time you have new data added.

To go one more step “closer” to “fully automatic”, we may instruct Excel to refresh that Pivot Table when opening the file.


Hope you like it.

Do you handle this task by using Macro or other methods?  Please share with us your thought.


Power BI Course.

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s