Excel Arbeitstundenanteile vor 20 Uhr, nach 20 Uhr - 24 Uhr, nach 24 Uhr berechnen lassen?
Ich habe eine Excel-Datei über meine Stundenabrechnung.
Es ist so geregelt, dass alle Arbeitsstunden bis 20 Uhr mit 10€ vergütet werden, alle Arbeitsstunden ab 20 Uhr mit 12,50€ und alle ab 24 Uhr mit 14€. Ich habe in Zelle D8 Beginn und in Zelle E8 Ende meiner Arbeitszeit, nun möchte ich die jeweiligen Anteile in jeweils einer Zelle anzeigen lassen.
Also beispielhaft, Arbeitsbeginn 15:00 Uhr und Ende 01:00 Uhr, in Zelle G8 soll die Zeit vor 20 Uhr angezeigt werden, also 5 Stunden, in H8 soll die Zeit von 20-24 Uhr angezeigt werden, also 4 Stunden und in I8 soll die Zeit nach 24 Uhr angezeigt werden, also 1 Stunde. Wie kann ich das realisieren, wenn ich jeweils nur Arbeitsbeginn und -ende eintragen will.
Vielen Dank schon mal für die Mühe!
2 Antworten
Da fehlt ja was:
"Arbeitsstunden vor 20 Uhr" sind ja gleichzeitig "Arbeitsstunden nach 24 Uhr" (oder genauer: nach 0 Uhr.)
Wenn man von 4 bis 5 arbeitet: sind das dann "Stunden nach 24 h" oder "Stunden vor 20 h"?
Oder gibst du Zeiten > 24:00 Uhr ein? Also 1 Uhr morgens als "25:00"?
Aber die Lösung ist schnell erstellt, wenn man die Grenzen kennt.
Um auch die Zeiten über Mitternacht korrekt zu berücksichtigen:
Von 6 bis 20 Uhr: =MAX(MIN(WENN(E8<D8;E8+1;E8);20/24)-MAX(D8;6/24);0)
Von 20 bis 24 Uhr: =MAX(MIN(WENN(E8<D8;E8+1;E8);24/24)-MAX(D8;20/24);0)
Von 24 bis 6 Uhr: =MAX(MIN(E8;6/24)-MAX(WENN(D8>E8;D8-1;D8);0/24);0)
Nein, die Formatierung passt. Das Ergebnis muss auch in HH:MM formatiert werden, 3,5 h werden dann angezeigt als 3:30.
Wenn du das anders willst, kann man das umrechnen (mit 24 multipliziere um eine Dezimalzahl in Stunden zu erhalten)
Gib doch mal an, welche Beginn- und Endzeiten du eingibst, was bei meinen Formeln als Ergebnis herauskommt und was du dir als Ergebnis erwartest.
Jetzt klappt es, das mit 24 multiplizieren habe ich nicht bedacht, danke!
Jetzt habe ich nur noch ein Problem, wenn man Sonntags arbeitet, dann bekommt man den ganzen Tag 14€ Lohn, sprich, dass muss noch berücksichtigt werden.
In den Zellen von 6-20 Uhr, von 20-24 und von 24-6 Uhr habe ich das bereits mit einer Wenn Formel gelöst, dass wenn in Zelle D8 Sonntag steht in den jeweiligen Zellen 0 angezeigt wird und dafür in Zelle L8 die Gesamtstundenanzahl. Jetzt habe ich nur noch das Problem, wenn man Sonntags bis nach 24 Uhr arbeitet, diese Stundenanzahl nicht in der Zelle (von 24-6 Uhr) erscheint, weil es ja mit der Wenn Formel verneint wurde.
Gibt es hierfür auch eine Lösung?
Warte: in D8 steht nicht nur die Uhrzeit sondern Datum UND Uhrzeit? Dann wundert mich aber, dass meine Fomeln überhaupt funktonieren.
Nochmal: schreib exakt an, was in als Eingabe in den Zellen steht (also nicht "da steht die Zeit" sondern zb, "23.9.2019 23:45") und was du dir wo als Ergebnis wünschst.
In Zelle C8 steht der Wochentag (hab mich vorher verschrieben).
In Zelle D8 der Arbeitsbeginn (zb 18:00) und in E8 Arbeitsende (zb 23:00).
In Zelle L8 soll nun, wenn in C8 Sonntag steht, die gesamte Stundendifferenz von E8 und D8 stehen und zwar in Dezimalformat (also zb 5,0). Allerdings soll, wenn an einem Sonntag bis nach 24:00 gearbeitet wird, in Zelle L8 nur die Zeit vor 24 erscheinen und die Stunden die dann zum Montag gehören, in der Spalte K8 (Nachtzuschlag nach 24 Uhr) erscheinen.
Zb Arbeitszeit Sonntag von 17:00 - 2:00, dann soll in L8 stehen 7,0 und in K8 2,0.
Für die anderen Wochentage passt die Formel, so wie du sie oben geschrieben hast schon.
Bei Fragen einfach nochmal melden.
Das wird wohl so gerechnet, dass das Entscheidende immer der Arbeitsbeginn ist. die Zeit von 0-2 Uhr wäre dann nach 24 Uhr, wenn der Arbeitsbeginn um 15.00 Uhr war. Die Zeit von 15-20 Uhr wäre dann vor 20 Uhr. 🙂
Formel C2:
=WENN(ODER(A2="";B2="");"";WENN(A2>=$C$1;"";WENN(UND(A2<=$C$1;B2<=$C$1;B2-A2>0);B2-A2;$C$1-A2)))
Formel D2:
=WENN(ODER(A2="";B2="");"";WENN(UND(WENN(A2<=$C$1;$C$1;A2)-B2>0;B2-A2>0);"";WENN(UND($D$1+1>=B2;B2-A2>=0);B2-WENN(A2<=$C$1;$C$1;A2);$D$1+1-WENN(A2<=$C$1;$C$1;A2))))
Formel E2:
=WENN(ODER(A2="";B2="");"";WENN(UND(B2-A2<=0;B2>=$D$1);B2-$D$1;""))
Formatierung C1 und D1: "bis "hh:mm
Arbeitsstunden nach 24 Uhr gilt bis 6:00 morgens, ab dann wieder, Arbeitsstunden vor 20 Uhr.