Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1408to1412
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Aus Arbeitszeiten Sonderzeiten aussplitten

Aus Arbeitszeiten Sonderzeiten aussplitten
22.02.2015 14:39:27
Seebauer
Hallo zusammen,
ich suche Hilfe bei der Lösung von Arbeitszeitauswertungen, die auf folgenden Kriterien beruhen sollen:
Kernarbeitszeit: 07:00 Uhr bis 16:00 Uhr
Mehrarbeitszeit: 06:00 Uhr bis 07:00 Uhr und 16:00 Uhr bis 17:00 Uhr
Überstundenzeit: 17:00 Uhr bis 06:00 Uhr
Es sollen entsprechende Ergebnisspalten in eine bestehende Excelauswertung (nachträglich) eingefügt werden, die in einer Tabelle aus Zeilenreihen (wochenweise) bereits die Werte "Kommen" - "Gehen" = "Anwesenheit" ausweist.
z.B.
07:00 12:00 05:00
12:30 16:00 03:30
Die Tabelle sollte nun darüber hinaus ausrechnen können, wann in einer Zeitspanne
Mehrarbeitsstunden oder aber Überstunden angefallen sind.
Wie stelle ich das an? (Mit Wenn und Dann komme ich hier leider nicht weiter..)
Vielen Dank für eure Unterstützung!
Gruß
Brigitte

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Aus Arbeitszeiten Sonderzeiten aussplitten
22.02.2015 20:33:08
Ulrich
Hi Brigitte,
die reine Excel-Lösung ohne VBA könnte so aussehen:
für die Arbeitszeitberechnung: "=WENN(A#="";"";WENN(B#<A#;(Tabelle2!H1-A#+1/1440)+B#;B#-A#))" für die Mehrarbeitszeitberechnung:
"=WENN(A#="";"";WENN(A#<Tabelle2!B1;WENN(A#<=Tabelle2!A1;1/24;A#-Tabelle2!A1);0)+WENN(B#>
Tabelle2!C1;WENN(B#>=Tabelle2!D1;1/24;Tabelle1!E1-Tabelle1!B#);WENN(B#<A#;WENN(A#<=Tabelle2!C1;
1/24;WENN(A#<Tabelle2!D1;Tabelle2!D1-A#;0)))))"
für die Überstundenzeitberechnung:
"=WENN(A#="";"";WENN(A#<Tabelle2!F1;Tabelle2!F1-A#;0)+WENN(B#>Tabelle2!G1;B#-Tabelle2!G1;
WENN(B#<Tabelle2!F1;(7/24)+B#;0)))"
wobei sich in Tabelle2 folgende Informationen befinden:
[Zelle A1]: 6:00 | [Zelle B1]: 7:00 | [Zelle C1]: 16:00 | [Zelle D1]: 17:00 (Mehrarbeitszeiten)
[Zelle E1]: 0:00 | [Zelle F1]: 6:00 | [Zelle G1]: 17:00 | [Zelle H1]: 23:59 (Überstundenzeiten)
Erklärung:
das Zeichen "#" steht in den Formeln für die Zeilenzahl
jede Formel prüft zunächst, ob eine Anfangszeit in Spalte A eingetragen ist, Wenn nicht, wird nix angezeigt
Die Zellen müssen im Uhrzeitformat (sinnvollerweise "00:00") formatiert sein.
Die Formel für die reine Arbeitszeitberechnung prüft zunächst, ob bis nach Mitternacht gearbeitet wurde. Ist dies der Fall, wird die Zeit vom Arbeitsbeginn errechnet und mit der Endzeit addiert. Wurde nicht bis nach Mitternacht gearbeitet, wird nur die Differenz aus Endzeit und Anfangszeit ermittelt.
Die Formel für die Mehrarbeitszeit besteht aus zwei Teilen. Die Formel im ersten Teil prüft zuerst, ob die Startzeit vor der regulären Arbeitszeit liegt. Ist dies der Fall, wird geprüft, ob die Startzeit vor dem Beginn der Mehrarbeitszeit liegt. Wenn Ja wird eine Stunde ausgegeben, wenn Nein, wird die Differenz zwischen regulärer und Startzeit berechnet. Ist von alldem nichts der Fall, wird 0 ausgegeben. Im zweiten Teil wird geprüft, ob die Endzeit nach der regulären Arbeitszeit liegt. Ist dies der Fall, wird geprüft, ob die Endzeit größer oder gleich dem Ende der Mehrarbeitszeit ist. Wenn Ja, wird eine Stunde ausgegeben, wenn Nein, wird die Differenz zwischen Endzeit und Ende der regulären Arbeitszeit ausgegeben. Liegt jedoch die Endzeit vor dem Ende der regulären Arbeitszeit, wird geprüft, ob die Endzeit vor der Startzeit liegt (Arbeiten bis nach Mitternacht). Wenn JA, wird geprüft, ob die Startzeit vor dem Ende der regulären Arbeitszeit liegt oder dieser gleicht. Wenn Ja, wird eine Stunde ausgegeben, wenn Nein, wird geprüft, ob die Startzeit vor dem Ende der Mehrarbeitszeit liegt. In diesem Fall wird die Differenz aus dem Ende der Mehrarbeitszeit und der Startzeit errechnet und ausgegeben. Ist dies nicht der Fall, wird 0 ausgegeben. Die beiden Teilergebnisse werden miteinander addiert und das Ergebnis der Addition wird angezeigt.
Die Formel für die Überstunden besteht ebenfalls aus zwei Teilen und ist der eben geschilderten Funktion ähnlich (nur etwas einfacher). Liegt die Startzeit vor dem Ende der "Überstundenzeit" (also vor 6:00), wird die Differenz aus dem Ende der Überstundenzeit und der Startzeit berechnet und ausgegeben. Wenn nicht, wird 0 ausgegeben. Liegt die Endzeit nach dem Beginn der Überstundenzeit (>17:00), wird die Differenz aus Endzeit und Beginn der Überstundenzeit berechnet und ausgegeben. Ist dies nicht der Fall, wird überprüft, ob die Endzeit kleiner ist, als die Überstundenendzeit (bei Arbeiten bis nach Mitternacht). Ist dies der Fall, wird die Summe aus sieben Stunden (17:00 ... 24:00) und der Endzeit gebildet und ausgegeben. Ist dies nicht der Fall, wird 0 ausgegeben. Wieder werden beide Teilergebnisse zum Formelergebnis addiert und angezeigt.
Ich bin mir sicher, dass das Ganze mit VBA eleganter zu lösen ist, aber das ist mir auf die Schnelle eingefallen und hilft erst mal weiter. Auch könnten diese Berechnungen für eine VBA-Lösung als Grundlage dienen, da auch hier ähnliche Berechnungen angestellt werden müssten.
Die Formeln kann man ganz einfach einmal einfügen und die "#" durch die entsprechende Zeilenzahl ersetzen. Wenn man jetzt die Formeln nach unten kopiert, wird Excel zwar alle Zellenangaben anpassen - was dazu führt, dass die Formlen nicht mehr funktionieren, da diese in leere Zellen zeigen, aber mittels (z.B.) "Suchen & Ersetzen" kann man hier in den (markierten) Spalten nach "Tabelle2!E*-" (als Beispiel für die erste Formel) suchen und durch "Tabelle2!E2-" ersetzen lassen.
HTH
Ciao Uli

Anzeige
AW: Aus Arbeitszeiten Sonderzeiten aussplitten
23.02.2015 08:42:04
Seebauer
Hallo lieber Uli,
ich habe Deine Formel zwar in der Spalte Arbeitszeit einfügen können, die Spalte Mehrarbeitszeit funktioniert aber nicht mehr, OBWOHL ICH DIE ZEILENZIFFER durch suchen - einfügen angepasst habe.
Auch die ersten beiden Programmbedingt angezeigten Spalten habe ich vor der Formeleingabe nach hinten verbannt, danach wieder nach vorne geholt, um die automatische Spaltenanpassung zu erhalten.
Leider beisst's bei der Spalte "Mehrarbeits-Stunden" aus, wie der Bayer so schön sagt. Hier funktioniert was nicht... Warum, keine Ahnung!
Vielleicht könntest Du mir ja da nochmal hilfreich unter die Schultern greifen, wenn ich die das Ding einfach sende, so können wir viel Erklärungs-Palawer sparen und Du siehst gleich, was ich meine.

Die Datei https://www.herber.de/bbs/user/95920.xls wurde aus Datenschutzgründen gelöscht


Die Zielspalten habe ich farbig markiert in rot, blau und grün.
Ich wäre so froh, wenn Du mir doch noch abschließend helfen könntest.
Danke!
Brigitte Seebuer

Anzeige
AW: Aus Arbeitszeiten Sonderzeiten aussplitten
23.02.2015 13:01:56
Ulrich
Hi Brigitte,
ich hab mir das mal angeschaut und ein paar kleine Sachen gefunden: beim Anpassen der Formeln sind Verweise in die falsche Zeile hineingeraten. So wird z.B. versucht, den Text aus Zeile 1 in die Berechnungen mit einzubeziehen. Außerdem muss die "Hilfstabelle" namens "Tabelle2" angelegt werden (hier kommen doch die Zeiten für MA-Std und Ü-Std her).
Ich habe die Formeln in Deiner Tabelle mal entsprechend angepasst und die Werte in Tabelle2 abgelegt (seltsamerweise funktioniert das nur, wenn die Zahlen hier als Text formatiert sind!?!). Natürlich sind jetzt Deine externen Verküpfungen weg, aber die Formeln kannst Du Dir aus den betreffenden Zellen heraus nehmen (und nicht Tabelle2 vergessen!).
Anhand Deiner Tabelle habe ich feststellen müssen, dass ich noch nicht alle Möglichkeiten berücksichtigt habe (konkret: wenn jemand nachts von 3:00 - 4:00 arbeitet) und außerdem habe ich noch einen Fehler entdeckt - ich habe also alle Funktionen noch einmal überarbeitet (auch die für die Arbeitszeit).
https://www.herber.de/bbs/user/95927.xls
Ich hoffe, ich habe nix übersehen. Wenn Dir noch etwas auffällt, melde Dich einfach.
Viel Erfolg!
Ciao Uli

Anzeige
AW: Aus Arbeitszeiten Sonderzeiten aussplitten
23.02.2015 11:48:17
Arthur
Hallo Brigitte.
Über die MIN/ MAX Funktionen geht das Berechnen recht gut. Einziger Trick: Mitternacht mit "24:00" geht nicht, dazu muss bspw: "23:00"+"1:00" in der Zelle gerechnet werden.
Anbei ein Lösungsvorschlag.
https://www.herber.de/bbs/user/95925.xlsx
Gruß, Arthur

Arbeitszeiten Sonderzeiten aussplitten
23.02.2015 17:13:00
Erich
Hi Brigitte,
hier noch ein Vorschlag, der intensiv mit MIN und MAX arbeitet.
Ein Mangel in deiner Arbeitsmappe ist, dass die Uhrzeiten in den Spalten C und D (und in weiteren Spalten)
keine Zahlen, sondern Texte sind. Damit lässt sich nicht gut rechnen.
Hier mein Vorschlag, mit einigen Beispielen. Den Vorgaben in Tabelle2 habe ich Namen gegeben:
 ABCDEFG
1vonbisKernMehrÜberSummegesamt
204:4405:5500:0000:0001:1101:1101:11
304:4406:1500:0000:1501:1601:3101:31
404:4407:1500:1501:0001:1602:3102:31
504:4417:1509:0002:0001:3112:3112:31
604:4403:3309:0002:0011:4922:4922:49
705:5512:0005:0001:0000:0506:0506:05
805:5516:1509:0001:1500:0510:2010:20
905:5517:1509:0002:0000:2011:2011:20
1010:0012:0002:0000:0000:0002:0002:00
1110:0016:1506:0000:1500:0006:1506:15
1210:0017:1506:0001:0000:1507:1507:15
1316:0517:1500:0000:5500:1501:1001:10
1417:0517:1500:0000:0000:1000:1000:10

Formeln der Tabelle
ZelleFormel
C2=MAX(;MIN(KernB+(KernV>KernB); B2+(A2>B2))-MAX(KernV;A2)) +MAX(;(MIN(KernB;B2+(A2>B2))-A2)*(KernV>KernB)) +MAX(;MIN(KernB+(KernV>KernB); B2+0)-KernV)*(A2>B2)
D2=MAX(;MIN(Mehr1B+(Mehr1V>Mehr1B); B2+(A2>B2))-MAX(Mehr1V;A2)) +MAX(;(MIN(Mehr1B;B2+(A2>B2))-A2)*(Mehr1V>Mehr1B)) +MAX(;MIN(Mehr1B+(Mehr1V>Mehr1B); B2+0)-Mehr1V)*(A2>B2)
+MAX(;MIN(Mehr2B+(Mehr2V>Mehr2B); B2+(A2>B2))-MAX(Mehr2V;A2)) +MAX(;(MIN(Mehr2B;B2+(A2>B2))-A2)*(Mehr2V>Mehr2B)) +MAX(;MIN(Mehr2B+(Mehr2V>Mehr2B); B2+0)-Mehr2V)*(A2>B2)
E2=MAX(;MIN(UebeB+(UebeV>UebeB); B2+(A2>B2))-MAX(UebeV;A2)) +MAX(;(MIN(UebeB;B2+(A2>B2))-A2)*(UebeV>UebeB)) +MAX(;MIN(UebeB+(UebeV>UebeB); B2+0)-UebeV)*(A2>B2)
F2=SUMME(C2:E2)
G2=REST(B2-A2;1)
Namen in Formeln
ZelleNameBezieht sich auf
C2KernB=Tabelle2!$C$7
C2KernV=Tabelle2!$B$7
D2Mehr1B=Tabelle2!$C$8
D2Mehr1V=Tabelle2!$B$8
D2Mehr2B=Tabelle2!$E$8
D2Mehr2V=Tabelle2!$D$8
E2UebeB=Tabelle2!$C$9
E2UebeV=Tabelle2!$B$9


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Tabelle2

 ABCDE
6 vonbisvonbis
7Kern07:0016:00  
8Mehr06:0007:0016:0017:00
9Über17:0006:00  

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige