Excel Formula for Working Days

How to Work Out Working Days?

Sometimes you just need the Excel formula for working days. Basically, to find out how many days there are between two dates.

Let's say you have a spreadsheet that looks like:

As you can see from the above formula that takes the start date away from todays date. The result will be displayed in cell C2. There's no need to give you the result. I reckon that you know the answer to that. There's just one thing, and that's formatting. You may notice that the number of days result is in the form of a date. Simply change the format back to the "General" format.
BTW: I used the TODAY() function in cell B2 to work out what Today's date was.

The problem with this date calculation in Excel, is that it doesn't tell you how many working days. Now, this might be important should you want to work out how many days annual leave you've accrued. Especially as most companies work that out based on the number of actually working days. I mean you may have worked at a company for 10+ years but how many days have you actually done?

NETWORKDAYS Function

Enter the NETWORKDAYS function. This little baby's job is to calculate working days between two dates in Excel. It also has the ability to work out holidays, but we'll cover that later. But basically it's the ideal Excel formula for working days.

Let's work out how many days minus weekends enter the following forumula:
=NETWORKDAYS(A2,B2)

Once you have sorted out the date formatting problem (see above) then your Excel sheet should look like:

Now as you can see from above the difference between number of days and number of working days. Now we need to introduce the holidays. That is everyday you want the Number or Working Days to Exclude. If you are trying this yourself you can use the table below:
(Just one little note is that should you wonder why Christmas Day and Boxing Day is on a different date, those dates are substitute dates. I've marked some of them for you. Let's face it should Christmas Day fall on a weekend we need another day off don't we?

Once you've copied and pasted the above into an Excel spreadsheet it could look like this:

Or why not download the NETWORKDAYS bank holiday file and update it with the dates above through 2030.

Great so now that you've downloaded the file let's add it to the formula. For convenience I've placed all the holidays on a seperate sheet. But you might want to place them in a separate Excel workbook. Now, go back to where you typed the NETWORKDAYS function and adjust it as follows:

Note that I changed the name of the sheet with all the Bank Holidays on to "Holidays".

Press Enter. Now you can see the difference between the two dates in actually working days. This will include August bank holidays along with all the other holidays you know and love. Moreover, should you want to include your own holidays, then simply add your holidays to the holiday sheet.

In conclusion, by using the NETWORKDAYS function you will be able to calculate working days between two dates in Excel using the public holiday dates above through to the end of 2030. Additionally, in calculating your working days please ensure you have added any extra holidays to the public holiday sheet.