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:
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.
The EOMONTH function returns the serial number of the last day of the month before or after a specified number of 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.
I use the TODAY function to return the current date. You simply enter the TODAY function like this:
And it will pick up the date from your computer clock.
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.