Nachtstundenberechnung/zählung mit unterschiedlichen Zuschlägen

[Ego]

Hallo Brutality, hier die Formeln:




Tabelle1

	A	B	C	D	E	F	G	H	I	J	K	L	M
7
8									Arbeits- zeit	Nacht	Nacht	So	FT
9	Datum	Tag		FT	Beginn	Ende	Pause		0,25	0,4	0,5	1,25
10	30.06.2016	Donnerstag	5	x	0:00	0:00
11	01.07.2016	Freitag	6	x	0:00	0:00	20:00	21:00	23	5,00	4,00	0	23
12	02.07.2016	Samstag	7		0:00	0:00	2:00	4:00	22	6,00	2,00	0	2,00
13	03.07.2016	Sonntag	1		0:00	0:00	10:00	12:00	22	6,00	4,00	22	0
14	04.07.2016	Montag	2		0:00	0:00			24	6,00	4,00	4,00	0
15	05.07.2016	Dienstag	3		0:00	0:00			24	6,00	4,00	0	0
16	06.07.2016	Mittwoch	4		0:00	0:00	21:00	22:00	23	5,00	4,00	0	0

verbundene Zellen

I8:I9

G9:H9

verwendete Formeln
Zelle	Formel	Bereich	N/A
B10:B16	=A10
C10:C16	=WOCHENTAG(A10)
I11:I16	=(-E11+F11+G11-H11)/ZEIT(1;0;0)
J11:J16	=WENN(ODER(E11="";F11="");0;(MAX(0;-WENN(E11=0;MAX(E11;ZEIT(4;0;0));MAX(E11;ZEIT(0;0;0)))+MIN(F11;ZEIT(6;0;0)))+MAX(0;-MAX(E11;ZEIT(20;0;0))+MIN(F11;1))-WENN(ODER(G11="";H11="");0;MAX(0;-WENN(E11=0;MAX(G11;ZEIT(4;0;0));MAX(G11;ZEIT(0;0;0)))+MIN(H11;ZEIT(6;0;0)))+MAX(0;-MAX(G11;ZEIT(20;0;0))+MIN(H11;1))))/ZEIT(1;0;0))
K11:K16	=WENN(ODER(E11="";F11="";E11<>0);0;(MAX(0;-MAX(E11;ZEIT(0;0;0))+MIN(F11;ZEIT(4;0;0)))-WENN(ODER(G11="";H11="");0;MAX(0;-MAX(G11;ZEIT(0;0;0))+MIN(H11;ZEIT(4;0;0)))))/ZEIT(1;0;0))
L11:L16	=WENN(D11="x";0;WENN(UND(C11=1;D10="x";F10=1;E11=0;NICHT(ISTLEER(E11)));(MAX(0;-MAX(E11;ZEIT(4;0;0))+MIN(F11;1))-WENN(ODER(G11="";H11="");0;MAX(0;-MAX(G11;ZEIT(4;0;0))+MIN(H11;1))))/ZEIT(1;0;0);WENN(C11=1;I11;WENN(UND(D10<>"x";C10=1;F10=1;E11=0;NICHT(ISTLEER(E11)));(MAX(0;-MAX(E11;ZEIT(0;0;0))+MIN(F11;ZEIT(4;0;0)))-WENN(ODER(G11="";H11="");0;MAX(0;-MAX(G11;ZEIT(0;0;0))+MIN(H11;ZEIT(4;0;0)))))/ZEIT(1;0;0);0))))
M11:M16	=WENN(D11="x";I11;WENN(UND(D10="x";F10=1;E11=0;NICHT(ISTLEER(E11)));(MAX(0;-MAX(E11;ZEIT(0;0;0))+MIN(F11;ZEIT(4;0;0)))-WENN(ODER(G11="";H11="");0;MAX(0;-MAX(G11;ZEIT(0;0;0))+MIN(H11;ZEIT(4;0;0)))))/ZEIT(1;0;0);0))

Excel-Inn.deHajo-Excel.deXHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 Add-In-Version 23.05 einschl. 64 Bit



Noch einige Anmerkungen:

A) In der Umsetzung habe ich noch folgende Regel berücksichtigt:
Wenn der Samstag ein Feiertag ist und ich über den Tageswechsel arbeite, bekommen in den ersten vier Stunden den Feiertagszuschlag und in den späteren Stunden den Sonntagszuschlag.

B) Wenn ich über den Monatswechsel arbeite und der letzte Tag des Vormonats ein Feiertag oder Sonntag war, möchte ich auch einen Zuschlag.
Die Arbeitszeiten und die Tagestypen des letzten Tages des Vormonats müssen also auch noch erfasst werden.

C)Boskobiati hatte erwähnt, dass man auf die Funktion Zeit() verzichten kann.
Wenn  dir das besser gefällt kannst du ( da du ja jetzt weist, dass in Excel ein Tag = 1 und eine Stunde = 1/24 entsprechen) in den Formeln zB "Zeit(4;0;0)" durch "4/24" oder "1/6" ersetzen und "/Zeit(1;0;0)" durch "*24".

D) Bei dir ist die Wochentagsnummer des Sonntags eine 1. Dies ist die amerikanische Zählweise. Im Deutschen beginnt die Woche mit dem Montag.
Wenn dich oder einem Benutzer das stört kannst du es wechsel.
1. Spalte B
zB. B10: "=A10"
2. Spalte C
zB. C10: "=Wochentag(A10;2)"  (hier steht die 2 für die deutsche Zählweise.
3. In den Formeln, in denen die Spalte C auf den Wert 1 geprüft wird muss dann auf 0 geprüft werden.