Computes the working date from the specified date plus or minus \a days - excluding holidays. Excel: WORKDAY

Contents

Interface Function Documentation Page Comments

Interface

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

using namespace Units::Date;

int

workDay (int nDate, int days, int holidays=0, int* holidayDates=NULL) Computes the working date from the specified date plus or minus days - excluding holidays. Excel: WORKDAY

Function Documentation

Calculates the next work day that is a number days from nDate. holidays and weekends are assumed not to be workdays and are excluded.

If days is set to zero then this function will simply return nDate regardless of weather is lies on a holiday (or weekend). This functionality may seem strange, however, without some directional bias, it is impossible to know if you should move forward to Monday or backward to Friday. This emulates Excel's interpretation of the function.

In solving this problem you could iterate through each day until you had counted a sufficient number of workable days. For example:

while(days>0)
{
  int h;
  for(h=0;h<holidays && holidayDates[h]!=nDate;h++);  // check against holiday list
  if(h==holidays) days--;                             // only decrement if not a holiday
  nDate=nextWeekDay(nDate);                           // find next week day
}

This approach is applied by certain spreadsheet applications we emulate, unfortunately the time taken by this function scales linearly with size of days. We have opted for a constant time method, which although more involved, works in the following fashion:

s=daysOfWeek(nDate)
nDate-= s;              // move to beginning of current week.
...
days+=s;
nDate+= (days/5) * 7;   // add whole weeks
nDate+= d%5;            // add remainder within week

We then count the number of holidays within this new date range, and advance forward using the slow method shown above (in C++). However, the majority of the work has been done by this point - so the overall scheme is far faster.

Example 1:

workDay(date("1 jan 5"), 0);     // returns Saturday 1 January 2005
workDay(date("1 jan 5"), 1);     // returns Monday 3 January 2005
workDay(date("1 jan 5"), 1, 1);  // returns Tuesday 4 January 2005 (Monday is assumed a holiday)
workDay(date("1 jan 5"), 2);     // returns Monday 3 January 2005

A more detailed example that catches holidays around Christmas and New year:

#include <stdio.h>
#include <codecogs/units/date/workday.h>
#include <codecogs/units/date/dateymd.h>
#include <codecogs/units/date/date.h>
#include <codecogs/units/date/nextweekday.h>
 
using namespace Units::Date;
 
void main()
{
int holidays[3];
holidays[0]=date(2005, 1, 1);            // New years day
holidays[1]=date(2004,12,25);            // Christmas day
holidays[2]=nextWeekDay(holidays[2]);    // Boxing day or first weekday after Christmas
 
printf("\n StartDate   days   newDate");
int startdate=date("1 jan 2005");
for(int i=-50;i<50;i+=10)
{
  int d,m,y;
  dateYMD(startdate,y,m,d);
  printf("\n %2d-%2d-%d",d,m,y);

  dateYMD(workDay(startdate, i, 3, holidays),y,m,d);
  printf("  %2d   %2d-%2d-%d", i, d,m,y);
}
}

Output:

StartDate   days   newDate
1- 1-2005  -50   22-10-2004
1- 1-2005  -40    5-11-2004
1- 1-2005  -30   19-11-2004
1- 1-2005  -20    3-12-2004
1- 1-2005  -10   17-12-2004
1- 1-2005    0    1- 1-2005
1- 1-2005   10   14- 1-2005
1- 1-2005   20   28- 1-2005
1- 1-2005   30   11- 2-2005
1- 1-2005   40    1- 3-2005

Parameters:

nDate	is a serial number of days from 24 November 4714 BC (1 January 4713BC in the Julian Calendar) - also known as the Julian Period.
days	are the number of weekdays to increment by. This value may be signed, with positive values moving to a later date and negative values moving to an earlier date.
holidays	is the number of holidays listed for exclusion, i.e. days that don't count as work days. holidays should match the size of the array passed in holidayDates. If holidayDates is left as NULL, then holidays is added to days (a negative number of days is not allowed)
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 holidays, therefore weekDay(adate,10,2) is the same as weekDay(adate,12). n.b. This is not standard behaviour in Excel.

Authors:

Will Bateman (Oct 2004)