Returns the number of working days between the start and end dates, excluding holidays. Excel: NETWORKDAYS

Contents

Interface Function Documentation Page Comments

Interface

#include <codecogs/units/date/networkdays.h>

using namespace Units::Date;

int

networkDays (int startDate, int endDate, int holidays=0, int* holidayDates=NULL) Returns the number of working days between the start and end dates, excluding holidays. Excel: NETWORKDAYS

Function Documentation

Calculates the number of work days between startDate and endDate, excluding all holidays listed. A workday is Monday to Friday (inclusive), so weekends are ignored. holidays arising over a weekend are also ignored.

A simple solution to this problem is to iterate through each day counting all valid dates. For example:

int days=0;
for(;startDate < endDate;startDate++)
{
  int h;
  for(h=0;h<holidays && holidayDates[h]!=startDate;h++);  // check against holiday list
  if(h==holidays && dayOfWeek(startDate)<5) days++;       // only increment if not a holiday or weekend
}

Sadly we don't like this method because the time taken scales linearly with duration between dates. Moreover we have to check all the holidays against every date - an process. Our approach uses a constant time method along the following lines: where is first Monday after endDate and is the first Monday before startDate; with e and s being the adjustments made to the end and start dates, respectively.

Example 1:

#include <stdio.h>
#include <codecogs/units/date/networkdays.h>
#include <codecogs/units/date/eastersunday.h>
#include <codecogs/units/date/nextweekday.h>
#include <codecogs/units/date/date.h>
#include <codecogs/units/date/dateymd.h>
 
using namespace Units::Date;
 
int main()
{
  // The major holidays in the Christian World
  int holidays[7];
  holidays[0]=date(2005, 1, 1);                // New years day
  holidays[1]=easterSunday(2005)-2;            // Good Friday
  holidays[2]=easterSunday(2005);              // Easter - should be ignored in calculations
  holidays[3]=easterSunday(2005)+1;            // Easter Monday
  holidays[4]=nextWeekDay(date(2005, 5, 1)-1); // May day holiday (first weekday after the 1st - usually)
  holidays[5]=date(2004,12,25);                // Christmas day
  holidays[6]=nextWeekDay(holidays[5]);        // Boxing day or first weekday after Christmas
 
  printf("\n startDate    endDate   NetworkDays");
  int startdate=date("23 oct 2004");
  int enddate=date("16 mar 2005");
  for(int i=0;i<200;i+=10)
  {
    int d,m,y;
    dateYMD(startdate+i,y,m,d);
    printf("\n %2d-%2d-%d",d,m,y);
    dateYMD(enddate,y,m,d);
    printf("  %2d-%2d-%d",d,m,y);

    printf("       %d", networkDays(startdate+i, enddate, 7, holidays));
  }
  return 0;
}

Output:

startDate    endDate   NetworkDays
23-10-2004  16- 3-2005       102
2-11-2004  16- 3-2005       96
12-11-2004  16- 3-2005       88
22-11-2004  16- 3-2005       82
2-12-2004  16- 3-2005       74
12-12-2004  16- 3-2005       67
22-12-2004  16- 3-2005       60
1- 1-2005  16- 3-2005       53
11- 1-2005  16- 3-2005       47
21- 1-2005  16- 3-2005       39
31- 1-2005  16- 3-2005       33
10- 2-2005  16- 3-2005       25
20- 2-2005  16- 3-2005       18
2- 3-2005  16- 3-2005       11
12- 3-2005  16- 3-2005       3
22- 3-2005  16- 3-2005       -5
1- 4-2005  16- 3-2005       -11
11- 4-2005  16- 3-2005       -17
21- 4-2005  16- 3-2005       -25
1- 5-2005  16- 3-2005       -31

Parameters:

startDate	and...
endDate	are serial Julian dates (see date). The return value is signed, so if startDate occurs before endDate then a negative answer will be returned. See date, for details on creating these numbers.
holidays	is the number of holidays listed for exclusion, i.e. days that don't count as network days. holidays should match the size of the array passed in holidayDates. If holidayDates is left as NULL, then the number holidays will be deducted from the number of network days (a negative number of days is not allowed); n.b. This is not standard behaviour in Excel.
holidayDates	is a vector of holiday dates, indexed from 0 to holidays-1. If this array is left pointing to NULL (default), then the number of holidays will be 0 holidays.

Authors:

Will Bateman (Sep 2004)