I've tried a couple of CFs for this and am not getting correct results.

Found one called NextWorkDay(tempDate;listOfHolidays) that seemed like what I needed. It is defined as:

Let ( [

isWorkDay = Mod ( tempDate - 1 ; 7 ) < 5 and IsEmpty ( FilterValues ( tempDate ; listOfHolidays ) )

] ;

Case (

isWorkDay ; tempDate ;

NextWorkDay ( tempDate + 1 ; listOfHolidays )

)

)

I basically need to loop through a group of "schedule task" records and get a date by adding a prescribed number of days to a preceding task's date (stored in a related record). Here's the calc I use in my looping script that employs the CF above:

Let([

p=PrecedingTask::ProjectedDate;

d=Task::DaysToAdd;

HolidayList=Task::_cListHolidays

];

NextWorkDay(p+d ; HolidayList )

)

I'm attaching a screenshot that shows the strange results (example: asking to get the NextWorkDay for PrecedingTask + 11 days only yields the PrecedingTask date + 6 work days)

Any experience/ideas/other approaches that might work? Thanks...

ndelacretaz,

Didn't look at your example too closely, but dug up this custom function I wrote a while back:

FunctionName: CalcEndDate

/*

calculates a new date by adding or subtracting days; flag for skipping weekends and holidays

Variables

-startDate, beginning date to start calculations from

-numDays, number of days to add or subtract from startDate

-loopCount, determine when to exit loop, if loopCount = numDays then calculation is finished, start at 0 - function will increment

-holidayList, global with all holidays, must be populated before calling function

-allowWeekends, 'true' indicates endDate can fall on a weekend or holiday, empty for skipping weekends and holidays

-direction, 'b' to subtract numDays from startDate, empty to add numDays

*/

Let ( [

//used for calculating loop count

tempEndDate = If ( direction = "b"; startDate - 1; startDate + 1 ); //'b' for backward calcs, empty for forward calcs

//determine when to exit function

loopCount = If (

DayOfWeek ( tempEndDate ) = 1 or DayOfWeek ( tempEndDate ) = 7 or ValueCount ( FilterValues ( tempEndDate; HolidayList )) > 0 ;

loopCount ; //don't increment loop if not counting the day

loopCount + 1 );

//calc end date

endDate = Case (numDays = 0; startDate ;

If ( allowWeekends = "true" ; If ( direction = "b"; startDate - numDays; startDate + numDays ) ; If ( direction = "b"; startDate - 1; startDate + 1)))

] ;

Case (

numDays = 0 or allowWeekends = "true" ; endDate; //no looping, just give result

loopCount = numDays; endDate; CalcEndDate (endDate; numDays; loopCount; holidayList; allowWeekends; direction ) //call function again as necessary

)

)

It's a recursive function, with the parameters described in the top comment. The list of holidays must be a return-delimited list of dates. Can calc dates forward or backward. Hoping it might help,

Debi Rubel

FullCity Consulting