(Excel 2008) Überstunden berechnen
- Ersteller Jogi193
- Erstellt am
M
Mr. Snoot
Gast
Vertausche mal in der H15-Formel die letzten beiden Wenn-Funktionen:
=WENN(UND(WOCHENTAG(B15)=7;D15<0,5);D15*0,5;WENN(UND(WOCHENTAG(B15)=1);(D15-C15)*0,5;WENN(UND(WOCHENTAG(B15)=1;D15<0,5);(1-C15)*0,5;"")))
Zuletzt bearbeitet:
=WENN(UND(WOCHENTAG(B23)=7;D23<0,5);D23*0,5;WENN(UND(WOCHENTAG(B23)=1;D23<0,5);(1-C23)[/SIZE][/B]*0,5;WENN(UND(WOCHENTAG(B23)=1;D23>C23);(D23-C23)*0,5;"0")))
liegt der Fehler nicht bei rechne die Anfangszeit minius 24 Stunden? also (1-C23)
liegt der Fehler nicht bei rechne die Anfangszeit minius 24 Stunden? also (1-C23)
M
Mr. Snoot
Gast
Der Fehler liegt jetzt in deiner frühen Arbeitsendzeit!
Wie sagen Excel ja, eine Endzeit kleiner 12:00 bedeutet, dass es 12:00 am Montag wäre. Das stimmt aber in deinem Fall nicht. Genau das ist ja das große Problem der Formel.
Wenn du die letzten beiden Wenn-Funktionen vertauscht (siehe mein vorheriger Post), dann klappt es.
Denn jetzt wird zuerst überprüft ob die Endzeit größer als die Anfangszeit ist. Dadurch ist es egal, wie früh die Endzeit ist. Erst wenn das nicht zutrifft, wird überprüft ob die Endzeit kleiner 12:00 ist.
Wie sagen Excel ja, eine Endzeit kleiner 12:00 bedeutet, dass es 12:00 am Montag wäre. Das stimmt aber in deinem Fall nicht. Genau das ist ja das große Problem der Formel.
Wenn du die letzten beiden Wenn-Funktionen vertauscht (siehe mein vorheriger Post), dann klappt es.
Denn jetzt wird zuerst überprüft ob die Endzeit größer als die Anfangszeit ist. Dadurch ist es egal, wie früh die Endzeit ist. Erst wenn das nicht zutrifft, wird überprüft ob die Endzeit kleiner 12:00 ist.
M
Mr. Snoot
Gast
Okay, das mit den 12:00 Stunden bei gar keiner Arbeit liegt hieran:
Wenn der Wochentag ein Sonntag ist und D15 kleiner 0,5 dann rechne (1-C15)*0,5.
Wenn die Zelle leer bleibt (keine Arbeit), dann ist der Wert der Zelle 0, und das ist ja kleiner als 0,5. Also wird gerechnet wenn der Tag Sonntag und die Zeit leer ist. Um das zu verhindern muss man also noch sagen, dass die Zelle nicht leer sein darf (rote Markierung):
Jetzt muss der Tag also Sonntag sein, die Zeit muss vor 12:00 Uhr sein und (C15 und D15) dürfen nicht 0 (bzw. leer) sein. Sicherheitshalber, kann man ähnliches bei den anderen Berechnungen auch einfügen (grüne Markierung).
Aber sonst sollte die Formel eigentlich korrekt rechnen.
Außerdem solltest du wie gesagt aufpassen, dass die Zellen mit Uhrzeit identisch formatiert sind (Rechtsklick | Zelle formatieren). Bei einigen hast du ein Format mit Sekunden (h:mm:ss) bei anderen ohne Sekunden (h:mm). Das Problem hatten wir ja schon, dass dann irgendwo Sekunden von Minuten oder Minuten von Stunden abgezogen wurden.
Ich habe das in der angehängten Datei in der spalte H mal wieder in h:mm formatiert. Außer, du brauchst tatsächlich irgendwo Sekunden.
=WENN(UND(WOCHENTAG(B15)=7;D15<0,5);D15*0,5;WENN(UND(WOCHENTAG(B15)=1);(D15-C15)*0,5;WENN(UND(WOCHENTAG(B15)=1;D15<0,5);(1-C15)*0,5;"")))
Wenn der Wochentag ein Sonntag ist und D15 kleiner 0,5 dann rechne (1-C15)*0,5.
Wenn die Zelle leer bleibt (keine Arbeit), dann ist der Wert der Zelle 0, und das ist ja kleiner als 0,5. Also wird gerechnet wenn der Tag Sonntag und die Zeit leer ist. Um das zu verhindern muss man also noch sagen, dass die Zelle nicht leer sein darf (rote Markierung):
=WENN(UND(WOCHENTAG(B15)=7;D15<0,5;C15<>0;D15<>0);D15*0,5;WENN(UND(WOCHENTAG(B15)=1;C15<>0;D15<>0);(D15-C15)*0,5;WENN(UND(WOCHENTAG(B15)=1;D15<0,5;C15<>0;D15<>0);(1-C15)*0,5;"")))
Jetzt muss der Tag also Sonntag sein, die Zeit muss vor 12:00 Uhr sein und (C15 und D15) dürfen nicht 0 (bzw. leer) sein. Sicherheitshalber, kann man ähnliches bei den anderen Berechnungen auch einfügen (grüne Markierung).
Aber sonst sollte die Formel eigentlich korrekt rechnen.
Außerdem solltest du wie gesagt aufpassen, dass die Zellen mit Uhrzeit identisch formatiert sind (Rechtsklick | Zelle formatieren). Bei einigen hast du ein Format mit Sekunden (h:mm:ss) bei anderen ohne Sekunden (h:mm). Das Problem hatten wir ja schon, dass dann irgendwo Sekunden von Minuten oder Minuten von Stunden abgezogen wurden.
Ich habe das in der angehängten Datei in der spalte H mal wieder in h:mm formatiert. Außer, du brauchst tatsächlich irgendwo Sekunden.
Zuletzt bearbeitet:
hab noch ne Frage, ist dies (C15="";D15="")
das selbe wie C15<>0;D15<>0 ???
heißt das alles, wenn C15 null bzw. leer ist
wenn D15 null bzw. leer ist
__________________________________________________________________________________
=0,15*(WENN(ODER(C15="";D15="");0;(WENN(UND(C15<$G$11;C15>$G$12);0;WENN(C15>$G$11;C15-$G$11;WENN(C15<$G$12;$G$12-C15)))+WENN(UND(D15<$G$11;D15>$G$12);0;WENN(D15>$G$11;D15-$G$11;WENN(D15<$G$12;1-$G$11+D15;0))))))
und wieso fängt die Formel mit 15% an? ich dachte eine Formel beginnt immer mit =. und hätte man die 15% auch hinten dran hängen können, wäre das dass gleiche gewesen?
das selbe wie C15<>0;D15<>0 ???
heißt das alles, wenn C15 null bzw. leer ist
wenn D15 null bzw. leer ist
__________________________________________________________________________________
=0,15*(WENN(ODER(C15="";D15="");0;(WENN(UND(C15<$G$11;C15>$G$12);0;WENN(C15>$G$11;C15-$G$11;WENN(C15<$G$12;$G$12-C15)))+WENN(UND(D15<$G$11;D15>$G$12);0;WENN(D15>$G$11;D15-$G$11;WENN(D15<$G$12;1-$G$11+D15;0))))))
und wieso fängt die Formel mit 15% an? ich dachte eine Formel beginnt immer mit =. und hätte man die 15% auch hinten dran hängen können, wäre das dass gleiche gewesen?
ich hab bei der Nachtzuschlagsformel versucht den Sonntag mit rein zu baquen, dass sie am Sonntag keinen Nachtzuschlag berechnet und wenn man in den Sonntag hinein arbeit also ab 0Uhr Sonntag kein Nachtzuschlag berechnet wird..........ich kapiers einfach nicht, ich bekomme es nicht gebacken.
M
Mr. Snoot
Gast
Sicher? Möglicherweise stimmt das Ergebnis nicht, wenn du um 0:00 anfängst oder aufhörst. Teste das mal an deiner aktuellen Tabelle.Sannel schrieb:
Nein (ob 0 oder "" sei jetzt mal dahingestellt):
C15="" bedeutet C15 ist gleich leer
C15<>"" bedeutet C15 ungleich leer (also genau das Gegenteil)
C15<>"" bedeutet C15 ungleich leer (also genau das Gegenteil)
Die Formel fängt auch nach wie vor mit = an. Wo das 0,15 dann steht ist egal. Ist ja nur eine einfache Multiplikation. =0,15*FORMEL ist also das gleiche wie =FORMEL*0,15
Da kommen wir glaube ich an einen Punkt, wo man das alleine mit einer Formel nicht mehr berechnen kann.Sannel schrieb:
Problem: Excel kann maximal 7 Wenn-Funktionen verschachteln. In der jetzigen Formel haben wir aber genau diese 7 Abfragen schon erreicht. Für die Nacht-/Sonntagszulage müssten noch ein paar mehr dazu.
Excel 2007 kann mit mehr als 7 verschachtelten Wenn-Funktionen arbeiten, allerdings wird es generell langsam unübersichtlich, so dass ich mir was anderes überlegt habe.
Anstatt einer langen Formel nimmt man mehrere kleine Formeln, die man erst am Ende zusammenfügt. Diese kleinen Formeln musst du irgendwo außerhalb deiner Zeittabelle (z.B. rechts daneben) platzieren. Na ok, zugegebenermaßen braucht man auch die lange Formal weiterhin, aber daran muss nichts mehr geändert werden (
).Dabei wird folgendes überprüft:
Wird überhaupt Nachtschicht gearbeitet?
Wird von Samstag auf Sonntag gearbeitet?
Wird nur Sonntags gearbeitet?
Wird von Sonntag auf Montag gearbeitet?
Wird von Samstag auf Sonntag gearbeitet?
Wird nur Sonntags gearbeitet?
Wird von Sonntag auf Montag gearbeitet?
Findet bspw. keine Nachtschicht statt, dann muss gar nicht gerechnet werden. Wird nur Sonntags gearbeitet und Nachtschicht findet statt, kann man die Zeit einfach mit 0,5 multiplizieren. Findet Nachtschicht an einem Tag außer Sonntag statt, wird diese Zeit einfach mit 0,15 multipliziert.
Weil ich jetzt nicht genau wusste, was du rechnen willst, hab ich mal folgende Sachen gemacht:
- Nachtzeit außerhalb Sonntag (daraus Nachtzulage außerhalb Sonntag) + Nachtzeit nur Sonntag (daraus Nachtzulage nur Sonntag) = Gesamtnachtzulage (Spalte AE)
- Nachtzeit außerhalb Sonntag (daraus Nachtzulage außerhalb Sonntag) + Arbeitszeit nur Sonntag (daraus Sonntagszulage) = Gesamtzulage (Spalte AH)
Kann auch sein, dass das ganze viel einfacher geht, und ich mittlerweile nur den Wald vor lauter Bäumen nicht mehr sehe, aber was besseres fällt mir zumindest jetzt nicht ein
Kannst ja mal schauen, ob du damit zurecht kommst. Die Zusatzspalten sind rechts neben der Tabelle, Spalte S bis AH. Ist eigentlich alles beschriftet. Du brauchst dann wohl entweder die drei Spalten AC, AD, AE (Nachtzulagen außerhalb Sonntag + Nachtzulage Sonntag) oder die drei Spalten AF, AG, AH (Nachtzulagen außerhalb Sonntag + Sonntagszulage).
Von dieser Extra-Tabelle musst du nur die jeweiligen Ergebniss mit deiner eigentlichen Tabelle verknüpfen.
Ich weiß, es sieht wohl alles sehr viel komplizierter aus als vorher, aber ich denke ohne VBA-Programmierung geht es nicht leichter. Natürlich könnte man einzelne Teilformeln der Extratabelle noch zusammenfassen, aber das ist ja im Grunde egal. Wenn dich die Extraspalten stören, kannst du deren Breite ja auf 0 setzen.
oh mein Gott... ich schau es mir lieber morgen genau an...jetzt bin ich zu übermüdet...und wenn ich mir manche Fragen von mir ansehe, dann war ich gestern auch ziemlich übermüdet, also versuche ich es heute gar nicht erst....
Aber was ich weiß, ist dass die Formel, über die ich vorher sprach, doch einen Fehler hat:
wenn ich am Sonntag arbeite aber das Ende bei Null oder nach Null liegt ist das Ergebnis wieder ####
Aber was ich weiß, ist dass die Formel, über die ich vorher sprach, doch einen Fehler hat:
wenn ich am Sonntag arbeite aber das Ende bei Null oder nach Null liegt ist das Ergebnis wieder ####
M
Mr. Snoot
Gast
Sollte bei der neuen Tabelle eigentlich funktionieren.
Oder vielleicht besser gar nicht
Noch mal was Grundlegendes.
Ich habe eine Übersicht erstellt (siehe angehängte Datei: Anhang anzeigen arbeitszeiten.zip) damit man sieht, wie Arbeitsbeginn und Arbeitsende liegen können. Also z. B. Arbeitsbeginn zwischen 5:00 und 22:00 und Arbeitsende zwischen 22:00 und 0:00.
So kann man sich relativ leicht die ganzen Wenn-Funktionen und die Berechnung zur Ermittlung der Nachtzeit zusammenbasteln.
Wie man aber auch sieht, sind einige Fälle nicht berücksichtigt. Weil mehr als die bisherigen 7 Fälle ja nicht möglich sind (Excel 2007 mal ausgenommen).
Überleg dir mal, ob diese Zeiten überhaupt korrekt sind bzw. so bei dir in Frage kommen können. Vielleicht fängst du ja nie nach 22:00 an, dann würde man die letzten drei Fälle 5-7 gar nicht benötigen, und die Formel würde etwas kürzer.
Evtl. kann es dafür aber sein, dass du mal vor 5:00 anfängst, dann müsste man das auch irgendwie einbauen, denn diese Möglichkeit wird im Moment ja gar nicht berücksichtigt.
Wenn du aber sowohl vor 5:00, als auch nach 22:00 anfangen kannst, dann wirds problematisch
Außerdem hab ich noch festgestellt, dass die Formel nicht richtig rechnet, wenn eine Zeit genau auf 22:00 oder 5:00 liegt.
Dazu muss überall wo es in der großen Formel <$G$11 bzw. <$G$12 bzw. >$G$12 bzw. >$G$11 heißt <=... bzw. >=... heißen.
In der neuen Tabelle, spalte W also:
=WENN(UND(C15>=$G$12;C15<=$G$11;D15>C15;D15<=$G$11);0;WENN(UND(C15>=$G$12;C15<=$G$11;D15>=$G$11);D15-$G$11;WENN(UND(C15>=$G$12;C15<=$G$11;D15<C15;D15<=$G$12);1-$G$11+D15;WENN(UND(C15>=$G$12;C15<=$G$11;D15<C15;D15>=$G$12);1-$G$11+$G$12;WENN(UND(C15>=$G$11;C15<D15);D15-C15;WENN(UND(C15>=$G$11;D15<=$G$12);1-C15+D15;WENN(UND(C15>=$G$11;D15>=$G$12;D15<=$G$11);1-C15+$G$12;0)))))))
Zuletzt bearbeitet:
oh mein Gott... ich schau es mir lieber später genau an...jetzt bin ich zu übermüdet...und wenn ich mir manche Fragen von mir ansehe, dann war ich vorgestern auch ziemlich übermüdet, also versuche ich es heute gar nicht erst....
Aber was ich weiß, ist dass die Formel, über die ich vorher sprach, doch einen Fehler hat:
wenn ich am Sonntag arbeite aber das Ende bei Null oder nach Null liegt ist das Ergebnis wieder ####
Aber was ich weiß, ist dass die Formel, über die ich vorher sprach, doch einen Fehler hat:
wenn ich am Sonntag arbeite aber das Ende bei Null oder nach Null liegt ist das Ergebnis wieder ####
M
Mr. Snoot
Gast
So, jetzt habe ich eine gute Nachricht und eine .. naja sagen wir und eine Nachricht
Du gute Nachricht: ich habe eine neue Formel für die Berechnung der Nachtstunden gefunden. Damit braucht man also nicht mehr dieses Ungetüm mit 7 Wenn-Funktionen, die neue Formel ist auch nur halb so lang.
Die andere Nachricht: das ändert aber eigentlich nichts daran, dass man diese Zusatztabelle "braucht" um die Nacht- und Sonntagszulage zu berechnen. Braucht deshalb in "..", da man sie jetzt eigentlich weglassen könnte. Aber da diese Formel insgesamt so lang würde, dass man beim besten Willen nicht mehr weiß, wo vorne und hinten ist, denke ich, man sollte die Zusatztabelle zum Aufdröseln von Nachtschicht und Sonntagsarbeit weiterhin benutzen. Ich hab sie in der neuen Datei auch mal noch weiter minimiert.
Ist vielleicht nicht schön, aber wenn man da mal was ändern muss weiß man sofort, wo und vor allem was man ändern muss. In einer einzigen, kilometer langen Formel müsste man erst eine Stunde suchen. Ich habs nur mal so probiert, alles in eine Formel zu packen, aber nachdem der halbe Bildschirm voll, und die Formel noch lange nicht fertig war, hab ichs gelassen
Da du, denke ich, Nachtzulage (ohne Sonntagsnacht) und die Gesamt-Sonntagszulage willst, habe ich die Tabelle entsprechend angepasst. Falls du die aber die einzelne Sonntagsnacht auch brauchst, kann man das schnell noch ändern.
Du gute Nachricht: ich habe eine neue Formel für die Berechnung der Nachtstunden gefunden. Damit braucht man also nicht mehr dieses Ungetüm mit 7 Wenn-Funktionen, die neue Formel ist auch nur halb so lang.
Die andere Nachricht: das ändert aber eigentlich nichts daran, dass man diese Zusatztabelle "braucht" um die Nacht- und Sonntagszulage zu berechnen. Braucht deshalb in "..", da man sie jetzt eigentlich weglassen könnte. Aber da diese Formel insgesamt so lang würde, dass man beim besten Willen nicht mehr weiß, wo vorne und hinten ist, denke ich, man sollte die Zusatztabelle zum Aufdröseln von Nachtschicht und Sonntagsarbeit weiterhin benutzen. Ich hab sie in der neuen Datei auch mal noch weiter minimiert.
Ist vielleicht nicht schön, aber wenn man da mal was ändern muss weiß man sofort, wo und vor allem was man ändern muss. In einer einzigen, kilometer langen Formel müsste man erst eine Stunde suchen. Ich habs nur mal so probiert, alles in eine Formel zu packen, aber nachdem der halbe Bildschirm voll, und die Formel noch lange nicht fertig war, hab ichs gelassen
Da du, denke ich, Nachtzulage (ohne Sonntagsnacht) und die Gesamt-Sonntagszulage willst, habe ich die Tabelle entsprechend angepasst. Falls du die aber die einzelne Sonntagsnacht auch brauchst, kann man das schnell noch ändern.
Zuletzt bearbeitet:
so jetzt ist es soweit ich weis gar nicht mehr wo von wir reden 
also welche Formel funktioniert jetzt und welche geht noch nicht???????
Bzw. was für eine neue oder bearbeitete formel muss ich in G15 (Nacht) und H15 (Sonntag) eintragen.
und bei dem excel mit der zusatztabelle funktioniert die auch ohne die Tabelle, also dient es nur der aufschlüsselung bzw. übersicht? oder sind die verknüpft?
also welche Formel funktioniert jetzt und welche geht noch nicht???????
Bzw. was für eine neue oder bearbeitete formel muss ich in G15 (Nacht) und H15 (Sonntag) eintragen.
und bei dem excel mit der zusatztabelle funktioniert die auch ohne die Tabelle, also dient es nur der aufschlüsselung bzw. übersicht? oder sind die verknüpft?
M
Mr. Snoot
Gast
Also, die Zusatztabelle brauchst du immer - wie gesagt, wenn sie dich stört, stell die Spaltenbreite auf 0 oder schiebe sie nach rechts. Theoretisch kannst du die Zusatztabelle (die du in jedem Monat brauchst) auch auf ein eigenes Tabellenblatt machen.
In Spalte X ist die Nachtzulage, in Spalte Z die Sonntagszulage.
Also musst du in G15 nur =X15 eintragen und in H15 -> =Z15.
Im Grunde funktionieren beide. Sowohl die lange mit den 7 Wenn-Funktionen, als auch die neue, die du in Spalte U findest. Die neue ist aber kürzer und rechnet unabängig von Anfangs- und Endzeit korrekt. Bei der alten langen Formel, ist das manchnmal nicht Fall.
Zuletzt bearbeitet:
Ähnliche Themen
- DieRenteEnte
- Office und Text