Excel Stunden-Berechnung mit Pause?
Hallo allerseits,
folgender Sachverhalt: Zelle A5 Arbeitsbeginn 06:00,Zelle B5 Arbeitsende 14:55,Zelle C5 Formel zur Stundenberechnung : =WENN(UND(A5;B5>0);WENN(A5<=B5;B5-A5;ZEITWERT("23:59")-A5+B5+ZEITWERT("00:01"));"") Zelle D5 P für Pause und K.P für keine Pause und Zelle E5 hier soll die Zeit rein,die größer als 09:00 Stunden ist.
Wie bewerkstellige ich das nun,wenn C5 <9 und D5 ein P steht,das Excel mir in C5 dann 00:30 Minuten abzieht und wenn C5>=9 ist,dann 00:45 Minuten abgezogen wird.
Als erster Lösungsansatz: =WENN(UND(C5<9;D5="P");C5-"00:30";WENN(UND(C5>=9;D5="P");C5-"00:45")). Allerdings läuft das über eine Hilfsspalte. Ich möchte aber,vorrausgestzt es geht,diesen Teil der Formel noch mit in die andere Formel integrieren.
Wäre dann nur noch die Formel für E5,die mir die Überstunden ausgibt.
Wer hat da ne adäquate Idee?
3 Antworten
Neue AW, die andere wird mir zu lang und schmal.
ich habe in Spalte T noch eine Tagesliste eingeführt, das muss ja i'wo stehen. (kannst du hinterher ja nach Bedarf versetzen oder T5 Deiner wahrscheinlich schon vorhandenen anpassen). Der 1.März steht in Zeile5, der 31. dementsprechend in Zeile 35.Formel in F5:
=WENN(C5>"9:00"*1;C5-"9:00";0)
+E5*(REST(T5;7)=1)
+E5*(REST(T5;7)<>1)
*ISTUNGERADE(REST(REST(T5;7);2)+ISTGERADE(GANZZAHL(T5/7))*1) *ISTGERADE(REST(T5;14))
Sehe gerade noch: Istungerade(Rest(Rest(T5;7);2) könnte man auch kürzer ausdrücken als (Rest(Rest(T5;7);2)=1)
das istgerade(Ganzzahl(T5/7))*1 ist ein Addend, der die wechselnden Wochentage innerhalb des 14-Tage-Rhythmus' in die richtige Ordnung bringt.
Generell: Rest(Tagliste;7) ist 0 für Sa, 1 für So, 2..6 für Mo..Fr
Rest(Tagliste;14) gibt die Stellung innerhalb eines 14-Tage-Rhythmus wieder.
Ich jongliere mit der Kombination dieser beiden, um aus nur 1 Tagesdatum abzuleiten, ob es ein planmäßiger oder ein ausserplanmäßiger Arbeitstag ist.
=WENN(C5>"9:00"*1;C5-"9:00";0) : normale AnwesenheitsStunden
+(REST(T5;7)=1)*E5 Zeitfaktor für Sonntage = 1 : Anwesenheit abzgl Pausen (ist E5)
Zeitfaktor für NICHTSonntage: (Rest(..;7)<>1)=WAHR an Werktagen, *E5
Die Überstunden des gesamten Monats erhältst Du in der Zeile des letzten Tags des Monats mit der Formel (bei mir in G35, die Liste braucht dafür noch nicht fertig ausgefüllt zu sein, nur die Tageliste in T sollte stehen): =SUMMENPRODUKT((MONAT(T35)=MONAT(T$5:T35))*F$5:F35)
Ob Du nun für jeden Monat ein eigenes Blatt machst oder die Liste im April nach unten fortführst, bleibt Dir überlassen.
Wenn du einen Fehler finden solltest, sag ihn mir bitte. Und verzweifle nicht, wenn Du nicht durchblickst, ich hab selber recht lange gebraucht, um die eigene Formel für die Erklärung aufzubereiten und hätte beinahe selbst den Durchblick verloren!
ok, ich hab Fehler gefunden, aber noch nicht beseitigen können. Wird möglicherweise erst am WE soweit sein!
Kein Problem,ich such derweil im Netz mal nach Ansatzpunkten für das manuelle einfärben der Zellen ;)
Hab jetzt mal folgenden Lösungsansatz gefunden:
Einfügen - Namen - definieren als [Namen in der Arbeitsmappe:] Farbe eingegeben und im Feld [Bezieht sich auf:] folgende Formel: =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-2)";FALSCH)) eingetragen. Dann in Zelle E1 =WENN(Farbe=42;C1;"") eingetragen und siehe da,es scheint zu funktionieren. Wenn ich nun aber den neuen Teil der Formel in die vorhandene integriere,kommt entweder
falsch wie hier:
=WENN(C1="";WENN(Farbe=42;C1;WENN(C1>"9:00"*1;C1-"9:00";"")))
oder #WERT! wie hier :
=WENN(C1="";WENN(Farbe=42;C1;""));WENN(C1>"9:00"*1;C1-"9:00";"")
Wo ist der Fehler in der Klammersetzung?
Hab die Lösung gefunden.
=WENN(D1="";"";WENN(Farbe=42;D1;WENN(D1>"9:00"*1;D1-"9:00";""))). Leider aktualisiert Exel das nicht automatisch,weiß jemand,wie ich das bewerkstelligen kann?
Lg
ich hab Dich nicht vergessen, aber da waren/sind noch mehr Unstimmigkeiten und ich bin am Ende meiner Konzentrationsfähigkeitl, muss auch so langsam weg. Werde mich heut Abend nochmal dransetzen!
Vorläufig aber mal soviel: Um den Wert-Fehler zu vermeiden, musst Du in
H49: =REST(G49-F49;1)
schreiben (runterziehen) und in
J49: =REST(G49-F49;1)-WENN(I49="P";WENN(H49>="9:00"*1;"0:45"*1;"0:30"*1);0)
"" ist nämlich ein (Leer-)-Text!
Im übrigen empfehle ich immer ein spezielles Ben.-def. Format für Zeitdarstellungen, (auch für den Zeitkonto-Übertrag in J48):
[h]:mm;[Rot]-0,000;[magenta]°;[blau]@
das erlaubt das Rechnen auch mit negativen Zeiten (die aber als rote Zahl dargestellt werden) und stellt das optisch störende 0:00 als unauffälligen violetten °-Kringel dar. Texte werden blau, positive Zeitfarben frei wählbar. Format übertragen nach H49, J49, K49
zB spricht das rot an, wenn in einn Nicht-Arbeitstag P eingetragen wird. aber vllt kommt ja auch mal eine Situation, wo man einen Monat von seinem vorhandenen Überstunden-Konto zehrt.
Kann man dann immer noch in einer anderen Zelle als Minus-Bezug im Zeitformat wieder als positive Zeit darstellen. Oder per Wenn-Formel als -Text (falls Dich das interessiert)
Danke,aber über deine Antwort hatte ich schon geschrieben,das ich den Fehler bzw. die Lösung gefunden hab. Außerdem würde dein Lösungsansatz jetzt überhaupt nicht passen,da in J49 ja meine Überstunden stehen.Die Formel für J49 hab ich jetzt in H49 stehen.Hast dich bestimmt vertan ;)
Ich verstehe schon dein erstes Und nicht..
was genau soll
UND(A5;B5>0)
deiner Ansicht nach machen?
A5 ist immer wahr - es sein denn es steht 0 in A5. Selbst eine Leere Zelle A5 würde Excel als "wahr" interpretieren. Also nur wenn jemand exakt um 0 Uhr anfängt zu arbeiten, dann.... ja dann wird keine Arbeitszeit berechnet..
B5 > 0, okay, man darf nicht 0 Uhr mit der Arbeit aufhören, das ist noch sinnig wenn man an einem Tag rechnet.
Danach wird es nicht verständlicher
was soll
ZEITWERT("23:59")-A5+B5+ZEITWERT("00:01")
denn berechnen?
Es geht doch wohl darum, wenn es sich um ein Nachschicht handelt, dann soll Excel nicht die Differenz zwischen den Beiden Zeiten ermitteln sondern die Differenz zwischen den Beiden Tagen.
Also gilt: (24 Uhr - Arbeitsbeginn) + Arbeitsende ergibt die Summe der Stunden.
Wobei ich bei Tagüberschreitenden Zeiten immer auch mit Datum arbeiten würde, sonst muss man irgendwann anfangen zu "ferkeln"
Wie dem auch sein, die offensichtliche Formel für eine Nachtschicht lautet also 1-A5+B5, das hat aber deiner Formel mal so gar nichts gemein. Wozu wird die 1 dort in 23:59 + 0:01 aufgespaltet !?
Guten Morgen,
diese Formel zur Stundenberechnung habe ich hier aus dem Netz bekommen.Lediglich den Zusatz mit dem "UND(A5;B5>0)" habe ich hinzugefügt. Der soll dazu dienen,dass wenn in einer der beiden Zellen,mal nichts steht,in Zelle C5 nichts berechnet wird.
Die Brutto-Arbeitszeit in xl ist: =Rest(Gehen-Kommen;1) das am besten in C5, ist wesentlich kürzer als Deine Formel, zählt auch über Mitternacht richtig.
Also: =Rest("14:55"-"6:00";1) bzw Bezüge auf die Zeit-Zellen.
(Du kannst das einfach einsetzen, fürs Verstehen müsstest Du Dich näher mit der Funktion Rest() auch bei negativen Zahlen befassen)
Pausenabzug: -Wenn(D5="P";Wenn(C5>="9:00"*1;"0:45"*1;"0:30"*1);0)
Kurzerklärung; xl macht aus "14:55" durch eine Rechenoperation (hier +) die der Zeit entsprechende Zahl. Deshalb andernfalls *1, könnte auch +0 sein.
Steht in D5 P oder p, ist die Bedingung erfüllt, K.P. oder was anderes ist nicht P.
Der Rest sollte selbsterklärend sein.
Eher noch das Rechtliche: Arbeitszeiten von 8h ohne Pause sind nicht erlaubt (war zumindest mal so). Und Arbeitszeiten >10 h bzw 10h+Pausen auch nicht (es gibt Ausnahmen für Bereitschaftsdienste etc). Wenn da was passiert, kommt ihr in Teufels Küche, evtl auch ohne dass was passiert! Informiere Dich dazu!
Streng genommen entspricht die Pausenformel nicht der Frage, vermutlich ist aber eher die Frage falsch.
Gemäß Frage soll die Pause sollen 30 Minuten abgezogen werden wenn Pause aktiviert ist und die Arbeitszeit maximal 9 Stunden beträgt. Bei mehr als 9 Stunden sollten 45 Minuten abgezogen werden, unabhängig davon ob Pause aktiviert ist oder nicht.
@Jackie: das tut sie doch, nur mit umgestellten Argumenten: >=9h: 0:45, ansonsten 0:30 Abzug von der gestempelten Zeit.
Und nein, nicht unabhängig vom P, in der Formel der Frage steht ausdrücklich UND(C5>=9;D5="P") und das dürfte auch so gewollt sein.
Hast Du das - vor dem Wenn() übersehen? Oder steh ich auf dem Schlauch?
(ich sehe das erste Und(A5;B5>0) nur als Flüchtigkeitsfehler, im Blatt scheint die Formel ja funktioniert zu haben.
Nachtzeit: Rest(Gehen-Kommen;1) braucht keine Tagesangabe, es funktioniert bis knapp 24 h zuverlässig ohne, egal wann Gehen und Kommen sind. Also Rest("6:30"-"21:30";1) liefert 9:00 h,
Rest("22:00"-"13:30";1) 8:30 h.
Rest("17:00"-"18:00";1) liefert 23:00 h (was bei Arbeitszeiten ohnehin nicht erlaubt wäre, aber es gibt ja auch andere Fälle)
Genau so ist es.Das P spielt für mich eine wichtige Rolle.Es gibt bei uns Tage,da haben wir einfach keine Möglichkeit eine "offizielle" Pause zu machen und somit bekommen wir diese Pause dann auch bezahlt. Wird aber in 99% der Fälle erst bei Arbeitszeiten von >9 Stunden interessant.
So sieht meine Formel für C5 jetzt zusammen gesetzt aus: =REST(B5-A5;1)-WENN(D5="P";WENN(C5>="9:00"*1;"0:45"*1;"0:30"*1);0) und was noch viel besser ist,es funktioniert sogar 👍
Was noch nicht funktioniert sind die Überstunden in Zelle E5:
A5 = 09:00 B5 = 20:00 C5 = 11:00 D5 = K.P. In Zelle E5 hab ich mir folgende Formel überlegt, wenn C5>9 dann C5 minus 9 sonst nix.
=WENN(C5>"09:00";C5-"09:00";""). Leider bleibt E5 aber leer. Die Formel müsste doch eigentlich okay sein oder?
Nein. Alles was in " " steht, wird von Excel als TEXT interpretiert. Ein Text ist keine Zahl und kann somit nicht größer oder kleiner als etwas anderes sein.
In der Excel-Logik wird bei Datum und Uhrzeit ein Tag in Ganzzahl, eine Uhrzeit in Dezimalzahlen ausgedrückt und lediglich als Datum oder Uhrzeit formatiert.
09:00 ist also eigentlich der Wert 0,375.
=WENN(C5>0,375;C5-0,375;"")
Das wäre also eine Möglichkeit.
Perfekt,genau so habe ich mir das vorgestellt. Jungs,auf Euch Excel-Profis ist wie immer Verlass 👍.Dafür schon mal vorab ein großes Dankeschön an alle.
Ein kniffeliges Schmankerl hätte ich noch,ist nicht dringend notwendig,nur wenn es sich ohne größeren Aufwand bewerkstelligen lässt,warum nicht.
Zur Zeit arbeite ich in der einen Woche Mo,Mi,Fr in der anderen Woche Di,Do,Sa.Der Sonntag sind immer Überstunden.
Wenn ich nun in der Woche mit dem Di,Do,Sa zusätzlich an einem Mo,Mi oder Freitag arbeite,soll Excel mir die Zeile A-E z.B rot einfärben und den gesamten Wert aus C5 auch in E5 setzen.
Mir ist natürlich klar,das Excel am Montag noch nicht wissen kann,ob es Überstunden sind oder reguläre Arbeitszeit.Da die Zeiterfassung über den ganzen Monat geht,könnte Excel das auch am Monatsende prüfen so nach dem Motto,wenn Wochentag 1,3 und 5 eingetragen ist,dann muss Wochentag 2 oder 4 Überstunden sein.
Vielleicht gibt es ja auch eine viel simplere Möglichkeit,die ICH mal wieder nicht sehe.
Sodele,nachdem mir persönlich der Aufwand dafür viel zu groß ist,hab ich mir was anderes einfallen lassen.
Ich werde die Tage,an denen ich ausserplanmässig arbeite,einfach manuell einfärben.Das heisst,wenn A5-D5 zyan eingefärbt ist,soll der Wert von C5 auch in E5 stehen,ohne natürlich die Formel in E5 zu löschen
Danke für die detaillierte Rückmeldung.
Aber zunächst mal zu dieser Formel:
=WENN(C5>"09:00";C5-"09:00";"")
Die Lösung von Funfroc funktioniert, ist aber unanschaulich. > wird von xl nicht als mathematische Operation angesehen (weiß nicht, ob begründet oder übersehen beim Programmieren)
Es funktioniert aber =Wenn(C5>"9:00"*1;C5-"9:00";"")
In das mit der Zusatzarbeit am Mo, Mi oder Fr und den So-Überstunden muss ich mich reinvertiefen, wenn ich mehr Ruhe habe. Kanns nicht versprechen, aber schau heut abend nochmal rein. Zusatzfrage: ist der 14-Tagerhythmus übetr lange Zeiten fixiert? (man kann dann nämlich mit Rest("Datum";14) einen 14-Tage-Rhythmus abfragen (Mo ist dann 2 oder 9, geht sogar ohne *1), ansonsten müsste man noch komplizierter werden mit Abfrage der Tage der Vorwoche.
Ist das IMMER Wenn(UND(Mo..;Mi..;Fr..)... bzw UND(Di..Do..Sa)?
oder kann das auch mal ODER sein : was ist zB bei Krankheit/eintägigem Ausfall?
Was ist bei Urlaub? setzt sich das exakt fort oder kann danach ein Wechsel passieren? (Schichtarbeit in der Auto-Industrie ist zB auf Jahre hinaus planbar konstant).
Bei häufigem Wechsel müsste man sich was anderes einfallen lassen oder bei Deiner Einfärbemethode bleiben.
Also auf die nächsten Monate gesehen bleibt es immer bei Mo,Mi,Fr und die Woche drauf dann Di,Do und Sa.Alle paar Wochen kommt mal nen Sonntag dazu,aber der wäre immer Überstunden. Wenn du Lust hast da mal was raus zu knobeln,gerne,wobei ich mit der Lösung für das Einfärben schon zufrieden wäre,da ich im Netz nix gescheites finde,lediglich was mit farblichen Zellen zählen
Das mit den farblichen Zellen zählen funktioniert nicht bei Einfärbung mit bedingter Formatierung.
Ich muss mal sehen, ob ich heut nacht was Vernünftiges zustandebringe, ansonsten in der nächsten Zeit (sofern ichs hinkriege).
Aber vllt noch eine Spezifizierung: Wann war der letzte Montag mit Arbeit: der 27. oder der 20. März? (Rest(Datum;14) unterscheidet das ja!)
Der letzte reguläre Arbeitstag war der 27. März. Wie gesagt,die bedeutend einfachere Methode scheint mir das manuelle Einfärben der jeweiligen Zellen A-E,so muss Excel mir dann nur die eventuellen Überstunden in E ausgeben
Guten Abend,
habe mir deine Lösung grad mal angeschaut und in meine Tabelle eingebaut.Entweder ist da noch ein Fehler drin,oder es haut mit "meiner" vorhandenen Tagesliste nicht hin.
Hab die Tabelle hier mal hoch geladen,wahrscheinlich siehst du meinen Fehler schneller als ich
https://www.dropbox.com/s/h8xvldfs9wn0hcj/Werbungskosten%20aktuell.xls?dl=0
Lg Frank