Est. 2002

# 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:

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?

 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 (Substitute) 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 (Substitute) 28/12/2020 New Year's Day 01/01/2021 Good Friday 02/04/2021 Easter Monday 05/04/2021 May Day (Early May Bank Holiday) 03/05/2021 Spring Bank Holiday 31/05/2021 Summer Bank Holiday 30/08/2021 Christmas (Substitute) 27/12/2021 Boxing Day (Substitute) 28/12/2021 New Year's Day (Observed) 03/01/2022 Good Friday 15/04/2022 Easter Day 17/04/2022 Easter Monday 18/04/2022 May Day Bank Holiday 02/05/2022 Spring Bank Holiday 30/05/2022 Late Summer Holiday 29/08/2022 Christmas Bank Holiday 27/12/2022

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

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 up to the end of 2022. Additionally, in calculating your working days please ensure you have added any extra holidays to the public holiday sheet.