I have a sheet used to calculate worked time. The aim is to sum up the hours and divide them in "regular" ones (up to 8h per day) and "overtime" ones (anything more than 8h per day). Each date has to have two rows(the second one is hidden by default), as there are multiple places I can work at, but the time calculated should be summed. Also, time worked on Saturday and Sunday should always be counted as overtime.
The problem I have is with calculating overtime hours. Regular hours are generated by:
=arrayformula(IFS(
WEEKDAY(A3:A70; 1)=1;0;
WEEKDAY(A3:A70; 1)=7;0;
E3:E70+E4:E70>8;8;
E3:E70+E4:E70<=8; E3:E70+E4:E70))
They are summed by =SUM(IFERROR(G3:G71;0)), which works just fine. Overtime hours are generated by:
=arrayformula(IFS(
WEEKDAY(A3:A70; 1)=1;E3:E70+E4:E70;
WEEKDAY(A3:A70; 1)=7;E3:E70+E4:E70;
E3:E70+E4:E70>8;E3:E70+E4:E70-8;
E3:E70+E4:E70<=8; 0))
And summed similarly by =SUM(IFERROR(H3:H71;0)) in H72. However, it returns a wrong value - in the sample sheet, it is 57 instead of 7
If I select the summed range, the tooltip shows the correct sum (7). If I add/remove the decimal place or change the formatting in H72, it suddenly changes to the correct one, too. However, when any new data is added (i.e. hours form a new day), it goes back to showing incorrect values.
It is not a simple display error, because the values are then imported by another sheet via =IMPORTRANGE and it imports those wrong values.
Any idea how to fix it?
