Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – calculate shift hours with break time deductions starting from current night to next day morning using google apps script

I’m trying to calculate the shift hours in a day, accounting for different types of shifts (Normal, Evening, and Night), and also considering overlapping break times during those shifts. Here’s the breakdown of the shifts:

  • Normal Hours: From 06:00 – 18:00. A 30-minute break occurs between
    12:00 – 12:30.
  • Evening Hours: From 18:00 – 21:00. A 30-minute break
    occurs between 20:00 – 20:30.
  • Night Hours: From 21:00 – 06:00. A
    30-minute break occurs between 04:00 – 04:30.

Break Time Rules:

  • If the shift is less than 6 hours, no break is given.
  • If the shift is 6 hours and break time is between this interval, a 20-minute break is provided.
  • For shifts that are longer than 6 hours and break time is between this interval, a 30-minute break is provided.

I need to calculate the hours worked in each shift category. Hers is the code which partly works:

function calculateShiftHours(startShift, endShift) {
  const parseTime = (timeStr) => {
    const [hours, minutes] = timeStr.split(":").map(Number);
    return hours * 60 + minutes;
  };

  const formatHours = (minutes) => (minutes / 60).toFixed(2);

  const NORMAL_START = parseTime("06:00");
  const NORMAL_END = parseTime("18:00");
  const EVENING_START = parseTime("18:00");
  const EVENING_END = parseTime("21:00");
  const NIGHT_START = parseTime("21:00");
  const NIGHT_END = parseTime("06:00");

  const BREAK_TIMES = {
    normal: { start: parseTime("12:00"), end: parseTime("12:30") },
    evening: { start: parseTime("20:00"), end: parseTime("20:30") },
    night: { start: parseTime("04:00"), end: parseTime("04:30") },
  };

  const startTime = parseTime(startShift);
  const endTime = parseTime(endShift) < startTime ? parseTime(endShift) + 1440 : parseTime(endShift);
  const shiftDuration = endTime - startTime;

  let breakDuration = 0;
  if (shiftDuration >= 6 * 60 && shiftDuration < 6.5 * 60) breakDuration = 20;
  else if (shiftDuration >= 6.5 * 60) breakDuration = 30;

  const calculateOverlap = (start, end, rangeStart, rangeEnd) => {
    const overlapStart = Math.max(start, rangeStart);
    const overlapEnd = Math.min(end, rangeEnd);
    return Math.max(0, overlapEnd - overlapStart);
  };

  const calculateShiftHoursInRange = (rangeStart, rangeEnd) => {
    return calculateOverlap(startTime, endTime, rangeStart, rangeEnd);
  };

  let normalHours = calculateShiftHoursInRange(NORMAL_START, NORMAL_END);
  let eveningHours = calculateShiftHoursInRange(EVENING_START, EVENING_END);
  let nightHours = calculateShiftHoursInRange(NIGHT_START, NIGHT_END + 1440); 

  if (breakDuration > 0) {
    if (calculateOverlap(startTime, endTime, BREAK_TIMES.normal.start, BREAK_TIMES.normal.end) > 0) {
      normalHours -= breakDuration;
    } if (calculateOverlap(startTime, endTime, BREAK_TIMES.evening.start, BREAK_TIMES.evening.end) > 0) {
      eveningHours -= breakDuration;
    } if (calculateOverlap(startTime, endTime, BREAK_TIMES.night.start, BREAK_TIMES.night.end) > 0) {
      nightHours -= breakDuration;
    }
  }

  return {
    normal: formatHours(normalHours),
    evening: formatHours(eveningHours),
    night: formatHours(nightHours)
  };
}

function testShiftCalculation(){
  const result = calculateShiftHours("22:00", "07:00"); 
  console.log(result); // Test for a normal shift with break (Expected output should adjust with break time)

}

It works well when start time is less than end time. for example if shift is between 06:00-24:00. But it calculates incorrectly when the shift starts from midnight, for example:

const result = calculateShiftHours("22:00", "07:00"); 

The output should be:

Normal hours:  1 hrs (06:00-07:00) 
Evening hours: 0
Night hours:   8 hrs (22:00-06:00) 

Since shift is 9 hour long and 04:00-04:30 overlaps with Night hours so, 30 minutes deduction.

Final output should be: {normal:'1',evening:'0',night: '7.5'}

But the script output is: { normal: '0.00', evening: '0.00', night: '8.00' }

So, the issue is, it does not calculate hours and break time deduction correctly when shift start time is in current day and shift end time is in next day. Any suggestion would be much appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have been able to get around this limitation by adding a few checks when shift end time move into the next day morning. Here is the modified code, that works well for all intervals along with break dedcutions:

     function calculateHoursGroups(startShift, endShift) {
    
        
      const parseTime = (timeStr) => {
      const [hours, minutes] = timeStr.split(":").map(Number);
      return hours * 60 + minutes;
    };
  
    const formatHours = (minutes) => (minutes / 60).toFixed(2);
    var check = false;
    var endTime;
    const NORMAL_START = parseTime("06:00");
    const NORMAL_END = parseTime("18:00");
    const EVENING_START = parseTime("18:00");
    const EVENING_END = parseTime("21:00");
    const NIGHT_START = parseTime("21:00");
    const NIGHT_END = parseTime("06:00");
    
  
    const BREAK_TIMES = {
      normal: { start: parseTime("12:00"), end: parseTime("12:30") },
      evening: { start: parseTime("20:00"), end: parseTime("20:30") },
      night: { start: parseTime("04:00"), end: parseTime("04:30") },
      midnightBreak: { start: 1680, end: 1710 }, // Midnight break from 28:00 to 28:30
    };
  
      
    const startTime = parseTime(startShift);
    if(parseTime(endShift) < startTime) {
        endTime = parseTime(endShift) + 1440;
        check = true;  
      } 
      else {
        endTime = parseTime(endShift);
      }
  
    const shiftDuration = endTime - startTime;
  
  
  
    let breakDuration = 0;
    if (shiftDuration == 6 * 60) breakDuration = 20;
    else if (shiftDuration > 6 * 60) breakDuration = 30;
  
    const calculateOverlap = (start, end, rangeStart, rangeEnd) => {
  
      // console.log(start)
      // console.log(end)
      const overlapStart = Math.max(start, rangeStart);
      const overlapEnd = Math.min(end, rangeEnd);
      return Math.max(0, overlapEnd - overlapStart);
    };
    
      const calculateShiftHoursInRange = (rangeStart, rangeEnd) => {
        return calculateOverlap(startTime, endTime, rangeStart, rangeEnd);
      };
    
      let normalHours = calculateShiftHoursInRange(NORMAL_START, NORMAL_END);
      let eveningHours = calculateShiftHoursInRange(EVENING_START, EVENING_END);
      let nightHours = calculateShiftHoursInRange(NIGHT_START, NIGHT_END + 1440); 
    
      if (breakDuration > 0) {
        if (calculateOverlap(startTime, endTime, BREAK_TIMES.normal.start, BREAK_TIMES.normal.end) > 0) {
          normalHours -= breakDuration;
        } if (calculateOverlap(startTime, endTime, BREAK_TIMES.evening.start, BREAK_TIMES.evening.end) > 0) {
          eveningHours -= breakDuration;
        } if (calculateOverlap(startTime, endTime, BREAK_TIMES.night.start, BREAK_TIMES.night.end) > 0) {
          nightHours -= breakDuration;
        }
      }
    
        
        // console.log(shiftDuration);
    
      if(check){
           console.log(startTime);
           console.log(endTime)
           console.log("Into the Next Day");
          //  console.log(BREAK_TIMES.midnightBreak.start)
          //  console.log(BREAK_TIMES.midnightBreak.end)
    
           if(nightHours == 360 && startTime < BREAK_TIMES.midnightBreak.start && endTime > BREAK_TIMES.midnightBreak.end){
            nightHours = nightHours - 20;
           }
           if(nightHours > 360 && startTime < BREAK_TIMES.midnightBreak.start && endTime > BREAK_TIMES.midnightBreak.end){
            nightHours = nightHours - 30;
           }
           
           if(endTime > 1800){
            normalHours = endTime - 1800;
           }
    
         }
    
    
      if (startTime < NORMAL_START) { // Handle shifts starting before 06:00
          console.log("Into before normal start time")
          console.log(startTime);
          console.log(endTime);
          nightHours = Math.min(NORMAL_START, endTime) - startTime; 
          normalHours = Math.max(0, endTime - NORMAL_START);
          var totalHours = nightHours + normalHours;
          console.log("Sum: ", totalHours)
          
          if(totalHours == 360 && startTime < 240 && endTime > 270){
            nightHours = nightHours - 20;
          }
  
          if(totalHours > 360 && startTime < 240 && endTime > 270){
            console.log("Into greater sum");
            nightHours = nightHours - 30;
          }
          console.log(nightHours);
          console.log(normalHours)
        }
      
          return {
            normal: formatHours(normalHours),
            evening: formatHours(eveningHours),
            night: formatHours(nightHours)
          };
  }
  
    
 function testShiftCalculation(){
      const result = calculateHoursGroups("16:00", "00:00");
      console.log(result); 
    
    }

    These added checks, explicitly get start and end time, compare them with the break intervals and then compile the results, any suggestions to improve the overall efficiency of the script would be much appreciated.


  2. 0

    So, the issue is, it does not calculate hours and break time deduction
    correctly when shift start time is in current day and shift end time
    is in next day. Any suggestion would be much appreciated.

    This is because when a shift starts in one day and ends in the next, it complicates calculations, especially with break deductions. So, to fix this you would need to

    1. convert the start and end times into a continuous range of minutes, even when the shift crosses midnight.
    2. calculate breaks correctly based on the shift duration rules and adjust only if the break time overlaps with the working shift.

    Try these 2 approaches and see if your issue is fixed. I didnt had enough time to implement a code snippet. So, i explained it here. If i get time i will update the solution here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search