1

I'm creating a custom function where I would like to update the start time of a schedule depending if they had Overtime.

For example, Employee1 entire schedule is from 8:45 AM - 7:00 PM, however this person had Overtime before his real shift started, so I'm trying to update his start time based on that, but when I try to compare the start time with the Overtime start time, for some reason even when they both are equal, the script doesn't recognize them, below my code:

function calculateStart(shiftStart, shiftEnd, scheduleState, scheduleStateStart, scheduleStateEnd) {
  var newScheduleState = [];
  var res;
  var newShiftStart = "Start";
  var sLen = scheduleState.length;
  
  //Creating new array that contains only the schedule information for one day
  for (i = 0; i < sLen; i++) {
    res = String(scheduleState[i]).slice(0, 3);
    if(res != "Req"){
      newScheduleState.push(scheduleState[i]);
    }else{
      break;
    }
  }

  //Checkig if shiftStart needs to be updated based on Overtime
  sLen = newScheduleState.length;
  for (i = 0; i < sLen; i++){
    if(String(newScheduleState[i]) === "Overtime"){
      newShiftStart = "Yes, this is overtime";
    //NOT SURE WHY the next line is false 
    if(shiftStart == scheduleStateStart[i]){
      newShiftStart = "Yes, they are the same" + shiftStart + ' - ' + scheduleStateStart[i];
      if(String(newScheduleState[i+1]) === "CLINIC"){
        newShiftStart = "End Clinic"
        //newShiftStart = scheduleStateEnd[i+1];
      }else{
        newShiftStart = "Overtime"
        //newShiftStart = scheduleStateEnd[i];
      }
    }else{
      newShiftStart = "Not the same " + shiftStart + ' --- ' + scheduleStateStart[i];
    }
    }
  }
  return newShiftStart;
  // Returns: Not the same Sat Dec 30 1899 09:47:04 GMT-0500 (GMT-05:00) --- Sat Dec 30 1899 20:02:04 GMT-0500 (GMT-05:00)
}

And below and image of the parameters:

custom function

Adding a link to sample file here

Sheets Lover
  • 13
  • 3
  • Can you share a sample sheet that we could work on? – NightEye Jan 14 '21 at 15:34
  • 1
    Sure, you can find it here [link](https://docs.google.com/spreadsheets/d/1jFfLwSHz58g6HcpVJYZwCv0pYpDG1wqNH3zxJABzWg0/edit?usp=sharing) – Sheets Lover Jan 14 '21 at 15:50
  • 1
    @NaziA Date equality will always fail. Instead, you need to compare against the primitive value using `.valueOf()` or `getTime()`. – Diego Jan 14 '21 at 16:58

1 Answers1

1

When trying to check the values by using JSON.stringify, they returned:

output

The first one is the shiftStart while the 2nd is scheduleStateStart[i].

What you need to do is first, access the scheduleStateStart[i] first element which is scheduleStateStart[i][0].

I did JSON.stringify both of them then compare. The condition should look like this:

if(JSON.stringify(shiftStart) == JSON.stringify(scheduleStateStart[i][0])){

This should return End Clinic

output

Note that when you opt to remove JSON.stringify on the variables, it will return false.

NightEye
  • 10,634
  • 2
  • 5
  • 24