Office: (Office 2011 Mac) Gleitzeit ermitteln

Servus,

Ich hoffe es kann mir jemand einen tipp geben.
Ich will Gleitzeit ermitteln. Gleitzeit kann man wie Überstunden verstehen. Es ist die zeit für die Schicht Übergabe.
Die Arbeitszeit ist auf drei Schichten verteilt. Die Gleitzeit ist jeweils 15 min vor und nach der Schicht.

N5 => Beginn (gestempelt)
O5 => Ende (gestempelt)
S5 => Code für Schicht Bezeichnung ( F = Frühschicht usw. )

Die Frühschicht startet um 6:00 Uhr u. endet um 14:00 Uhr.
Die Gleitzeit ist dann von 5:45 bis 6:00 und 14:00 bis 14:15.
Ich möchte die zeit jetzt automatisch zusammen rechnen in Verbindung mit der Abfrage der Schichtbezeichnung
das Ergebnis darf die 15 min nicht überschreiten kann aber kürzer sein, wenn man später stempelt.

grüße Jörg

 

Hallo Jörg,

die Zeit zwischen 2 Stempelungen (Ende - Beginn) kann man mit folgender Formel berechnen:
=REST(O5-N5;1)
und da ich annehme, dass die Kernzeit jeder Schicht 8 Stunden dauert (06:00-14:00 ; 14:00-22:00 ; 22:00-06:00), kann man diese 8/24 Tag abziehen, um so die Gleitzeit davor und danach in Summe zu erhalten (mit folgender Formel):
=REST(O5-N5;1)-8/24
Da 15 Minuten = 1/4 Stunden = 1/96 Tag sind und diese nach deiner Vorgabe das Maximum für die Gleitzeit darstellen, so darf das Ergebnis der vorherigen Formel nicht größer als 1/96 Tag sein. Diese Begrenzung erfolgt schließlich mit folgender Formel:
=MIN(REST(O5-N5;1)-8/24;1/96)
Dieses Ergebnis ist dann noch mittels "hh:mm:ss" bzw. "hh:mm" zu formatieren, damit es im gewohnten Uhrzeitformat angezeigt wird.

 

Hallo Anton,
vielen Dank für deine Tips. Die Formeln funktionieren bestens.
Wie kann ich nun die 3 Formeln Kombinieren.
=WENN(S10="F";MIN(REST(O10-N10;1)-7/24;2/96);"") für die Frühschicht
=WENN(S10="S";MIN(REST(O10-N10;1)-7/24;2/96);"") für die Spätschicht
=WENN(S10="N";MIN(REST(O10-N10;1)-7/24;2/96);"") für die Nachschicht
Ich möchte alle drei in eine Zelle, so das ich mir Hilfsspalten sparen kann.
beste Grüße

 

Hallo Jörg,

falls in Zelle S10 auch andere Buchstaben als "F" bzw. "S" bzw. "N" vorkommen können, lautet die Formel gemäß deinen Vorgaben:
=WENN(ODER(S10="F";S10="S";S10="N");MIN(REST(O10-N10;1)-7/24;2/96);"")
Sollten jedoch in Zelle S10 nur diese 3 Buchstaben vorkommen (können), dann vereinfacht sich die Formel gemäß deinen Vorgaben:
=MIN(REST(O10-N10;1)-7/24;2/96)

 

Hi Anton,

ich muß mich berichtigen
In S10 sind 4 Buchstaben, aber in den
Vorgaben für die stunden anders: F=8h, S=9h, N=7
Und T kommt noch dazu, T=9.

 

An der Erkennung für den Tagescode in S10 beiss ich mir auch die Zähne aus.
Das heißt ich möchte das excel automatisch in die Zelle S10 den Tagescode einträgt.
Wenn die gestempelten Zeiten in N10 und O10 eingegeben sind. Das Problem ist das die Uhrzeiten
nicht immer die gleichen sind, sich aber immer in einem bestimmten Bereich befinden.
Für die Frühschicht findet die Stempelung immer zwischen 5:15 und 6:00 statt.
Spätschicht 13:15 - 14:00 , Nachtschicht 22:15 - 23:00 , Tagschicht 6:15 - 7:00

 

Hallo Jörg,

in der beiliegenden Excel-Datei habe ich dir
a) eine kleine Tabelle für die Schicht-Typen (Bereich P2:T6) eingefügt;
b) ferner in Zelle S10 eine Formel zur Ermittlung des Schichttyps: =VERWEIS(N10;$P$3:$Q$6)
c) und in Zelle T10 eine Formel zur Ermittlung der Gleitzeit:=MIN(MAX(REST(O10-N10;1)-SVERWEIS(S10;$Q$3:$T$6;3;FALSCH);0);2/96)

Beide Formeln benutzen Teile der Schicht-Typen-Tabelle.

Wird in Zelle N10 (Beginn) eine Uhrzeit vor 05:15 eingegeben, wird in S10 (Schichttyp) und in T10 (Gleitzeit) der Wert #NV angezeigt, was bedeutet, dass in der Schichttypentabelle der gesuchte Eintrag nicht verfügbar ist.

 

Hola,

http://www.clever-excel-forum.de/Thread-Formeln-zusammenfassen?pid=36958#pid36958

Ging ihm hier wohl nicht schnell genug.

Gruß,
steve1da

 

Hallo Anton,
Die Hilfe mit der Tabelle ist klasse.
Nur mit der Gleitzeit hab ich noch ein kleines Problem. Da ich ich die Funktion Verweis und Verweis noch nicht richtig verstehe.
Als Beispiel für Frühschicht: Stempelung Beginn 5:28 und 14:09 als Ende
Wird Gleitzeit 15min vor 6:00 Uhr berechnet und 9 min nach 14 Uhr sind 24min
Deine Formel rechnet 30 min aus
Oder 5:50 und 14:17 sollten nur 25min ergeben nach deiner Formel sind es 27min

Kannst du da noch mal schauen wo der Fehler ist

viele Grüße
Jörg

 

Nun hab ich noch ein Problem gefunden.
Ich benutze die Formel zur Ermittlung der Nachtstunden:
=WENN(N10="";"";MAX(;MIN($I$20+($H$20>$I$20);O10+(N10>O10))-MAX($H$20;N10))+MAX(;(MIN($I$20;O10+(N10>O10))-N10)*($H$20>$I$20))+MAX(;MIN($I$20+($H$20>$I$20);O10+0)-$H$20)*(N10>O10))
Ich muß aber davon die Gleitzeit vor bzw nach der Schicht abziehen
In H20 und I20 stehen die Tarifzeiten für die Zuschläge 21 bis 6 Uhr
Das selbe gilt für Samstags:
=WENN(N10="";"";WENN(WOCHENTAG(L10;3)=5;MAX(;MIN($I$21+($H$21>$I$21);O10+(N10>O10))-MAX($H$21;N10))+MAX(;(MIN($I$21;O10+(N10>O10))-N10)*($H$21>$I$21))+MAX(;MIN($I$21+($H$21>$I$21);O10+0)-$H$21)*(N10>O10);
Hier gilt der Zuschlag von 13 bis 21 Uhr (H21 u. I21).
Wenn da jemand einen tip hat.
Vielen Dank

 

Hola,

...

http://www.clever-excel-forum.de/Thread-Abzug-der-Gleitzeit-von-Nachtstunden

Gruß,
steve1da