Increment Days Excluding Holidays and Weekends

[crogers111]

CR 2008


I've poured thru related threads about excluding weekends and holidays but haven't been able to get this working quite
right.

Given the last working day of the month, (which I'm calculating in the report for the current month, see formula below)
I need to increment days (both postive and negative) while excluding Holidays and Weekends.

For example if the Last Working day is: 8/30/2013 I need to display this date and a couple weeks of business days prior and following. I may need to display more than
9 incremented days at some point.



Desired Output:


Date / Working Days Difference

8/19/2013 -9
8/20/2013 -8
8/21/2013 -7
8/22/2013 -6
8/23/2013 -5
8/26/2013 -4
8/27/2013 -3
8/28/2013 -2
8/29/2013 -1
8/30/2013 0
9/03/2013 1
9/04/2013 2
9/05/2013 3
9/06/2013 4
9/09/2013 5
9/10/2013 6
9/11/2013 7
9/12/2013 8
9/13/2013 9


The lastWorkingDay of the month is considered Day 0
Labor Day is excluded thus Day 1 = 9/3/2013
I have the Holidays listed in my formula for LastWorkingDay:

@LastWorkingDay

WhilePrintingRecords;
DateTimeVar LastWorkDayNoWknd;
DateVar Array Holidays1 := [Date (2013, 01, 01),
Date (2013, 01, 21),
Date (2013, 02, 18),
Date (2013, 05, 27),
Date (2013, 07, 04),
Date (2013,09,02),
Date (2013,11,28),
Date (2013,11,29),
Date (2013,12,25),
Date (2014, 01, 01)];



if dayofweek({@LastDayofCurrentMonth}) = 7 then
LastWorkDayNoWknd := {@LastDayofCurrentMonth} - 1


else

if dayofweek({@LastDayofCurrentMonth}) = 1 then
LastWorkDayNoWknd := {@LastDayofCurrentMonth} - 2


else
LastWorkDayNoWknd := {@LastDayofCurrentMonth};

DateVar LastWorkDayNoHol := cdate(LastworkDayNoWknd);

If LastWorkDayNoHol in Holidays1
then LastWorkDayNoHol := LastWorkDayNoHol -1
else LastWorkDayNoHol := LastWorkDayNoHol;

//2nd pass to account for Thanksgiving and Day after Thanksgiving (prob a better way to handle this)
If LastWorkDayNoHol in Holidays1
then LastWorkDayNoHol := LastWorkDayNoHol -1
else LastWorkDayNoHol := LastWorkDayNoHol;

LastWorkDayNoHol;