I have a Google Spreadsheets I have been using to keep track of my hours worked at my job. I am trying to create a custom function to calculate my total hours for the week. Say I work 6 hours of overtime, but then take Friday off. My regular hours would be 32, and I would have 6 hours or overtime. In the event that I don't work 40 hours, I would like to adjust my total hours by taking from any overtime hours and adding to my regular hours.
I have come up with the following function, but I have not yet been able to make it work. I believe I am running into a problem with data types (the inputs are Durations), but I'm not sure how to resolve it. I am dividing by 24 because that seems to convert the values from Duration to Number, but I still can't get it to return the correct answer.
function calcAdjRegHours(regHours, otHours) {
if(regHours<(40/24));
{
if(otHours>(0/24));
{
if((regHours + otHours)>(40/24));
{
var diff = (40/24) - regHours;
regHours += diff;
return regHours;
} elseif; {
return "regHours + otHours is less than 40";
}
} elseif; {
return "there are no otHours";
}
} elseif; {
return "regHours is greater than 40";
}
}
What am I overlooking, or am I making this overly complicated?
Edit: When I call this function with inputs of 40:00, and 2:00, I get the value:
Sun Dec 31 1899 17:00:00 GMT-0700 (MST)2208988800001.6665.
If I run this function:
function calcAdjRegHours(regHours, otHours) {
return ((regHours*24 + otHours*24)/24);
}
I get: -4418114400000.
If I use "return (regHours + otHours);", I get:
Sun Dec 31 1899 17:00:00 GMT-0700 (MST)Sat Dec 30 1899 03:00:00 GMT-0700 (MST).
Something is going wrong when I try to add the variables. They are formatted as Duration, and from my research I can/need to convert them to do arithmetic. I did that by multiplying the variables by 24, adding, and then dividing by 24 again to get it back to a duration.
