• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us

Calculate Time Elapsed using NETWORKDAYS()

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.

TimeElapsed_01

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’.

TimeElapsed_02

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.

TimeElapsed_03

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.

TimeElapsed_04

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.

TimeElapsed_05

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.

TimeElapsed_06

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.

TimeElapsed_06

Download The Workbook

Time Elapsed (Excel) 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))
  • 29 May
  • Tobias Owen
  • Excel
  •  0 Comments
  •  Like
  • Conditional Formatting , Excel , Formula , NETWORKDAYS

Share This

Related Posts

  • Rock Paper Scissors
  • Rank by Multiple Criteria in Excel
  • Date Functions : Excel Calendar (Part 1)
  • Updated Excel workbooks links
File Naming Conventions and Best Practice →← Gut Instinct? That’s So Last Generation
1

Comment

  1. Eoin Sharkey
    29th November 2017 at 10:36 pm

    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 !

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.


We also use cookies to understand how visitors use our website Accept


To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy

Necessary Always Enabled

Non-necessary