Scenario
Whilst working with a customer on a project tracker document, we were asked to write a formula that would look at the start date and end date of a project, calculate the duration of the project in working days, and show at-a-glance what proportion of the allocated time for the project had elapsed to date.
Whilst this seemed a simple request in principle, it actually turned out to be more complex in practice, so we thought we’d share our findings.
The first requirement – to calculate the duration of a project in working days – can be met by using the NETWORKDAYS() function. This function calculates the difference (in days) between two dates, minus the number of Saturdays and Sundays.
Whilst this calculation discounts weekends, it still includes bank holidays. A true reflection of total working days would exclude these. The NETWORKDAYS() function allows you to exclude defined dates from the calculation. The simplest way of doing this is by using a defined named range.
We listed out all UK bank holidays for 2015 in a separate table, as below. We then selected the dates in the first column and created a new named range called ‘Holidays’.
We can now tell the NETWORKDAYS() function to exclude any dates in this list by including it as a condition within the function, as below. Note that the duration in cell D6 has now reduced to 62 days, as the formula is taking account of four days of holiday.
Next, we needed to identify how many days had elapsed since the designated ‘Start Date’, and express this as a proportion of the total duration of the project.
The reporting date is in cell C2. We can again use NETWORKDAYS() to calculate how many working days fall between the project start date and the reporting date. This can then be expressed as a percentage of the total duration by dividing by the calculated value in column D, as below.
However, this formula produces some illogical results when the reporting date falls either before the project’s ‘Start Date’ (see cell E7, above), or after the ‘End Date’ (see cell E9, above).
We therefore need to define some conditions in our formula:-
- When the reporting date falls before the ‘Start Date’, show 0% (i.e. the project has not yet started, so no time has elapsed);
- When the reporting dates falls after the ‘End Date’, show 100% (i.e. the full duration of the project has elapsed).
This can be achieved using an IF() statement with a nested dependent clause.
This formula calculated the proportion of the project duration that has elapsed to date. However, our brief was to create an “at-a-glance” overview, and whilst these percentages display meaningful data, a more visual output would be favourable.
If you are using Excel 2010 or later, there is a useful conditional formatting rule that enables you to convert numbers and percentages to in-cell status bars. It can be found under the ‘Data bars’ section of the Conditional Formatting menu.
In this instance, we wanted to display both the bar and the percentage, but Excel’s default way of doing this can be messy. We therefore set our conditional formatting rule to ‘show bar only’ (as below), and replicated the percentages in the adjacent column of the workbook.
Download The Workbook
Formula Breakdown
This formula is an IF() statement with a nested dependent clause. It calculates using the following steps:-
First TRUE Condition
=IF($B6>$C$2,0,
If the ‘Start Date’ is after (i.e. greater than) the reporting date, show 0%
Second TRUE Condition
IF($C6<$C$2,1,
If the ‘End Date’ is before (i.e. less than) the reporting date, show 100%
FALSE statement
(NETWORKDAYS($B6,$C$2,Holidays))/$D6))
Calculate the number of working days (excluding holidays) between ‘Start Date’ and ‘End Date’. Divide this by the total duration of the project.
Get The Formula
The full formula used in this blog is as follows:-
=IF($B6>$C$2,0,IF($C6<$C$2,1,(NETWORKDAYS($B6,$C$2,Holidays))/$D6))
Eoin Sharkey
Thanks for posting the Tax Year formula.
Simple but effective. My brain was too tired to work this out, and copying and pasting this allowed me to classify 5 years worth of Child Benefit statements into correct tax year in 30 seconds so …Yay !