The Excel NETWORKDAYS function returns the number of whole working days between two date serial numbers, excluding weekends (Saturday & Sunday) and holidays.
Excel NETWORKDAYS Function syntax:
=NETWORKDAYS(start_date, end_date, [holidays])
start_date A date serial number or text
end_date A date serial number or text
[holidays] This optional argument can be a range of cells containing holiday dates that you want to exclude from the workday count.
In the example below, my list of holidays are out of view, in cells J11:J18.
Note: NETWORKDAYS results are inclusive of the start and end dates.
Tip: NETWORKDAYS works with dates stored as text, but this is not reliable. It's always best to work with date serial numbers.
Excel NETWORKDAYS Function Example
Johnny works Monday to Friday selling encyclopedias (no one has told him about Wikipedia yet) and he wants to forecast his sales each day to see how he’s performing against his targets.
He uses this simple formula to extrapolate his sales:
=sales this month to date / workdays passed this month X total workdays this month
Johnny is a bit of a technophobe (obviously…he’s still selling encyclopedias) so he doesn’t have the benefit of Excel, but we can help him out with the NETWORKDAYS, EOMONTH and TODAY functions in Excel like this:
=1000/NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1)*NETWORKDAYS(EOMONTH(TODAY(), -1)+1,EOMONTH(TODAY(),0))
I’ve used the EOMONTH and TODAY functions to calculate the start_date and end_date arguments:
Note: The holidays argument is optional and I haven’t used it in this example as Johnny only takes weekends off!
Download the Excel workbook to see an example of the holidays argument in use.
EOMONTH function
The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.
EOMONTH syntax:
=EOMONTH(start_date, months)
Where ‘months’ is the specified number of months before or after the start date.
You can calculate months before the start date by using a negative number as I have done above; EOMONTH(TODAY(),-1) which gives the last day of the previous month.
TODAY Function
I use the TODAY function to return the current date. You simply enter the TODAY function like this:
=TODAY()
And it will pick up the date from your computer clock.
NETWORKDAYS Limitations
The Excel NETWORKDAYS function considers workdays as Monday to Friday.
Thankfully in Excel 2010 NETWORKDAYS.INTL function was introduced to allow you to enter custom weekend parameters.
The syntax is:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
If you don’t have Excel 2010 there is an alternative solution for setting custom weekends here.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
How can we calculate networkdays using power query?
Hi Sam,
This post covers that.
Mynda
It is a good idea to use EOMONTH and TODAY functions to calculate start_date and end_date arguments. In your formula to Johnny for his forecast, it is counting 22 workdays (including holiday on 25th Dec) but actual workdays in December are 21. I think your formula need to adjust by adding 1 day to start-date argument so that the start_date will become December 1 instead of November 30 and then the count of NETWORKDAYS will be 21 which are the actual workdays in December.
=1000/NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY())*NETWORKDAYS(EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0))
Regards,
Syed
Hi Syed,
You’re correct…the formula starts at 30th November and it should start at 1st December.
Thanks for bringing my attention to the error 🙂
I’ve corrected it in the post for future.
Kind regards,
Mynda.
We are using the Networkdays formula to track response times in hours. We would like to incorporate the holiday piece of this function, but are not sure how to do that. Currently we have 2 different formulas to arrive at the hours as shown below.
Example: Request rec’d = 11/20/12 10:47 am (G2)
Request completed = 11/26/12 8:12 am (I2)
Formula in column “K” =IF(I2=””,””,(ROUND((I2-G2)*24,0.5)))
Formula in column “L” =IF(K2=””,””,IF(NETWORKDAYS(G2,I2)*24<K2,K2-48,K2))
Hi Jill,
The syntax for the NETWORKDAYS function is:
=NETWORKDAYS(start_date,end_date,[holidays])
In your last formula above you have omitted the holidays argument. You simply need to add it like this:
=IF(K2=””,””,IF(NETWORKDAYS(G2,I2,holidays_range)*24