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

    Calculate a future date minus weekends and holidays

    ndelacretaz

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

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

          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
            ndelacretaz

            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

              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
                ndelacretaz

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

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

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

                  ~debi