
1. Re: Calculate a future date minus weekends and holidays
debi Apr 24, 2013 11:31 AM (in response to ndelacretaz)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 returndelimited list of dates. Can calc dates forward or backward. Hoping it might help,
Debi Rubel
FullCity Consulting

2. Re: Calculate a future date minus weekends and holidays
ndelacretaz Apr 25, 2013 12:32 PM (in response to debi)Thanks very much, Debi  going to try your function ASAP. I have to admit I don't fully understand the "loopCount" parameter..? My script is going to loop through, say, 50 tasks on a job schedule individually and calculate the end date...so I guess your use of the term "loop" is throwing me off?

3. Re: Calculate a future date minus weekends and holidays
debi Apr 25, 2013 2:35 PM (in response to ndelacretaz)ndelacretaz,
I've found a more recent custom calc that does the same thing; see attached file. As for the loopCount: I've found that, for some recursive functions, I need an additional parameter to be fed back into the formula when calling itself, one that needs to be incremented separately from other parameters. The newer example doesn't use that; it goes like this:
Custom Function: dr_DateNew
// created by Debi Rubel  20111006  v 1.0 [for RL NPO]
// returns a date offset from StartDate by NumDays, adjusting for Weekend/Holidays
// Direction is assumed 1 (forward) unless specified 1
// ExcludeWeekends skips Saturdays and Sundays
// ExcludeHolidays skips a GIVEN LIST of holidays
// Holidays is the optional list of holidays (returndelimited list of dates)
// NOTE: Most parameters are verified to be of correct type at start; but Holidays is NOT checked
Let (
[
start = Case ( IsValid ( GetAsDate ( StartDate ) ) ; StartDate ; "err" ) ;
numdays = Case ( GetAsNumber ( NumDays ) = NumDays ; Abs ( NumDays ) ; "err" ) ;
dir = Case ( Direction = 1 ; 1 ; 1 ) ;
exwkend = Case ( ExcludeWeekends = 1 ; 1 ; "" ) ;
exholi = Case ( ExcludeHolidays = 1 ; 1 ; "" ) ;
holi = Case ( exholi and not IsEmpty ( Holidays ) ; Holidays & "¶" ; "" )
] ;
Case (
// we don't want to waste time w/errors
start = "err" or numdays = "err" ;
"err" ;
// we're excluding weekends or holidays and the test day is one of those, go to next date
( exwkend and ( DayOfWeek ( start ) = 1 or DayOfWeek ( start ) = 7 ) ) or
( exholi and ( FilterValues ( holi ; start & "¶" ) = start & "¶" ) ) ;
dr_DateNew ( start + dir ; numdays ; dir ; exwkend ; exholi ; holi ) ;
// we're on an ok day, but still counting
numdays > 0 ;
dr_DateNew ( start + dir ; numdays  1 ; dir ; exwkend ; exholi ; holi ) ;
// otherwise we must be done
start
)
)
The function "loops" to find a date that is not a weekend or holiday, whereas (if I understand correctly) your script will loop through a number of records that contain dates. You might be able to use this function in lieu of a script, or in conjunction with it. Please remember that you will need to provide the list of holidays (the example file includes a few year's worth, through 2013).
Debi Rubel
FullCity Consulting

DateCalc.fp7.zip 10.2 K


4. Re: Calculate a future date minus weekends and holidays
ndelacretaz May 17, 2013 11:37 AM (in response to debi)Debi  thanks so much. This one (dr_DateNew) seems to be working well!

5. Re: Calculate a future date minus weekends and holidays
debi May 18, 2013 7:29 AM (in response to ndelacretaz)Thanks for checking back and letting me know  much appreciated!
~debi