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
The spreadsheet consists of a simple data sheet, applying my principles for laying out data and using protection and colour to make it obvious where data is to be entered.
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".
Thanks for this really useful post and example!
ReplyDeleteI've been meaning to get something like this set up for a while but haven't found the time.
I wonder, would it be possible to have dates for tasks repeated on a weekly, monthly, quarterly or annual basis automatically added to the list as they become due, rather than having to add them manually? Then, if a task is overdue for some reason, the subsequent instances of that task would not be overlooked.
I appreciate this is a more complicated problem than you were aiming to address!
JCrossley - Thanks for your comments. I'm not sure that you could do this without VBA, and I would recommend that if you were thinking of going down that route, Outlook or Google Calendar would be a better starting point - or a CRM package.
DeleteJCrossley,
DeleteI haven't fully flushed out the thought process, but I did think of one possible solution (birds-eye view).
The first part involves using formulas to set the dates. Depending on the dates you want, this could get fairly complicated, but it could be done.
The second part would involve some way of identifying that a particular date is completed. Of course you could always have part one roll over after each due date, but that would not account for items that weren't completed by the due date. -- My first thought is to have a column that reflects the last completed date (manual date) for each date column. Then the Due columns can use that column and 'today's date' to determine the next date due.
Once again this is a thought process, and the formulas could get quite complicated. You may even need to keep a list of holiday, vacations ... and other dates ranges to be skipped to account for days you want to bypass as a due date.
I hope this may be helpful for someone.