komme leider bei einer Zeiterfassungsexcel nicht weiter. Um genau zu sein hab ich irgendwie komplett nen Brett vorm Kopf gerade und habe nicht mal nen Ansatz :(
Vielleicht kann mir einer von euch Cracks vielleicht dabei helfen eine passende Formel zu finden.
So sieht die Tabelle aus:
[ Bild bitte so als Datei hochladen: Klick mich! ]
- die 25%(Spalte J) gibt es zwischen 20 und 6 Uhr, exklusive Pause
- die 40%(Spalte K) gibt es zwischen 0 und 4 Uhr, exklusive Pause
Sollte nun also jemand von 14 - 22 Uhr arbeiten, müsste in Spalte J "2" stehen. Macht er zudem noch Pause von 20-21 Uhr, müsste in Spalte J "1" stehen.
Von 1-4Uhr = Spalte J "3"
Von 0-4Uhr = Spalte K "4"
Von 0-6, mit Pause von 3-4 = Spalte J "2", Spalte K "3"
Hoffe es ist verständlich was ich meine, ansonsten nachhaken. Danke.
Zitat:Hoffe es ist verständlich was ich meine, ansonsten nachhaken. Danke.
nö, ist es leider nicht.
Stell uns bitte keine Bildchen, sondern (d)eine (Beispiel)Tabelle vor. Wie das geht, kannst du hier nachlesen: http://www.clever-excel-forum.de/thread-326.html. In dieser Tabelle füge bitte händisch in zwei bis drei Datensätzen dein Wunschergebnis ein. Dann, denke ich, werden auch die richtigen Antworten kommen.
Gruß Günter Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen. angebl. von Georg Christoph Lichtenberg (1742-1799)
29.06.2016, 22:33 (Dieser Beitrag wurde zuletzt bearbeitet: 29.06.2016, 22:33 von Ego.)
Hallo Brutality,
Wenn man das gemeinsame Intervall (Schnittmenge) (U,O) zweier Intervalle (U1,O1) und (U2,O2) ermitteln will, berechnet man U = MAX(U1;U2) und O = MIN(O1;O2).
Hierbei sind zwei Fälle zu unterscheiden:
Fall 1: U kleiner O
Dann ist die Schnittmenge (U,O).
Fall 2: U größer oder gleich O
dann haben die Intervalle keine Schnittmenge.
Für die Arbeitszeiten sind aber nicht die Intervalle, sondern die Länge der Intervalle zu berechnen.
Fall 1: U kleiner O
"Länge = O-U" wie schon in Spalte I berechnet. Dieser Wert ist positiv.
Fall 2: U größer oder gleich O
"Länge = 0" (Null) da keine Schnittmenge.
Wenn man im Fall 2 auch O-U rechnet bekommt man 0 oder negative Werte.
Über die Formel:"Länge= MAX(0;O-U)" kann man die Länge des gemeinsamen Intervalls für beide Fälle ermitteln.
Die Formel zur Berechnung der Länge einer Schnittmenge ist dann: "Länge = MAX(0;-MAX(U1;U2)+MIN(O1;O2)).
40% Zuschlag:
Ich muss hierfür die Schnittmenge mit der Arbeitszeit und mit der Pause berechnen daher lautet die Formel in der Zelle K5
25% Zuschlag:
Hier berechne ich die Länge der Schnittmengen zwischen Arbeitszeit und Pause mit den Zeitintervallen 4-6 Uhr und 20-24 Uhr.
Das bedeutet, dass sich die Länge der Formel wieder verdoppelt und in der Zelle J5 steht dann:
Es gibt zwei Excel Besonderheiten, die unbedingt zu beachten sind.
1. Die MIN- und die MAX-Funktionen in Excel berücksichtigen keine leeren Zellen.
Das bedeutet die Schnittmengen werden nur richtig berechnet wenn sowohl die Arbeits- als auch die Pausen-Zeiten Werte enthalten.
Wenn beide Zeitbereiche leer sind kommt auch das richtige Ergebnis heraus.
Wenn aber nur die Arbeitszeit und keine Pause erfasst ist (das umgekehrte ist sowieso sinnlos), wird das Ergebnis falsch.
Wenn in Wirklichkeit keine Pause gemacht wurde kann man eine Pause mit Anfangszeit = Endzeit eintragen. Das Auflösen dieser Bedingung überlasse ich den wirlichen Excel Formel-Experten.
Alle meine Ideen würden die Formel unleserlich machen.
2. Excel unterscheidet zwischen 0 Uhr und 24 Uhr
Auch wenn das bei dem gewählten Format in den Spalten E bis H nicht sichtbar ist. Arbeitszeiten bis 24 Uhr müssen auch als "24:" eingegeben werden.
Mein Vorschlag:
Die beiden Formeln übernehmen und in der Datei sicherstellen dass
1. Wenn die Arbeitszeit eingetragen ist auch eine Pause eingetragen ist.
2. Der Anfang der Arbeitszeit immer kleiner als das Ende der Arbeitszeit und die Differenz kleiner gleich 1 ist.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • brutality
30.06.2016, 08:44 (Dieser Beitrag wurde zuletzt bearbeitet: 30.06.2016, 08:44 von brutality.)
Guten Morgen,
habe die Tabelle angehängt und auch schon die Formeln von Ego eingefügt.
Diese funktionieren soweit auch und machen das was ich machen will.
Dazu jedoch noch 2 Fragen bezüglich den Hinweisen:
- Wie Ego sagte gehen die Formeln nur wenn eine Pause eingetragen ist. Lässt sich dies auch irgendwie umgehen da es vermehrt dazu kommt, das keine Pause gemacht wird(z. B. wenn der Einsatz nur 2 Std dauert)
Beispiel: Zeile 13/14 + 17
- die Differenz zwischen Beginn und Ende kann auch geringer als 1 sein, 30min Einsatz z. b. Lässt sich dies auch umgehen?
Und dann noch eine letzte Frage:
Angenommen jemand arbeitet an einem Montag von 0-4Uhr und am Sonntag davor von 22-24Uhr, bekommt er für diese Zeit auch noch den Sonntagszuschlag. Kann man dies irgendwie mit einbinden in meine Formeln? Also sowas wie: Wenn Montag 0 Uhr Beginn und Sonntag 24Uhr Ende, dann zähle auch die Stunden am Montag von 0-4(ohne Pause) als Sonntagsstunden.
Beispiel wären Zeile 19+20 in der Tabelle
Das selber gilt auch bei einem Tag nach einem Feiertag, analog wie am Montag.
2. Arbeitsenden- Arbeitsanfang kleiner gleich 1.
Hier hast du meine Bedingung nicht richtig gelesen. Die Differenz muß kleiner oder gleich 1 sein.
Das bedeutet nicht, dass man nur eine Stunde am Tag arbeiten darf.
Excel verwaltet Zeitpunkte als Zahlen. Hierbei ist der Tag die Einheit und eine Stunde ist 1/24 (=0,4166..).
Die Bedingung "kleiner als 1" bedeutet also nur, dass man nicht mehr als 1=24/24=24Stunden am Tag arbeiten kann.
Du kannst ja einmal in deiner Tabelle, in den Spalten E und F (Wenn schon als Stunden formatiert) größere Zahlen mit Nachkommastellen eingeben und sehen was mit deiner Arbeitszeitberechnung geschieht.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • brutality
30.06.2016, 10:01 (Dieser Beitrag wurde zuletzt bearbeitet: 30.06.2016, 10:01 von BoskoBiati.)
Hallo,
solche Berechnungen gibt es doch schon zuhauf im Forum und im restlichen Netz. Eine Formel, die zwingend Einträge in einem Feld braucht, ist nicht ok! Auf die Funktion Zeit() kann man auch locker verzichten.
@Ego: Danke und ja Hinweis2 hab ich falsch gelesen, sorry :)
@Bosko: Ich habe davor schon lange gegoogelt aber leider nur Standardberechnungen gefunden daher die Frage hier. Auch die von dir angegebenen Links helfen nur teilweise. Der jetzt noch offene Punkt, lässt sich(zumindest für mich) nicht daraus ableiten.
Hat denn jemand für die letzte Fragestellung noch eine Lösung parat?
Angenommen jemand arbeitet an einem Montag von 0-4Uhr und am Sonntag davor von 22-24Uhr, bekommt er für diese Zeit(am Montag) auch noch den Sonntagszuschlag. Kann man dies irgendwie mit einbinden in die Formeln? Also sowas wie: Wenn Montag 0 Uhr Beginn und Sonntag 24Uhr Ende, dann zähle auch die Stunden am Montag von 0-4(ohne Pause) als Sonntagsstunden.
Beispiel wären Zeile 19+20 in der Tabelle
Das selbe gilt auch bei einem Tag nach einem Feiertag, analog wie am Montag.
Kann leider nicht mehr editieren, daher Doppelpost. Sorry.
Ich komme leider nicht auf den roten Part(sitze wahrscheinlich schon zu lange drüber)
Wenn ich die Formel 1zu1 übernehme, zählt er mir auch die Zeit von 1-4/2-4/3-4 als 40 %. Obwohl es ja nur 40% sind wenn zwingend Start bei 0Uhr. Bei Start 2/3/4 sind dies ja normale 25%-Stunden.
Kann da nochmal jemand unterstützen bitte? Danke.
1Hallo,
hier die Zuschlagsfomeln, wenn die 40% nur bei Arbeitsbeginn 0 Uhr berücksichtigt werden (bei einem Arbeitsbeginn um eine Sekunde nach Mitternacht werden dann nur noch die 25% berücksichtigt.
Da meines Erachtens deine Bedingungen und Beispiele for die Sonntags- und Feiertagszuschläge noch nicht vollständig sind, hier die Vorgehensweise:
Feiertagszuschläge:
1. Erstelle eine vollständige Liste der Bedinungen für Feiertagszuschläge FTBedinung(1),...;FTBedingung(n)
2. Erarbeite zu jeder Bedingung die Berechnungsformel FTFormel(1),...,FTFormel(n)
3. Die Formel zur Berechnung des Feiertagzuschlages ist dann:
= Wenn(FTBedingung(1);FTFormel(1);Wenn(FTBedingung(2);FTFormel(2);...;Wenn(FTBedingung(n);FTFormel(n);0)...))
Sonntagszuschläge:
Im Prinzip muß man hier genau wie bei den Feiertagszuschlägen vorgehen. Da ich aber davon aus gehe, daß nicht gleichzeitig Feiertags- und Sonntagszuschläge berücksichtigt werden, muss man die Fälle, an denen schon für einige Stunden Feiertagszuschläge ermittelt werden, gesondert betrachten.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Bedingungen sind nicht meins, bräuchte noch mal Support :/ Bei folgenden Sachverhalten sollen die Spalten L+M berechnet werden.
Spalte L - Sonntagszuschlag:
0 - 24 h, wenn es ein Sonntag ist(Prüfung über Hilfsspalte C - wird ausgeblendet später)
0 - 4 h, wenn es ein Montag ist und am Sonntag davor das Arbeitsende 24h ist (sprich wenn z. B. von So 22Uhr bis Mo 5 Uhr gearbeitet wird)
0 - 24 h, wenn es ein Sonntag ist und nicht gleichzeitig ein Feiertag ist(Spalte D - wird mit Text gefüllt)(Feiertag steht höher als Sonntag)
Spalte M - Feiertagszuschlag:
0 - 24 h, wenn es ein Feiertag ist(Spalte D - wird mit Text gefüllt)
0 - 4 h, am Folgetag des Feiertags wenn das Arbeitsende am Feiertag selbst 24h ist (sprich wenn z. B. von FT 22Uhr bis Folgetag 5 Uhr gearbeitet wird)
0 - 24h, wenn es zwar Sonntag ist aber zusätzlich ein Feiertag (Feiertag steht höher als Sonntag)
![[-]](https://www.clever-excel-forum.de/images/collapse.png)