This is a common question particularly in Construction when you have a start date, and at some point in the distance an end date, and you need to know the number of working days between – so excluding all weekends and perhaps some holidays too.  This can be a time consuming task using a calendar if the dates are far apart, and is compounded if you have a long list of dates you need to compare.  Here’s where Excel or Google Sheets can really save you some time.

NETWORKDAYS works exactly the same in Excel as it does in Google Sheets.  The arguments are NETWORKDAYS(Start Date, End Date, [Holidays]).  You will notice that the “Holidays” argument is in square brackets, this is indicating that this argument is optional.

In our first example, let’s assume we’re ignoring the holidays but we want to exclude the weekends from our calculation:

Inline image 1

So we can see it is taking the start date from Cell A2, and the end date from B2, and we’ve just closed the bracket because we don’t care about the holidays:

Inline image 2

Fantastic.  152 working days between those dates.  If your workbook contains many rows of data, then just drag that formula down and it will calculate the net work days between the rest.

But what if we want to exclude holidays from our calculation?  Well first we need to create a list of holidays for the formula to refer to.  In this example I have put the holidays in cells F2:F8, but you will notice that I have put Dollar $ signs before the letters and numbers of this reference ($F$2:$F$8).  This is called Absolute Referencing, and is a big enough topic for another blog post, but for now I will just explain that it means I can drag my formula down to read other dates, and I know that it will keep referring to the same block of holidays without the formula messing up.

Inline image 3

And there we have it, we can see that when we also exclude the holidays as well as the weekends, we only have 145 working days:

Inline image 4

Other tutorials about dates:

Calendar days between two dates

Formatting dates for Excel and Google Sheets

Back to top

2 Comments

Leave a Reply

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