Office: Nachtzulage berechnen

Hallo zusammen!

Ich bräuchte einmal das Schwarmwissen:

In Zelle E5 ist der Arbeitsbeginn eingetragen, Zelle F5 beinhaltet das Arbeitsende.

Nun dürfen aber nur die Stunden berechnet werden, die zwischen 20 h abends und 6:00 Uhr morgens liegen, und hiervon ist dann noch eine Stunde Pause abzuziehen.

Bislang habe ich folgende Formel, die aber nur stumpf die Differenz zwischen 22:00 und 6: berechnet und eine Stunde runterrechnet
=WENN(F22<=6/24;REST(F22-23/24;1);ZEIT(8;0;0))

 

http://hajo-excel.de/gepackt/fremd/arbeitszeit_enthalten.zip

 

Hola,
Nachtzuschlag in excel wurde tausendfach beantwortet….
excel nachtzulage berechnen - Google Suche
Gruß,
steve1da

 

Danke für den Hinweis!

Ich habe nun folgende Formel angewandt und entsprechend angepasst

=MAX(;1-MAX(A2;A1)+MAX(;B1-A2)+MIN(B2;B1)+(B2>A1)*(B2-A1)-(A2 < B2)*(1-(A1-B1))-(A1 < B1)*(B2+(A2 > B2)-A2))*(A2<>B2)*(A1<>B1)

=MAX(;1-MAX(E20;M20)+MAX(;N20-E20)+MIN(F20;N20)+(F20>M20)*(F20-M20)-(E20 < F20)*(1-(M20-N20))-(M20 < N20)*(F20+(E20 > F20)-E20))*(E20<>F20)*(M20<>N20)

Ein kleines Mosaiksteinchen fehlt jetzt noch, damit das ganze für mich perfekt ist:

Kann man die Formel so anpassen, das, wenn in den Feldern E20 und F20 keine Uhrzeiten stehen, automatisch x Stunden als Grundlage für die Nachtzulage genommen werden?

Wenn der Mitarbeiter Krank ist oder Urlaub hat, verbinde ich die beiden Zellen, und schreibe da 'Urlaub' oder 'Krank'.

 

=wenn(und(E20="";F20="");XSTUNDEN;Max(;.....))

 

Danke für die Anpassung, komischerweise funktioniert es leider nicht.

So sollte das doch eigentlich korrekt sein, oder habe ich da jetzt trotz mehrfacher Kontrolle etwas übersehen?

=WENN(UND(E5="";F5="");8;MAX(;1-MAX(E5;M5)+MAX(;N5-E5)+MIN(F5;N5-1/24)+(F5>M5)*(F5-M5)-(E5 < F5)*(1-(M5-N5))-(M5 < N5)*(F5+(E5 > F5)-E5))*(E5<>F5)*(M5<>N5))

Und leider enthält meine angepasste Formel auch noch ein Problem:

In M20 steht der Beginn der Nachtzulage mit 20:00 h, in N20 mit 6:00 h

Ist die Arbeitszeit länger als 6:00, wird die Nachtzulage korrekt abzüglich einer Stunde Pause berechnet. Das habe ich durch die hervorgehobene Anpassung erreicht.:

=MAX(;1-MAX(E20;M20)+MAX(;N20-E20)+MIN(F20;N20-1/24)+(F20>M20)*(F20-M20)-(E20 < F20)*(1-(M20-N20))-(M20 < N20)*(F20+(E20 > F20)-E20))*(E20<>F20)*(M20<>N20)

Wird jedoch genau bis 6:00 gearbeitet, oder unterschreitet die Arbeitszeit die Grenze von 6:00, wird die ganze Arbeitszeit als Nachtzulage berechnet und nicht eine Stunde Pause abgezogen.

Sprich also:

Arbeitszeit von 21:50 h bis 6:30 = Nachtzulage 7:10 (22:50 bis 6:00) - korrekt
Arbeitszeit von 20:30 h bis 5:00 = Nachtzulage 8:30 (20:30 bis 6:00) - falsch

Ich habe den Abzug der einen Stunde innerhalb der ganze Formel probiert, aber es hat nicht zum Erfolg geführt

 

Mit "funktioniert leider nicht" kann außer dir niemand etwas anfangen. Sind die beiden Zellen wirklich leer?
Lade eine Exceldatei mit ein paar Beispielen hoch wo man sieht, was wann berechnet werden soll.

 

Hallo @steve1da

Anbei die Tabelle

Und Danke vorab schon für die Hilfe

 

Gut meine Lösunhg hat nicht gefallen.
Ich bin dann raus.

Tabelle1

	I	J	K	L
17	20:00	06:00	14	08:10
18			14	08:30
19			14	07:00
20			14	07:30
21			14	08:00
22			14	07:30
23			14	07:30
24			14	08:30
25			14	09:30

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
K17:K25	=WENN(REST(G17-D17;1)>8/24;14;0)
L17:L25	=MAX(;MIN($F17+($E17>$F17);MAX((I$17>J$17);J$17))-MAX($E17;J$17))+MAX(;MIN($F17;MAX((I$17>J$17);J$17))-MAX(;I$17))*($E17>$F17)+MAX(;MIN($F17+($E17>$F17);MIN((I$17>J$17);J$17))-MAX($E17;))+MIN($F17;MIN((I$17>J$17);J$17))*($E17>$F17)

Zahlenformate
Zelle	Format	Inhalt
I17	'hh:mm		0,833333333333333
I18:J25	'hh:mm
J17	'hh:mm		0,25
K17:K25	'#.##0,00 €		14
L17	'hh:mm		0,340277777777778
L18	'hh:mm		0,354166666666667
L19	'hh:mm		0,291666666666667
L20, L22:L23	'hh:mm		0,3125
L21	'hh:mm		0,333333333333333
L24	'hh:mm		0,354166666666667
L25	'hh:mm		0,395833333333333

Zellen mit Format Standard werden nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
I17:L25									Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

Gruß Hajo

 

@Hajo_Zi ,

hab ich das irgendwo geschrieben?

Ich habe sie nicht heruntergeladen bekommen.

 

aber nicht in diesem Beitrag da steht "nicht" im ZUsammenhamg mmit runterladen.

Gruß Hajo

 

Nein, da steht nichts von herunterladen. Aber ein Link mit einer zip-Endung bedeutet in der Regel Download einer Datei.

Nachdem ich den Link kopiert und an das http ein s angehängt habe, funktionierte der Download. Aber so ganz schlau werde ich da, was meinen Fall betrifft, leider auch nicht...

 

Gut meine angepasste Lösung hat Dir nicht gefallen.
Vielleicht poistet noch jemand eine andere Lösung.
viel Erfolg.

Gruß Hajo

 

Hallo,

sieh dir mal die Datei an, ob da was dabei ist.

Gruß
Roger

 

@Hajo_Zi

Wenn Du damit die Formel im gelben Kasten meinst:

Sie gibt das aus, was ich mit meiner Formel schon hatte. Der Fehler, bei Arbeitsende vor 6:00 h ist geblieben und auch bei einem Arbeitsende nach 6:00 wird die Nachtzulage komplett angerechnet und keine Pause abgezogen.

Werden, wie im Beispiel im Bereich D3 - G16, keine Uhrzeiten sondern Text eingegeben (bzw. der graue Kasten eingefügt), werden 0:00 h als Nachtzulage ausgegeben, keine 8