Search Not Just Numbers

Tuesday 2 September 2014

Excel Tip - counting workdays allowing for holidays

Please accept my apologies for the lack of posts these last couple of weeks - I've been away on holiday, chilling out on the Isle of Skye (that's me in the picture relaxing on the coral beach near Claigan).

I had intended to write some posts in advance but it didn't exactly work out as planned!

Now I'm back, I thought I'd write a (vaguely) holiday related post.

If you want to know how to work out the number of working days between two dates (allowing for weekends AND holidays), read on.

To do this, we can use the NETWORKDAYS function.

The syntax for this function is:

=NETWORKDAYS(StartDate,EndDate,[Holidays])

I would not recommend entering the dates directly into the function. This is not only good advice for making this particular function work, but it is best practice when referring to any variable in Excel - enter its value in a cell and refer to the cell.

Ignoring the optional argument, if A1 contains 1/8/2014 and A2 contains 31/8/2014, then

=NETWORKDAYS(A1,A2)

returns 21, being the number of working days in August 2014 (if you ignore public holidays).

The best way to use the optional third argument is to refer to a range, where you can enter holiday dates. So, say we add the third argument as follows:

=NETWORKDAYS(A1,A2,B1:B20)

We can now enter the dates of any holidays in cells B1 to B20 and these will be excluded from the calculation.

So, in the UK, last Monday was our August Bank Holiday, so if we enter 25/8/2014 into any of the cells from B1 to B20, the formula returns 20.

A practical way of using this functionality would be to enter the whole year's holidays into the range referred to (B1:B20 in our example), any of those dates that fall between the start and end dates would then be excluded from the calculation.


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

5 comments:

  1. Thank you - always short and to the point

    ReplyDelete
  2. I have a scenario where I need to give select variable holiday lists in one formula is it possible?

    for an example;

    USD - 01/09/2016

    I need to select next working day considering both criteria (USD and Date)

    my calendar lists like this

    USD EUR GBP
    29-Aug 29-Aug 29-Aug
    1-Sep
    3-Sep
    4-Sep
    5-Sep

    ReplyDelete
    Replies
    1. I'm not sure exactly what you're after but you should be able to do it with a combination of INDEX and MATCH, using MATCH for both of the arguments of the INDEX function. I did a post on INDEX and MATCH here:
      http://www.notjustnumbers.co.uk/2013/03/excel-tip-index-and-match-alternative.html

      Delete
    2. Thank you sir.
      It worked. Yes that's what I,wanted

      Delete