Computer Tutoring Logo
Est. 2002
Menu Button

Excel Formula for Working Days

Working out Working days?

Sometimes you need to find out how many days there are between two dates. That's right! You need an Excel function for working days. Let's say you have a spreadsheet that looks like:

Excel formula for working days

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 works out how many actually working days exists between two dates. 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)

NETWORKDAYS function - The Excel Formula for Working Days

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

NETWORKDAYS missing the weekends

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:

Holiday Date
New Year's Day 01/01/2009
Good Friday 10/04/2009
Easter Monday 13/04/2009
May Day (Early May Bank Holiday) 04/05/2009
Spring Bank Holiday 25/05/2009
Summer Bank Holiday 31/08/2009
Christmas 25/12/2009
Boxing Day 28/12/2009
New Year's Day 01/01/2010
Good Friday 02/04/2010
Easter Monday 05/04/2010
May Day (Early May Bank Holiday) 03/05/2010
Spring Bank Holiday 31/05/2010
Summer Bank Holiday 30/08/2010
Boxing Day 27/12/2010
Christmas 28/12/2010
New Year's Day 03/01/2011
Good Friday 22/04/2011
Easter Monday 25/04/2011
May Day (Early May Bank Holiday) 02/05/2011
Spring Bank Holiday 30/05/2011
Summer Bank Holiday 29/08/2011
Boxing Day 26/12/2011
Christmas 27/12/2011
New Year's Day 02/01/2012
Good Friday 06/04/2012
Easter Monday 09/04/2012
May Day (Early May Bank Holiday) 07/05/2012
Spring Bank Holiday 28/05/2012
Summer Bank Holiday 27/08/2012
Christmas 25/12/2012
Boxing Day 26/12/2012
New Year's Day 01/01/2013
Good Friday 29/03/2013
Easter Monday 01/04/2013
May Day (Early May Bank Holiday) 06/05/2013
Spring Bank Holiday 27/05/2013
Summer Bank Holiday 26/08/2013
Christmas 25/12/2013
Boxing Day 26/12/2013
New Year's Day 01/01/2014
Good Friday 18/04/2014
Easter Monday 21/04/2014
May Day (Early May Bank Holiday) 05/05/2014
Spring Bank Holiday 26/05/2014
Summer Bank Holiday 25/08/2014
Christmas 25/12/2014
Boxing Day 26/12/2014
New Year's Day 01/01/2015
Good Friday 03/04/2015
Easter Monday 06/04/2015
May Day (Early May Bank Holiday) 04/05/2015
Spring Bank Holiday 25/05/2015
Summer Bank Holiday 31/08/2015
Christmas 25/12/2015
Boxing Day 28/12/2015
New Year's Day 01/01/2016
Good Friday 25/03/2016
Easter Monday 28/03/2016
May Day (Early May Bank Holiday) 02/05/2016
Spring Bank Holiday 30/05/2016
Summer Bank Holiday 29/08/2016
Boxing Day 26/12/2016
Christmas 27/12/2016
New Year's Day 02/01/2017
Good Friday 14/04/2017
Easter Monday 17/04/2017
May Day (Early May Bank Holiday) 01/05/2017
Spring Bank Holiday 29/05/2017
Summer Bank Holiday 28/08/2017
Christmas 25/12/2017
Boxing Day 26/12/2017
New Year's Day 01/01/2018
Good Friday 30/03/2018
Easter Monday 02/04/2018
May Day (Early May Bank Holiday) 07/05/2018
Spring Bank Holiday 28/05/2018
Summer Bank Holiday 27/08/2018
Christmas 25/12/2018
Boxing Day 26/12/2018
New Year's Day 01/01/2019
Good Friday 19/04/2019
Easter Monday 22/04/2019
May Day (Early May Bank Holiday) 06/05/2019
Spring Bank Holiday 27/05/2019
Summer Bank Holiday 26/08/2019
Christmas 25/12/2019
Boxing Day 26/12/2019
New Year's Day 01/01/2020
Good Friday 10/04/2020
Easter Monday 13/04/2020
May Day (Early May Bank Holiday) 04/05/2020
Spring Bank Holiday 25/05/2020
Summer Bank Holiday 31/08/2020
Christmas 25/12/2020
Boxing Day 28/12/2020

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

Public Holidays for the NETWORKDAYS function

Or why not download the NETWORKDAYS bank holiday file.

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:

NETWORKDAYS Excel Function along with Bank Holidays

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 days in actually working days. This will include August bank holidays along with all the other holiday you know and love. Should you want to include your own holidays then simply add your holidays to the holiday sheet.