Excel Stunden-Berechnung mit Pause?

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

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!

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 

0
@Blondie64

ok, ich hab Fehler gefunden, aber noch nicht beseitigen können. Wird möglicherweise erst am WE soweit sein!

1
@Iamiam

Kein Problem,ich such derweil im Netz mal nach Ansatzpunkten für das manuelle einfärben der Zellen ;)

0
@Blondie64

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?

0
@Blondie64

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

0
@Blondie64

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)

0
@Iamiam

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 ;)

0

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.

0
@Jackie251

@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)

2
@Iamiam

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 👍

0
@Blondie64

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?

0
@Blondie64

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.

1
@Funfroc

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.



0
@Blondie64

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

0
@Blondie64

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.

0
@Iamiam

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

0
@Blondie64

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!)

0
@Iamiam

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

0

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 !?

0
@Jackie251

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.

0

EXCEL Formel gesucht ( Kniffel programmieren)

Hallo, ich soll in Informatik das Spiel Kniffel in Excel programmieren. Ich habe soweit auch schon alles fertig. Das Einzige, dass mir noch fehlt ist eine Formel um die 5 Zufallswürfel, die mit dem Befehl ZUFALLSBEREICH 1;6 , per Klick auf ein Kästchen beim nächsten Wurf nicht mehr neu würfeln. Also ganz kurz nochmal: Ich habe 5 Würfel, die auch würfeln. Ich brauche nur noch eine Formel um bestimmte Würfel, die man anklickt bei einem erneuten Wurf nicht mehr würfeln. Zusätzlich soll es eine Sperre geben, dass mannl nur 3x würfeln kann.

Danke für eure Hilfe.

...zur Frage

Wenn Dann Sonst Excel Arbeitsstunden mit Pausen berechnen

Hallo, ich will gerade eine Formel erstellen für folgende Berechnung: Arbeitszeit A1 13 A2 20

Wenn A2-A1 größer ist als 9 Stunden dann 0,75 Stunde Pause abziehen sonst 0,5 Stunde . Unter 6 Stunden keine Pause abziehen. Hab es bisher das er mit über 9 Stunden die 0,75 Stunde Pause abzieht aber weiß nicht, wie ich in der Formel noch die unter 6 Stunden reinbekomme.

=WENN(E5-D5>9;E5-D5-0,75;E5-D5-0,5)

...zur Frage

von einer arbeitszeit berechnung mit folgender formel noch eine pause abziehen?

ich will von der formel zur Berechnung der nachtarbeitszeit noch eine pause abziehen.

=WENN(ISTZAHL(D7)*ISTZAHL(E7);WENN(E7>ZEITWERT("20:00");E7-MAX(ZEITWERT("20:00");D7);0)+WENN(D7

Jetzt will ich davon noch eine pause die in H6 angegeben ist abziehen. wo und wie gebe ich das ein?

...zur Frage

Wie kann ich eine Excel Formel weiterlaufen lassen bei der sich Basiswerte ändern?

Hallo Leute,

Ich versuche eine Formel weiterzuziehen, allerdings scheint Excel die Basis der Formel nicht automatisch zu verstehen. Es ist eigentlich recht simpel: In einer immer gleich bleibenden Spalte stehen Einzelpreise für verschiedene Produkte. Auf diese soll sich jede weitere Spalte beziehen. Jede weitere Spalte ist dann ein einzelner Kunde der verschiedene Artikel in unterschiedlichen Mengen bestellt. In der untersten Zeile soll ein Gesamtpreis errechnet werden pro Kunde. Also jede Zelle mal den Preis in der jeweiligen Zeile. D5*B5+D6*B6+D7*B7 usw. Die B-Spalte sind die Preise. Beim nächsten Kunden soll es dann so weiterlaufen: E5*B5+E6*B6+E7*B7. Excel macht aber dies: E5*D5+E6*D6+E7*D7.

Ich hoffe das ist verständlich und mir kann einer helfen. Danke!

...zur Frage

Excel - nur Negative Zahlen anzeigen

Hallo hallo,

ich benutzt derzeit eine Formel:

=B5&" "&" "&E5&""

Zeil ist es diese Formel so umzustellen dass, die Zahlen aus E5 nur angezeigt werden wenn sie im Minus stehen.

die Derzeitige Ausgabe für die Formel ist:

yxc 10

xcv -5

cvb 12

die zukünftige Ausgabe soll sein

yxc

xcv -5

cvb

ist es möglich dies so umzustellen?

im voraus vielen DANK! :-)

...zur Frage

Gibt es in Excel eine Option die Differenz zum vorherigen Ergebnis einer Zelle anzeigen zu lasen?

Hallo liebe Community, die Zelle A5 ist das Ergebnis einer langen Berechnung. Nun werden die Faktoren die diese Berechnung beeinflussen geändert, wodurch sich auch das Ergebnis der Zelle A5 ändert. Gibt es eine Möglichkeit in einer weiteren Zelle z.B. B5 sich die Differenz aus dem aktuellen Ergebnis von A5 und dem vorherigen anzeigen zu lassen?

Vielen Dank im Voraus:)

...zur Frage

Was möchtest Du wissen?