Datum/Zeit (1) -> Berechnung der Netto-Arbeitszeit bei fester Pausenvorgabe * (403)

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 JosefB:
=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

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 überMitternacht hinaus) langt:
=B2-A2-MAX(0;MIN(B2+0;D2+0)-MAX(A2;C2))-MAX(0;MIN(B2+0;F2+0)-MAX(A2;E2;D2))-MAX(0;MIN(B2+0;H2+0)-MAX(A2;G2;F2))
Die Pausen müssen in aufsteigender Uhrzeit angegeben sein.



zurück

gestaltet by Biene Maya