Add number of hours to a DateTime while skipping non-business hours in Javascript

Question

The Problem :

If a person has been given 2 hours for a task, the script needs to calculate when it should be done. The planned time should not be after office hours. It should take into account the time that is left today and add the remaining time to the next office hours. But the next day can be a weekend or can be a holiday which needs to be skipped.

What the script does :

Given a date and time, it adds x number of hours. While doing that it skips off-hours and adds the remaining hour to the next day after opening time. If the next day is a weekend or a holiday, it skips that as well.

Where I am getting stuck :

If the number of hours I add makes it go to the next day, the calculation becomes off. For example, if at 17 hours I add 10 hours, the logic breaks.

The code :

function w(d) {
  let sheet = ss.getSheetByName("Holidays")
  let holidayDates = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues()
  
  let open_h = 9 //opening time
  let close_h = 18 //closing time
  let work_h = close_h - open_h //total working hours
  let add_h = 2 //time that was given to do the task (in hours) <- If adding this takes the end_date past 00:00:00 the logic breaks.
  d.setDate(d.getDate() + (1 + 7 - d.getDay()) % 7); 

  let end_date = d

  if (end_date.getHours() < open_h) {
    end_date.setHours(open_h)
  }

  end_date.setTime(end_date.getTime() + (add_h*60*60*1000))


  if (end_date.getHours()>open_h && end_date.getHours()

Kristkun · Accepted Answer

You can refer to this sample code to compute for the end datetime based on the task hours.

Holidays Sheet (Contains Date values):

Sample Code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
function test(){
  var startDate = new Date("August 27 2021 17:00:00")
  Logger.log(startDate);
  var endDate = w(startDate);
  Logger.log(endDate);
}

function w(d) {
  let sheet = ss.getSheetByName("Holidays")
  let holidayDates = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues().flat();
  
  //convert holiday dates to array string in yyyy-MM-dd format
  let holidayDatesStr = holidayDates.map(x => Utilities.formatDate(x, Session.getScriptTimeZone(),"yyyy-MM-dd"));

  
  let open_h = 9 //opening time
  let close_h = 18 //closing time
  let work_h = close_h - open_h //total working hours
  let add_h = 10 //time that was given to do the task (in hours) <- If adding this takes the end_date past 00:00:00 the logic breaks.

  //Compute for days and remaining hours needed
  let end_date = d
  let remain_h = close_h - d.getHours();//remaining hours for the current day
  let targetDays = 0;
  let targetHrs = 0;

  if(remain_h - add_h < 0){
    
    targetDays++; //Task cannot be finished today. Increment target day
    targetDays += Math.floor((add_h - remain_h) / work_h); //Get total number of days to complete
    targetHrs = (add_h - remain_h) % work_h; //Get remaining hours of the day

    if(targetHrs == 0){
      //task can be finished on the closing hour. Reduce targetDays by 1
      targetDays--;
      //Add work_h to the current targetHrs
      targetHrs += work_h;
    }
    Logger.log("Target Days: "+targetDays)
    Logger.log("Target Hours: "+targetHrs);
    //Increment days (Skip weekends and holidays)
    while (targetDays>0){
      //Increment end date by 1
      end_date.setDate(end_date.getDate()+1);
      
      //Check if end date falls under weekends
      if((end_date.getDay() == 0)||(end_date.getDay() == 6)){
        continue; //skip current end date
      }

      //Check if end date is a holiday
      let endDateStr = Utilities.formatDate(end_date, Session.getScriptTimeZone(),"yyyy-MM-dd");
      if(holidayDatesStr.includes(endDateStr)){
         continue; //skip current end date
      }
      
      //valid date
      targetDays--;
    }

    //Set hours
    end_date.setHours(open_h + targetHrs);

  }else{
    targetDays = 0;
    targetHrs = add_h;
    end_date.setHours(end_date.getHours()+add_h); //Add to current hour
  }

  return end_date
}

What it does?

Basically, I just add 1 additional parameter to consider when computing the end date which is the number of days to complete the task.
If the task cannot be done within the current date. Compute for the target days needed and the target hours to complete the task.
Increment the end datetime one by one check if the current end datetime falls on weekends or if it a holiday. Skip that date
Once you get the end date. Set the end time by adding the target hours to your open hour.

Output:

If add_h = 10 and startDate = "August 27 2021 17:00:00",

End date will be "August 30, 2021 18:00"

1:07:42 AM  Notice  Execution started
1:07:43 AM  Info    Fri Aug 27 17:00:00 GMT+08:00 2021
1:07:43 AM  Info    Target Days: 1
1:07:43 AM  Info    Target Hours: 9
1:07:43 AM  Info    Mon Aug 30 18:00:00 GMT+08:00 2021
1:07:43 AM  Notice  Execution completed

If add_h = 20 and startDate = "August 27 2021 17:00:00",

End date will be "September 3, 2021 18:00" since 8/31 and 9/2 are a holiday.

1:09:24 AM  Notice  Execution started
1:09:25 AM  Info    Fri Aug 27 17:00:00 GMT+08:00 2021
1:09:25 AM  Info    Target Days: 3
1:09:25 AM  Info    Target Hours: 1
1:09:25 AM  Info    Fri Sep 03 10:00:00 GMT+08:00 2021
1:09:25 AM  Notice  Execution completed

(UPDATE)

See sample code for floating add hours

var ss = SpreadsheetApp.getActiveSpreadsheet();
function test(){
  var startDate = new Date("August 27 2021 17:30:00")
  Logger.log(startDate);
  var endDate = w(startDate);
  Logger.log(endDate);
}

function w(d) {
  let sheet = ss.getSheetByName("Holidays")
  let holidayDates = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues().flat();
  
  //convert holiday dates to array string in yyyy-MM-dd format
  let holidayDatesStr = holidayDates.map(x => Utilities.formatDate(x, Session.getScriptTimeZone(),"yyyy-MM-dd"));

  let open_h = 9 //opening time
  let close_h = 18 //closing time
  let work_h = close_h - open_h //total working hours
  let add_h = 10.25 //time that was given to do the task (in hours) <- If adding this takes the end_date past 00:00:00 the logic breaks.

  //Compute for days and remaining hours needed
  let end_date = d
  let remain_h = close_h - d.getHours();//remaining hours for the current day
  let targetDays = 0;
  let targetHrs = 0;
  let targetMins = 0;

  if(remain_h - add_h < 0){
    
    targetDays++; //Task cannot be finished today. Increment target day
    targetDays += Math.floor((add_h - remain_h) / work_h); //Get total number of days to complete
    targetHrs = (add_h - remain_h) % work_h; //Get remaining hours of the day
    if(targetHrs == 0){
      //task can be finished on the closing hour. Reduce targetDays by 1
      targetDays--;
      //Add work_h to the current targetHrs
      targetHrs += work_h;
    }
    targetMins = (targetHrs - Math.floor(targetHrs)) * 60;
    Logger.log("Target Days: "+targetDays)
    Logger.log("Target Hours: "+targetHrs);
    Logger.log("Target Minutes: "+targetMins);
    //Increment days (Skip weekends and holidays)
    while (targetDays>0){
      //Increment end date by 1
      end_date.setDate(end_date.getDate()+1);
      
      //Check if end date falls under weekends
      if((end_date.getDay() == 0)||(end_date.getDay() == 6)){
        continue; //skip current end date
      }

      //Check if end date is a holiday
      let endDateStr = Utilities.formatDate(end_date, Session.getScriptTimeZone(),"yyyy-MM-dd");
      if(holidayDatesStr.includes(endDateStr)){
         continue; //skip current end date
      }
      
      //valid date
      targetDays--;
    }

    //Set hours (decimal ignored)
    end_date.setHours(open_h + targetHrs);

    //Set minutes
    end_date.setMinutes(end_date.getMinutes() + targetMins);

  }else{
    targetDays = 0;
    targetHrs = add_h;
    targetMins = (targetHrs - Math.floor(targetHrs)) * 60;
    
    end_date.setHours(end_date.getHours()+add_h); //Add to current hour
    end_date.setMinutes(end_date.getMinutes() + targetMins);
  }

  return end_date
}