Today's post was prompted by a discussion on the accountants website,
AccountingWeb.
One accountant was looking for a simple way to manage client deadlines in Excel, for Corporation Tax returns, VAT Returns, Companies House Annual Returns, etc. Although this example is for accountants, it can be applied to managing any sets of multiple deadlines.
Although there are all sorts of packages out there for doing this kind of thing - and I would suggest using those if your needs are much more complicated than this example - if kept simple, Excel might be all you need.
I have provided a finished (simple) example but will talk you through how to build it.
You can download the finished example, here:
DeadlineManagerExample.xlsx
The spreadsheet is to be used as follows:
- Enter client details on the data sheet, and the next due dates for each of the returns in the appropriate columns;
- Refresh the pivot table on the Reports Due sheet, ensuring that the Include drop-down at the top is set to Y;
- The report is sorted in ascending order by Next (which is the calculated, earliest due date for that client);
- You can also filter the report by manager using the drop-down at the top.
- When you have completed a return, update the next due date for that return on the data sheet. When you refresh the pivot table again it will re-sort reflecting the change.
How it works
There are three deadline dates in the example, but the principles can be applied to s many as you want.
There are also two calculated columns in the protected area (the protection has no password, so it can be switched off if you want to look at or edit these columns).
The first column (column I - Next Due Date) calculates the earliest of the three due dates, using the MIN function. On row 2 this reads:
=MIN(F2:H2)
As dates are stored in excel as numbers, the minimum of these three dates is also the earliest. Also note that the MIN function only applies to the numeric fields, so will ignore any cells that do not have a due date.
The second calculated column (column J - Include) is used to exclude any blank rows from the data, as the MIN function will return zero (00/01/1900 in Excel date terms) on these rows, putting them at the top of the list as they are well overdue!
This column simply uses the
IF function to return an "N" if the Next Due Date is zero, otherwise a "Y".
So row 2 reads:
=IF(I2=0,"N","Y")
These two calculated columns have been copied down for 500 rows, but could easily be copied down further if you need more.
The second sheet of the workbook is a
Pivot Table, whose source is all of the columns of the Data Sheet.
The pivot table has been created with the Manager and Include fields as Report Filters, and the client information as the rows. All subtotals have been switched off on the client information fields and the pivot table has been set to Classic Layout - this means that we can keep all of the client information on one row.
All of the dates have been set as Value fields, formatted as dates and set to summarise by Min. As there should only be one row per client, Sum, Average or Max would have done as well, however Min seems safer, as the point of the spreadsheet is our interest in the earliest date.
The most important change to the pivot table is the sort. By right-clicking on the Client Name field, selecting Sort -> More sort options, we can set the Client field to sort in order of the Next Due Date, ascending.
This sort is what makes the report useful, as it shows the clients with the earliest Next due date at the top of the report.
The only other changes made to the pivot table were to remove the column totals (they don't really make sense for dates), and to edit the headings for the value columns to make them more user friendly.
Obviously, this is a simple example and much more complexity could be added, however this would have confused some simple principles.
I hope you find it useful.
Click here for our our exclusive offer on Online Excel Training
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".
