(Excel 2008) Überstunden berechnen
- Ersteller Jogi193
- Erstellt am
M
Mr. Snoot
Gast
Okay, jetzt wäre es noch hilfreich zu wissen, was nicht geht (in welcher Zelle usw.) bzw. wo das Problem ist 
Bitte genau erklären was du berechnen willst.
Bitte genau erklären was du berechnen willst.
Überstunden bzw. Nachtstunden zählen von 22:00-05:00 Uhr
Ich habe eigentlich schon eine Formel, doch die funktioniert nur halb.
Also wenn ich bis 23:00Uhr arbeite, dann rechnet mir Excel die genauen Nachtstunden aus auch wenn ich bis 23:45Uhr zb. arbeite aber sobald die Endzeit meiner Arbeit später als 00:00 Uhr oder genau 00:00 Uhr ist, dann rechnet er 0 Stunden aus.
Das liegt wahrscheinlich daran, dass er ab 00:00 einen neuen Tag zählt oder dass logischerweise die Endzeit, wenn sie 00:00 oder später ist nicht mehr größer (also die Zahl 00) als 22:00 Uhr wie in der Formel angegeben ist.
Da es in der Formel heißt: wenn größer als 22:00Uhr
Ich muss wahrscheinlich die Formel erweitern, weis nur nicht wie.
Meine Formel: =WENN(C15<$G$12;($G$12-C15);0)+WENN(D15>$G$11;($G$11-D15)*-1;0)
Stammdaten: C15= Anfangszeit
G12= 05:00 Uhr
D15= Endzeit
G11= 22:00Uhr
Wie müsste meine Formel lauten??????
Ich habe eigentlich schon eine Formel, doch die funktioniert nur halb.
Also wenn ich bis 23:00Uhr arbeite, dann rechnet mir Excel die genauen Nachtstunden aus auch wenn ich bis 23:45Uhr zb. arbeite aber sobald die Endzeit meiner Arbeit später als 00:00 Uhr oder genau 00:00 Uhr ist, dann rechnet er 0 Stunden aus.
Das liegt wahrscheinlich daran, dass er ab 00:00 einen neuen Tag zählt oder dass logischerweise die Endzeit, wenn sie 00:00 oder später ist nicht mehr größer (also die Zahl 00) als 22:00 Uhr wie in der Formel angegeben ist.
Da es in der Formel heißt: wenn größer als 22:00Uhr
Ich muss wahrscheinlich die Formel erweitern, weis nur nicht wie.
Meine Formel: =WENN(C15<$G$12;($G$12-C15);0)+WENN(D15>$G$11;($G$11-D15)*-1;0)
Stammdaten: C15= Anfangszeit
G12= 05:00 Uhr
D15= Endzeit
G11= 22:00Uhr
Wie müsste meine Formel lauten??????
M
Mr. Snoot
Gast
Also wenns nur um spalte G geht, ja
Es geht ja nur darum zu prüfen, wieviel Stunden zwischen 22:00 und 5:00 Uhr liegen, korrekt?
Sollte eigentlich nicht zu schwer sein - wenns nicht schon so spät wäre
Hab mal was gebastelt, evtl. klappt das. Kannst ja mal mit paar Werten probieren, ob das Ergebnis stimmt. Wenn nicht - einfach nochmal melden. Ich schau mir das dann an. Aber heut nicht mehr 
Es geht ja nur darum zu prüfen, wieviel Stunden zwischen 22:00 und 5:00 Uhr liegen, korrekt?
Sollte eigentlich nicht zu schwer sein - wenns nicht schon so spät wäre
Hab mal was gebastelt, evtl. klappt das. Kannst ja mal mit paar Werten probieren, ob das Ergebnis stimmt. Wenn nicht - einfach nochmal melden. Ich schau mir das dann an. Aber heut nicht mehr
M
Mr. Snoot
Gast
Frag mich nich, wie ich das noch geschafft hab - ich kanns ja jetzt kaum nachvollziehen
Die Frage ist nur, ob du auch mal über 5:00 Uhr raus arbeiten kannst!? Dann stimmt die Berechnung nämlich nicht mehr. Dann müsste man aber wohl mit Datum rechnen, denn wenn denn du von 23:30 - 8:00 Uhr arbeitest, wären das ja 6:30 Nachtzuschlag. Von 6:00 bis 8:00 Uhr aber 0:00 Stunden Nachtzuschlag.
Die Frage ist nur, ob du auch mal über 5:00 Uhr raus arbeiten kannst!? Dann stimmt die Berechnung nämlich nicht mehr. Dann müsste man aber wohl mit Datum rechnen, denn wenn denn du von 23:30 - 8:00 Uhr arbeitest, wären das ja 6:30 Nachtzuschlag. Von 6:00 bis 8:00 Uhr aber 0:00 Stunden Nachtzuschlag.
Also wenn ich von 23:30-6:00 mal arbeiten würde wie du sagst, dann wären dass aber keine 6,5 stunden sondern nur 5,5std. Nachtzulage,der Nachzuschlag geht immer nur bis 5uhr, ab 5Uhr gibts nichts mehr. Deine Formel müsste deswegen stimmen.
Ich habe noch ne Frage, wie kann ich es schaffen, dass wenn ich z.B frei habe, also keine Dienstzeiten eingetragen werden, dass er mir dann auch keine Nachzuschläge ausrechnet und von mir aus null reinschreibt. Weil wenn ich nichts eintrage schreibt er mir von haus aus 7 sdt. rein....
Ich habe noch ne Frage, wie kann ich es schaffen, dass wenn ich z.B frei habe, also keine Dienstzeiten eingetragen werden, dass er mir dann auch keine Nachzuschläge ausrechnet und von mir aus null reinschreibt. Weil wenn ich nichts eintrage schreibt er mir von haus aus 7 sdt. rein....
M
Mr. Snoot
Gast
Ja, da hab mich verrechnet. Das Problem ergäbe sich halt, wenn du länger als 5:00 arbeitest. Wenn das aber keinesfalls vorkommet, müsste man sich ja keine Gedanken machen.
Damit nur ein Ergebnis angezeigt wird , wenn bei der Anfangs- und Endzeit ein Wert eingetragen ist, musst du das einfach mit einer WENN-Abfrage testen:
Das wäre die Formel in G15. Alles, was in der Formel neu ist, ist rot markiert.
Damit nur ein Ergebnis angezeigt wird , wenn bei der Anfangs- und Endzeit ein Wert eingetragen ist, musst du das einfach mit einer WENN-Abfrage testen:
=WENN(ODER(C15="";D15="");"";(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)))))
Das wäre die Formel in G15. Alles, was in der Formel neu ist, ist rot markiert.
Zuletzt bearbeitet:
M
Mr. Snoot
Gast
Dann müsstest du einfach 0 statt nichts angeben:
vorher leer: =WENN(ODER(C15="";D15="");"";(WENN ...
mit 0:00:00: =WENN(ODER(C15="";D15="");0;(WENN ...
Woher ich das kann? Jahre langes, hartes Training - ne, Scherz beiseite
Im Grunde hattest du die Formel ja selbst. Das Problem war ja weniger irgendeine Formel zu können, sondern nur irgendwo die Zeiten korrekt zusammenzurechnen. Man muss halt den Überblick behalten, weils bei so vielen, verschachtelten Sachen unübersichtlich wird. Aber mehr als WENN ist in der Formel ja nicht drin.
vorher leer: =WENN(ODER(C15="";D15="");"";(WENN ...
mit 0:00:00: =WENN(ODER(C15="";D15="");0;(WENN ...
Woher ich das kann? Jahre langes, hartes Training - ne, Scherz beiseite
Im Grunde hattest du die Formel ja selbst. Das Problem war ja weniger irgendeine Formel zu können, sondern nur irgendwo die Zeiten korrekt zusammenzurechnen. Man muss halt den Überblick behalten, weils bei so vielen, verschachtelten Sachen unübersichtlich wird. Aber mehr als WENN ist in der Formel ja nicht drin.
das Problem ist bei mir, dass ich ja gar nicht weis für was welches Zeichen steht usw. als die Grundlagen fehlen mir eigentlich, einige Formeln verstehe ich oder sind für mich teilweise logisch aber die ganzen zusätzlichen Zeichen usw. sind das Problem.
Sagmal woran liegt es,dass ich die Formel nicht kopieren kann und z.B in November wieder einfügen kann, also kopieren und einfügen geht aber das Ergebnis lautet wieder mal #####.
Obwohl November genauso aufgebaut ist wie Dezember was ich dir schickte
Sagmal woran liegt es,dass ich die Formel nicht kopieren kann und z.B in November wieder einfügen kann, also kopieren und einfügen geht aber das Ergebnis lautet wieder mal #####.
Obwohl November genauso aufgebaut ist wie Dezember was ich dir schickte
M
Mr. Snoot
Gast
Wenn man bspw. Text als Ausgabe der WENN-Bedingung haben möchte (wenn Zahl x größer als Zahl y dann schreibe den Text abc), dann muss dieser in Anführungszeichen stehen: WENN(x>y;"abc").
Wenn zwischen den "" nichts steht heißt das einfach, dass die Zelle eben leer ist: WENN(C15="";"") bedeutet also wenn C15 leer ist, dann schreibe nichts in die Zelle.
Die $-Zeichen verhindern, dass sich der Zellbezug verändert. Die Zellen in denen 22:00 und 5:00 steht, sind ja für jede Zeile dieselben. Wenn du jetzt in Zelle G15 deine Formel schreibst =WENN(C15>G11;...) und du kopierst die Formel in G16, G17, G18 usw. dann würde sich die Formel auch immer ändern:
=WENN(C15>G11;...)
=WENN(C16>G12;...)
=WENN(C17>G13;...)
=WENN(C18>G14;...)
...
..
.
Damit das nicht passiert, tackert man G11 mit $-Zeichen fest => $G$11, dann wird beim kopieren G11 nicht verändert und bleibt in jeder Zelle erhalten. Benötigt würde theoretisch nur das $-Zeichen vor der 11, da G in diesem Fall ja gleich bleibt. Würdest du die Zelle aber irgendwann nach rechts kopieren, würde aus G ein H -> I -> J -> K ...
Mit G$11 bleibt also die 11 fest, mit $G11 nur das G und mit $G$11 beides.
------------------------------------------------------
Was jetzt das Problem bei dir ist, lässt sich wieder nur schwer sagen. Wenn du sagst, alles ist identisch, dann sollte es eigentlich mit genau der gleichen Formel klappen. Stimmen denn die ganzen Zellbezüge noch überein? Ich vermute, dass da irgendwo was nicht stimmt - entweder ist es beim Kopieren passiert, oder die Zellen in der Dezemberliste sind nicht 1:1 die gleichen wie in der Novemberliste.
Wenn zwischen den "" nichts steht heißt das einfach, dass die Zelle eben leer ist: WENN(C15="";"") bedeutet also wenn C15 leer ist, dann schreibe nichts in die Zelle.
Die $-Zeichen verhindern, dass sich der Zellbezug verändert. Die Zellen in denen 22:00 und 5:00 steht, sind ja für jede Zeile dieselben. Wenn du jetzt in Zelle G15 deine Formel schreibst =WENN(C15>G11;...) und du kopierst die Formel in G16, G17, G18 usw. dann würde sich die Formel auch immer ändern:
=WENN(C15>G11;...)
=WENN(C16>G12;...)
=WENN(C17>G13;...)
=WENN(C18>G14;...)
...
..
.
Damit das nicht passiert, tackert man G11 mit $-Zeichen fest => $G$11, dann wird beim kopieren G11 nicht verändert und bleibt in jeder Zelle erhalten. Benötigt würde theoretisch nur das $-Zeichen vor der 11, da G in diesem Fall ja gleich bleibt. Würdest du die Zelle aber irgendwann nach rechts kopieren, würde aus G ein H -> I -> J -> K ...
Mit G$11 bleibt also die 11 fest, mit $G11 nur das G und mit $G$11 beides.
------------------------------------------------------
Was jetzt das Problem bei dir ist, lässt sich wieder nur schwer sagen. Wenn du sagst, alles ist identisch, dann sollte es eigentlich mit genau der gleichen Formel klappen. Stimmen denn die ganzen Zellbezüge noch überein? Ich vermute, dass da irgendwo was nicht stimmt - entweder ist es beim Kopieren passiert, oder die Zellen in der Dezemberliste sind nicht 1:1 die gleichen wie in der Novemberliste.
und wozu benötigt man die Klammern bzw. wie weis ich wann ich immer ne Klammer brauche...einige Teile mancher Formel stehen nicht in Klammern.
Wo kommt ein - dazwischen oder ein ; oder 1 und 0 usw.
________________________________________________________________________
Nein die sind komplett identisch, deswegen verstehe ich es ja nicht
zb. C15 steht an der selben Stelle für das Selbe usw. das Format ist auch gleich eingestellt usw.
________________________________________________________________________
Hälst du dich oft hier auf?
Wo kommt ein - dazwischen oder ein ; oder 1 und 0 usw.
________________________________________________________________________
Nein die sind komplett identisch, deswegen verstehe ich es ja nicht
zb. C15 steht an der selben Stelle für das Selbe usw. das Format ist auch gleich eingestellt usw.
________________________________________________________________________
Hälst du dich oft hier auf?
M
Mr. Snoot
Gast
Evtl. kannst du die Datei nochmal hier hochladen - ist wie gesagt immer schwer, das blind zu sagen.
--------------------------------
Klammern kommen immer nach einem Befehl: =WENN(.....) oder =UND(....) und in den Klammern stehen dann die Anweisungen, Bedingungen usw. diese wiederum werden durch ; getrennt. Zum Beispiel ist eine Wenn-Funktion in drei Teile unterteilt:
wenn( Anweisung ; wenn die Anweisung zutrifft dann tue irgendwas ; sonst tue etwas anderes)
Bsp.: Wenn Zelle A1 größer als B1 dann schreibe 27 sonst schreibe falsch.
=WENN(A1>B1;27;"falsch")
Eine UND-Funktion kann mehrere Bedingungen habe:
wenn in Zelle A1, B1, C1, D1, E1 und F1 etwas steht (also wenn die Zellen nicht leer sind) schreibe nicht leer sonst schreibe mindestens eine Zelle ist leer.
=WENN(UND(A1<>"";B1<>"";C1<>"";D1<>"";E1<>"";F1<>"");"nicht leer";"mindestens eine Zelle ist leer")
wenn (a1 nicht leer und B1 nicht leer und C1 nicht leer und D1 nicht leer und E1 nicht leer und F1 nicht leer; dann schreibe nicht leer; sonst schreibe ...)
<> bedeutet ungleich, also <>"" meint ungleich leer.
--------------------------------
Die 0 und 1 sind einfach zum rechnen. 0 ist der Dezimalwert von 0:00 Uhr und 1 der von 24:00 Uhr (da Excel intern nicht mit Uhrtzeiten rechnet sondern mit deren Dezimalwerten).
Und das - war halt irgendwo ein Minus
--------------------------------
Klammern kommen immer nach einem Befehl: =WENN(.....) oder =UND(....) und in den Klammern stehen dann die Anweisungen, Bedingungen usw. diese wiederum werden durch ; getrennt. Zum Beispiel ist eine Wenn-Funktion in drei Teile unterteilt:
wenn( Anweisung ; wenn die Anweisung zutrifft dann tue irgendwas ; sonst tue etwas anderes)
Bsp.: Wenn Zelle A1 größer als B1 dann schreibe 27 sonst schreibe falsch.
=WENN(A1>B1;27;"falsch")
Eine UND-Funktion kann mehrere Bedingungen habe:
wenn in Zelle A1, B1, C1, D1, E1 und F1 etwas steht (also wenn die Zellen nicht leer sind) schreibe nicht leer sonst schreibe mindestens eine Zelle ist leer.
=WENN(UND(A1<>"";B1<>"";C1<>"";D1<>"";E1<>"";F1<>"");"nicht leer";"mindestens eine Zelle ist leer")
wenn (a1 nicht leer und B1 nicht leer und C1 nicht leer und D1 nicht leer und E1 nicht leer und F1 nicht leer; dann schreibe nicht leer; sonst schreibe ...)
<> bedeutet ungleich, also <>"" meint ungleich leer.
--------------------------------
Die 0 und 1 sind einfach zum rechnen. 0 ist der Dezimalwert von 0:00 Uhr und 1 der von 24:00 Uhr (da Excel intern nicht mit Uhrtzeiten rechnet sondern mit deren Dezimalwerten).
Und das - war halt irgendwo ein Minus
Naja, ab und zu
Zuletzt bearbeitet:
M
Mr. Snoot
Gast
Der Fehler kommt, weil das Ergebnis negativ ist (Standardmäßig kann Excel nicht mit negativen Uhrzeiten rechnen - falls du das brauchst, kann man es aber einstellen).
Das sieht man sofort, wenn man das Zellenformat in G15 nicht auf Uhrzeit sondern Standard oder Zahl stellt. Genau nachvollziehen, wieso etwas negatives rauskommt konnte ich nicht.
Vermutlich wurde eine der vier Zeiten aber nicht als hh:mm erkannt sondern als mm:ss, es werden also von ein paar Minuten mehrere Stunden abgezogen.
Lösch am besten mal die von und bis sowie Nachtschichtzeiten, formatiere die Zellen wieder als Uhrzeit (oder Benutzerdefiniert als hh:mm) und trag dann die Zeiten wieder ein, dann sollte es klappen.
Da du wohl keine Sekunden haben wirst, nimm am besten dieses Format:
In einigen deiner Zellen ist nämlich dieses Format eingestellt:
Sollte eigentlich das (fast) gleiche sein, aber mach es am besten immer gleich.
Das sieht man sofort, wenn man das Zellenformat in G15 nicht auf Uhrzeit sondern Standard oder Zahl stellt. Genau nachvollziehen, wieso etwas negatives rauskommt konnte ich nicht.
Vermutlich wurde eine der vier Zeiten aber nicht als hh:mm erkannt sondern als mm:ss, es werden also von ein paar Minuten mehrere Stunden abgezogen.
Lösch am besten mal die von und bis sowie Nachtschichtzeiten, formatiere die Zellen wieder als Uhrzeit (oder Benutzerdefiniert als hh:mm) und trag dann die Zeiten wieder ein, dann sollte es klappen.
Da du wohl keine Sekunden haben wirst, nimm am besten dieses Format:
In einigen deiner Zellen ist nämlich dieses Format eingestellt:
Sollte eigentlich das (fast) gleiche sein, aber mach es am besten immer gleich.
Zuletzt bearbeitet:
funktioniert mal wieder
hab es mal gerade ausprobiert wenn ende später als 5 wäre, warum geht dies dann mit der formel nicht? müsste da was umgebaut oder dazu gebaut werden? die formel ist mir zu kompliziert liegt wahrscheinlich auch daran, dass sie so lang ist
hab es mal gerade ausprobiert wenn ende später als 5 wäre, warum geht dies dann mit der formel nicht? müsste da was umgebaut oder dazu gebaut werden? die formel ist mir zu kompliziert liegt wahrscheinlich auch daran, dass sie so lang ist
M
Mr. Snoot
Gast
Die Endzeit überprüft im Moment nur ob sie größer als 22:00 Uhr oder kleiner als 5:00 ist, also irgendwo dazwischen liegt.
Das Problem, falls die größer als 5:00 ist war das, was ich oben schon mal erwähnt hatte:
23:30 - 8:00 = 5:30 Nachtzeit
06:00 - 8:00 = 0:00 Nachtzeit
Theoretisch müsste man also dann wieder prüfen, was die Anfangszeit ist. Ließe sich natürlich machen - aber wenn's nicht sein muss .. macht mans halt erstmal nicht
Das Problem, falls die größer als 5:00 ist war das, was ich oben schon mal erwähnt hatte:
23:30 - 8:00 = 5:30 Nachtzeit
06:00 - 8:00 = 0:00 Nachtzeit
Theoretisch müsste man also dann wieder prüfen, was die Anfangszeit ist. Ließe sich natürlich machen - aber wenn's nicht sein muss .. macht mans halt erstmal nicht
kann man die Formel noch erweitern?
also ich bekomme ja nicht die gesamten nachtstunden gutgeschrieben sondern pro nachtstunde nur 15% was glaube 9 minuten pro stunde aus macht.
Kann man dann in der Zelle nicht nur die gesamt stunden ausgerechnet bekommen sondern gleich die 15% (in minuten)?
also ich bekomme ja nicht die gesamten nachtstunden gutgeschrieben sondern pro nachtstunde nur 15% was glaube 9 minuten pro stunde aus macht.
Kann man dann in der Zelle nicht nur die gesamt stunden ausgerechnet bekommen sondern gleich die 15% (in minuten)?
Ähnliche Themen
- DieRenteEnte
- Office und Text