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;
        }
    } 
}

Hi. What is your expected result? What about the built in date subtraction (`=A2-A3-A1`) doesn't work? (It works in my sheet, using your time values.) Can you post the code for `cutomF`? (maybe typo for `customF`?) — dwmorrin, Jul 22 '20 at 15:31
If your function is `CUSTOM_WORK_TIME(ROW_DATA,DAY,KM)` and you use it like `=CUSTOM_WORK_TIME(A1,A2,A3)` this means you want to calculate `DAY-ROW_DATA-KM`? If not - please show the correct code of `cutomF`. — ziganotschka, Jul 23 '20 at 09:00
Made a sheet, so you can se whats the problem https://docs.google.com/spreadsheets/d/1-knADrKsStLz5lS_9gayHpZFcaZPsEbBMmrpOGXGMqg/edit?usp=sharing — Richárd Nagy, Jul 23 '20 at 20:12
I still do not udnerstand the purpose of function `CUSTOM_WORK_TIME` (which is not in your spreadsheet). Do you want to calculate `A2-A3-A1`? So `16:00-05:00-08:00`? What result do you expect? `3`? — ziganotschka, Jul 24 '20 at 13:43

kirkg13 · Answer 1 · 2020-07-25T00:10:55.457

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.

Thank you Kirkg13! I just dont get it, why cant i get RAW data from a cell. I think its not normal that formating is changing the data. Because the sheet it big, with alot of info, its not the best that i ad more columns just to get the correct data. — Richárd Nagy, Jul 29 '20 at 06:56
I understand. I believe that Excel behaves differently, and that a date value is always treated as a simple number, regardless of how it is formatted to appear. But perhaps you can do some more research to see if there are other answers on the interent relating to how formatting may affect calculations in Google Sheets. I haven't looked for that yet. — kirkg13, Jul 29 '20 at 11:53
And if this answer has helped you, you can check it, to help others with a similar question. See here: https://stackoverflow.com/help/someone-answers — kirkg13, Jul 29 '20 at 11:55

Calculating with time

1 Answers1