Calculate Date By Adding or Subtracting Time With A Schedule

[sdarity1]

I needed to calculate a date that was days prior to another date using only the hours that are available in the "8-5 weekdays excluding holidays" Schedule.   Adding Time to a date to calculate a future date or due date using a Schedule is easy.   However, subtracting time from a date to calculate a previous date using a Schedule posed a challenge.   I quickly found out that there is no OOB subtract time function and the add time method does not work with negative numbers.

Created 'ScheduleUtils' Class that utilizes the DurationCalculator() Class.

Here is the "ScheduleUtils" Script Include with Two(2) Methods addDaysInSchedule(scheduleName,   startDate, daysForward) and subDaysInSchedule(scheduleName,   startDate, daysBack) that have the ability to create a date by adding or subtracting time in a schedule.

var ScheduleUtils = Class.create();
ScheduleUtils.prototype = {

// Init
initialize: function() {
  //Control Constants
  this.limitDaysBack = 100;
},

/*
  * This function goes days forward in a schedule.
  *
  * Parameters:
  *   scheduleName: String, Name of the Schedule
  *   startdate: GlideDateTime, Start Date
  *   daysForward: Int, Days to go forward
  *
  * Return value:
  *   GlideDateTime, Date in the Schedule with daysForward
  */

addDaysInSchedule:   function(scheduleName,   startDate, daysForward){
  //Get a schedule by name to calculate the check
  var schedRec = new GlideRecord("cmn_schedule");
  schedRec.get("name", scheduleName);
  //Get date/time in correct format for duration calculation
  var startDateBeg = new GlideDateTime();
  startDateBeg.setValue(startDate);
  startDateBeg = startDateBeg.getLocalDate();
 
  usrTZ = startDateBeg.getUserTimeZone();
 
  var dc = new DurationCalculator();
  dc.setTimeZone(usrTZ);
  dc.setSchedule(schedRec.sys_id);
  daysForward++; // Add 1-Day to Offset startDate in Calculation
  dc.setStartDateTime(startDateBeg);
  dc.calcDuration(daysForward*9*3600);
 
  return dc.getEndDateTime();
},

/*
  * This function goes days back in a schedule.
  *
  * ATTENTION:
  *       This function works with a looping approach.
  *       It goes back day by day and calculates the duration in a schedule.
  *       Accuracy is on day basis
  *       If the duration is large enough, it returns the date/time.
  *       The max Days could be limited, to avoid infinite loops.
  *
  * Parameters:
  *   scheduleName: String, Name of the Schedule
  *   startdate: GlideDateTime, Start Date
  *   daysBack: Int, Days to go back
  *
  * Return value:
  *   GlideDateTime, Date in the Schedule with daysBack
  */

subDaysInSchedule:   function(scheduleName,   startDate, daysBack){
  //Get a schedule by name to calculate the check
  var schedRec = new GlideRecord("cmn_schedule");
  schedRec.get("name", scheduleName);
  //Get date/time in correct format for duration calculation
  var endDate = new GlideDateTime();
  var startDateBeg = new GlideDateTime();
  startDateBeg.setValue(startDate);
  startDateBeg = startDateBeg.getLocalDate();
  endDate.setValue(startDateBeg);
 
  usrTZ = startDateBeg.getUserTimeZone();
 
  var dc = new DurationCalculator();
  dc.setTimeZone(usrTZ);
  dc.setSchedule(schedRec.sys_id);
 
  // Max 100 Days, just if loop does not find a slot in the schedule
  var limitHoursBack = this.limitDaysBack*9; // 9-Hours = 1-Business Day
  for (var i=0; i<limitHoursBack; i++){
    //Substract hour by hour
    endDate.addSeconds(-3600); //1hour = 60sec * 60min
   
    //Calculate Duration
    var duration = dc.calcScheduleDuration(endDate, startDateBeg);
   
    //If we went enough hours back, quit
    if (duration/32400 >= daysBack){ // 32400 Seconds = 9-Hours = 1-Business Day
      break;
  }
}

return endDate;
},

type: 'ScheduleUtils'
};

Here is the 'Subtract' Days Sample Code that uses the 'ScheduleUtils" Class that you can run in Scripts - Background Window To Try It Out.

executeSample();

function executeSample(){

    var su = new ScheduleUtils();

    var myDate = su.subDaysInSchedule("8-5 weekdays excluding holidays", "2017-04-09 23:59:00", 5);

    gs.print("MyDate = " + myDate);

}

Output Result: *** Script: MyDate = 2017-04-03 13:00:00

Here is the 'Add' Days Sample Code that uses the 'ScheduleUtils" Class that you can run in Scripts - Background Window To Try It Out.

executeSample();

function executeSample(){

    var su = new ScheduleUtils();

    var myDate = su.addDaysInSchedule("8-5 weekdays excluding holidays", "2017-04-09 23:59:00", 5);

    gs.print("MyDate = " + myDate);

}

Output Result: *** Script: MyDate = 2017-04-17 22:00:00

[prmadhikar]

Hello Steve,

This a great contribution. I was looking for something like this, though I am looking for private scope based solution this will be a great start (plan- to have a global script include). I am still hung up on your "Add 1-Day to Offset startDate in Calculation" if you can, can you please share the logic behind this?

Thanks,

Pratik

[anabegandolfi]

Thank you so much, it was really helpful!

Just a couple of changes if you're working within scoped application:

Replace startDateBeg.getUserTimeZone() by:

gs.getSession().getTimeZoneName();

DurationCalculator is not Accessible from all applications scopes by default so you should change it or make a copy to your current scope.

[Matt B4]

Hi Sdarity,

We use this script in our instance and it has been very helpful, thank you. When using the subDaysInSchedule function, it returns the same date whenever I pass it a daysBack value of >25. We have not changed the limitDaysBack variable from 100, do you have any ideas as to why this may be happening?

Thank you,
Matt

[Pragya03]

How to use duration to calculate the past date based on schedule rather than using the days.

[Kristiyan Niko1]

I tried substracting 1 days, but it always substracts 2. Always one more day substracted than the integer provided for daysBack. Do you have an idea why that is happening?

[Yordan Staykov]

You are a real hero my friend