NETWORKDAYS in Excel: how to use it
NETWORKDAYS counts the working days between two dates. Weekends don't count and you add the public holidays yourself.
Try the first exercises for freeIn the lesson Working days and due dates
What does NETWORKDAYS do?
NETWORKDAYS counts how many working days there are between two dates: Monday to Friday, and both ends count. Excel doesn't know your public holidays: you put those in a list and give it as the third argument.
WORKDAY works the other way round: you give a start date and a number of working days, and Excel gives the date you end up on, without weekends. EOMONTH gives the last day of a month, handy for due dates.
When do you use NETWORKDAYS?
- How many working days a project takes: =NETWORKDAYS(A2,B2).
- The same without the public holidays in E2:E12: =NETWORKDAYS(A2,B2,$E$2:$E$12).
- The delivery date after 10 working days: =WORKDAY(A2,10).
- The due date at the end of next month: =EOMONTH(A2,1).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The number of working days between two dates, both included.
Filled in: =NETWORKDAYS(A2,B2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
NETWORKDAYS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | NETWORKDAYS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
start_date | The first day, which counts too. With WORKDAY and EOMONTH: the date you start from. | A2 |
end_date | The last day, which also counts. | B2 |
[holidays] | Optional: a range with days off that don't count. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
NETWORKDAYS step by step
C2 contains =NETWORKDAYS(A2,B2). From Monday 2 March to Friday 13 March: two full working weeks. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C2 and type NETWORKDAYS with a bracket.
=NETWORKDAYS( - Click the start date, A2, and type a comma.
=NETWORKDAYS(A2, - Click the end date, B2.
=NETWORKDAYS(A2,B2 - Close the bracket and press Enter: two full working weeks, so 10 working days.
=NETWORKDAYS(A2,B2)
More examples
Without a day off
C2 contains =NETWORKDAYS(A2,B2,D2). The day off in D2 falls on a Friday and doesn't count: 9 working days are left.
Ten working days later
C1 contains =WORKDAY(A2,10). Ten working days after Thursday 5 March is Thursday 19 March: the two weekends don't count.
The end of the month
C1 contains =EOMONTH(A2,1). With 1 you get the last day of the next month: 30 April. With 0, it would be 31 March.
Public holidays
Excel doesn't know any public holidays. Put them in a list and refer to it. Bank holidays in England and Scotland aren't all the same, and Ireland and the US have their own, so a shared file may well need more than one list.
Where it often goes wrong
- Both the start and the end date count.
- If you don't work Monday to Friday, use NETWORKDAYS.INTL.
Practise this in the lesson Working days and due dates. The first exercises are free.
Frequently asked questions
How do I find the date after 10 working days?
With WORKDAY: =WORKDAY(A1,10).