Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Datum/Zeit - BEITRAG: Berechnung der Netto-Arbeitszeit bei fester Pausenvorgabe *


Aufgabe
Zwischen Arbeitsbeginn (Spalte A) und Arbeitsende (B) können bis zu 3 Pausen liegen
(C-D / E-F / G-H).
Die Nettoarbeitszeit soll errechnet werden.

       A               B               C               D               E               F               G               H               I       
1 von  bis  Pause 1    Pause 2    Pause 3    Arbeitszeit  
2 8:00  18:00  9:00  9:15  12:30  13:00  16:00  16:15  9:00  
3 22:00  2:00  23:45  0:15  12:30  13:00  16:00  16:15  3:30  
4 0:01  23:59  23:30  0:30          23:00  
5 0:01  23:59  9:00  9:15  12:30  13:00  16:00  16:15  22:58  
6 0:00  0:00  9:00  9:15  12:30  13:00  16:00  16:15  0:00  
7     9:00  9:15  12:30  13:00  16:00  16:15  0:00  
8 8:00  18:00              10:00  
9 8:00  18:00  0:00  0:00          10:00  
10 8:00  18:00  9:00  9:15  9:10  9:30      9:30  
11 8:00  18:15  12:00  12:30  9:00  9:15  18:00  18:30  9:15  
12 20:00  6:00  1:00  1:15  1:15  1:20  23:59  0:01  9:38  
13 20:00  6:00  1:00  1:15  23:00  23:30      9:15  
14 8:00  19:00  18:30  19:30          10:30  
15 20:00  5:00  23:30  0:00  0:00  0:30      8:00  
16 8:00  19:00  12:00  13:00  12:00  13:00  12:00  13:00  10:00 


Lösung
in i2:
{=SUMMENPRODUKT(WENN(A2<=B2;N((ZEILE($1:$1440)>(A2*1440))*(ZEILE($1:$1440)<=(B2*1440)));N(ZEILE($1:$1440)>(A2*1440))+N(ZEILE($1:$1440)<=(B2*1440)));N(WENN(C2<=D2;N((ZEILE($1:$1440)>(C2*1440))*(ZEILE($1:$1440)<=(D2*1440)));N(ZEILE($1:$1440)>(C2*1440))+N(ZEILE($1:$1440)<=(D2*1440)))=0);N(WENN(E2<=F2;N((ZEILE($1:$1440)>(E2*1440))*(ZEILE($1:$1440)<=(F2*1440)));N(ZEILE($1:$1440)>(E2*1440))+N(ZEILE($1:$1440)<=(F2*1440)))=0);N(WENN(G2<=H2;N((ZEILE($1:$1440)>(G2*1440))*(ZEILE($1:$1440)<=(H2*1440)));N(ZEILE($1:$1440)>(G2*1440))+N(ZEILE($1:$1440)<=(H2*1440)))=0))/1440}

kürzer von Josef B:
=SUMMENPRODUKT((ZEILE($1:$1439)>A2*1440)+(ZEILE($1:$1439)<=B2*1440)-(A2<=B2);(ZEILE($1:$1439)<=C2*1440)+(ZEILE($1:$1439)>D2*1440)-(C2>D2);(ZEILE($1:$1439)<=E2*1440)+(ZEILE($1:$1439)>F2*1440)-(E2>F2);(ZEILE($1:$1439)<=G2*1440)+(ZEILE($1:$1439)>H2*1440)-(G2>H2))/1440

jeweils runterkopieren

Erläuterung
Schwierigkeiten: Berücksichtigung von Pausen vor und/oder nach 24:00 Uhr
Selbst unsinnige Eingaben - wie sich überlappende Pausen - werden abgefangen.

Für den Normalfall (keine Arbeitszeiten und Pausen über Mitternacht hinaus):
=B2-A2-(B2>C2)*(A2 < D2)*(MIN(B2;D2)-MAX(A2;C2))-(B2>E2)*(A2 < F2)*(MIN(B2;F2)-MAX(A2;E2))-(B2>G2)*(A2 < H2)*(MIN(B2;H2)-MAX(A2;G2))