5 Replies Latest reply on May 18, 2013 7:29 AM by debi

# Calculate a future date minus weekends and holidays

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([

];

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...

• ###### 1. Re: Calculate a future date minus weekends and holidays

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

• ###### 2. Re: Calculate a future date minus weekends and holidays

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

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 R-L 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 (return-delimited 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

1 of 1 people found this helpful
• ###### 4. Re: Calculate a future date minus weekends and holidays

Debi - thanks so much.  This one (dr_DateNew) seems to be working well!

• ###### 5. Re: Calculate a future date minus weekends and holidays

Thanks for checking back and letting me know - much appreciated!

~debi