Calculating with time

Question

EDIT:2020.07.24. Here is a spreadsheet where you can see my problem. https://docs.google.com/spreadsheets/d/1-knADrKsStLz5lS_9gayHpZFcaZPsEbBMmrpOGXGMqg/edit?usp=sharing

I have a spreadsheet (That i can't share), made my own custom functions with Scripts.

=cutomF(A1,A2,A3)  
A1 = 8:00  (1899.12.30.08:00)
A2 = 16:00 (1899.12.30.16:00)
A3 = 05:00 (1899.12.30.05:00)

In script if i calculate with just two of these times like A2-A1, it gives back a correct number that i need to divide with /1000/60/60/24

But if i want to calculate this: A2-A3-A1 it just gives back extreme wrong numbers.

Whats could be wrong?

EDIT: The code got a little rewrite from my language to english...

In the spreadsheet cells have time content like this 08:00;

I want to archive something like a comparison between workers.

function CUSTOM_WORK_TIME(ROW_DATA,DAY,KM,ADDRESS,AVERAGE_KM,AVERAGE_ADDRESS,AVERAGE_TIME){
    
    if(ROW_DATA[0][0]!=""){
        Utilities.sleep(50);

        var WORK_TIME;
        var WORK_OVERTIME;
        var WORK_TIME_WEEKEND = ((ROW_DATA[0][2]-ROW_DATA[0][1])/1000/60/60/24);

        var return_array = [[]];

        var KM_RATE = ((KM/AVERAGE_KM)*0.33);
        var ADDRESS_RATE = ((ADDRESS/AVERAGE_ADDRESS)*0.33);
        var TIME_RATE = ((ROW_DATA[0][2]-ROW_DATA[0][1])/AVERAGE_TIME)*0.34;
        var PRINT_THIS;
        
        PRINT_THIS = (ROW_DATA[0][2]-ROW_DATA[0][1])/1000/60/60/24; // JUST FOR TESTING

        switch (ROW_DATA[0][0]){
            case "TRANSPORT":
            if(ROW_DATA[0][2]!="" && ROW_DATA[0][1]!=""){
                
                Utilities.sleep(100);
                
                if(DAY=="SATURDAY" || DAY=="SUNDAY"){
                    return_array[0][0] = "";
                    return_array[0][1] = "";
                    return_array[0][2] = WORK_TIME_WEEKEND;
                    return return_array;
                }else{
                    Utilities.sleep(200);
                    
                    WORK_TIME = ((ROW_DATA[0][2]-ROW_DATA[0][1])/1000/60/60/24);
                    if(WORK_TIME >(9/24)){
                        WORK_OVERTIME = (((ROW_DATA[0][2]-ROW_DATA[0][1])/1000/60/60/24) - (9/24))*(TIME_RATE+KM_RATE+ADDRESS_RATE);
                        WORK_TIME = (9/24);
                        return_array[0][0] = WORK_TIME;
                        return_array[0][1] = WORK_OVERTIME;
                        return_array[0][2] = "";
                        return return_array;
                        // return PRINT_THIS;
                    }else{
                        return_array[0][0] = WORK_TIME*(TIME_RATE+KM_RATE+ADDRESS_RATE);
                        return_array[0][1] = "";
                        return_array[0][2] = "";
                        return return_array;
                    }
                }
            }else{return "H";}

            break;
     
            default:
            break;
        }
    } 
}

Answer 1

I think I've figured out the cause of your issue. I've added a tab, Munkalap1-GK, to your sheet, where I've shown my logic. To get your value of 9:54 (9 hours and 54 minutes) I had to change your formula, and change your cell formatting. The new formula, used by the customF2GK function which I added, is:

function customF2(AVERAGE,START,END,WORK_RATIO) {
  return ((END-START)/AVERAGE)*WORK_RATIO*AVERAGE;}

Secondly, the calculations fail if the cells are formated as hh:mm ss.sss, or anything similar. Format them as a plain number, ie a fraction of a day, and the formulas work. If you prefer, you could use hidden cells to hold the numeric values you pass to the functions. The hidden cells would just point to the current cells, eg. A2, B2, C2, but be formated as numeric, not hh:mm ss.sss. Let me know if that isn't clear to you.

When working with times in Sheets, I find it easiest to work in fractions of a day, just in case dates may also be involved. But it could also be done using just hours, and fractions of an hour (not minutes), if you know you are only dealing with time, not dates as well.

Let me know if this works for you. If it does, you will need to adjust the customF1 and customF3 functions to use comparable logic.