Supportnet / Forum / Tabellenkalkulation
Nachtstunden ausrechnen
Frage
Bei Der suche nach einer formel für Excel bin ich auf dieses Forum gestossen und habe wieder hoffnung das mir auch einer helfen kann.
mein Problem:
Zelle A Anfangszeit der Arbeit Zelle B ende
Zelle C gesammt Sunden
Nun möchte ich in der zelle D die Tag Std und in der Zelle E die Nachtstunden.
Damit ich für den Mitarbeiter die Stunden wo er nachts arbeite seinen Nachtzusclag berechnen kann.
NACHTZUSCHLAG zwichen 20:00 und 06:00 Uhr
Ich hatte es fast schon aber da hat es nur beim Tag wechsel (allso über 00:00 Uhr) geklappt aber nicht wenn er z.B. von 02:00 bis 5:00 gearbeitet hat (3 Nachstunden)
18:00 bis 06:00Uhr = 2 Tag und 10 Nacht
Bei fragen dann schreibt eine e-mail
Bin mal gespannt ob es einer schaft ich Grübel schon seit 3 Wochen und habe es auch schon mit einer menge zusatzzellen probiert
Antwort 1 von Arnim
Hallo,
da Du die Löhne und Nachtzuschläge berechnen willst, nehme ich an, dass hier Dezimalwerte als Ergebnis stehen sollen.
Vorschlag:
Die Überschriften stehen in Zeile 1.
In D2 (unter Tagstunden) schreibe 20:00 und in E2 schreibe 08:00.
Formel in C3 ("gesamt"):
=WENN(B3="";"";REST(B3-A3;1)*24)
in D3 ("Tagstd):
=WENN(B3="";"";WENN((D$2<A3)+(E$2<B3);REST(B3-E$2;1)*24;REST(D$2-A3;1)*24))
in E3 (Nachtstd):
=WENN(B3="";"";(C3-D3))
Alle Formeln runterkopieren!
Gruß Arnim
PS: Bin in der Nacht heimgekommen und habe Dir geantwortet. Ich erhoffe einen kräftigen Nachtzuschlag! :-))
da Du die Löhne und Nachtzuschläge berechnen willst, nehme ich an, dass hier Dezimalwerte als Ergebnis stehen sollen.
Vorschlag:
Die Überschriften stehen in Zeile 1.
In D2 (unter Tagstunden) schreibe 20:00 und in E2 schreibe 08:00.
Formel in C3 ("gesamt"):
=WENN(B3="";"";REST(B3-A3;1)*24)
in D3 ("Tagstd):
=WENN(B3="";"";WENN((D$2<A3)+(E$2<B3);REST(B3-E$2;1)*24;REST(D$2-A3;1)*24))
in E3 (Nachtstd):
=WENN(B3="";"";(C3-D3))
Alle Formeln runterkopieren!
Gruß Arnim
PS: Bin in der Nacht heimgekommen und habe Dir geantwortet. Ich erhoffe einen kräftigen Nachtzuschlag! :-))
Antwort 2 von Sn01ow
Erst mal Danke an Dich das Du noch Nachts geantwortet hast,
auch einen Gruß an Pelle der mir gleich eine Tabelle geschickt hat.
Leider geht beides nur teilweise :(
Ich habe alles genau eingetippt bis auf 8 habe ich 6Uhr genommen
hier die ergebnisse.
Anfang Ende Gesamt Tag Std Nacht Std
20:00 06:00
18:00 07:00 13 1 12 f
17:00 07:00 14 1 13 f
18:00 06:00 12 2 10 OK
02:00 05:00 3 18 -15 f
02:00 07:00 5 1 4 OK
20:00 04:00 8 0 8 OK
18:00 02:00 8 2 6 OK
Vieleicht habt Ihr ja noch ein wenig zeit für eine verbesserung ich komme leider auch nicht mit euren formel weiter
Gruß Bernd
auch einen Gruß an Pelle der mir gleich eine Tabelle geschickt hat.
Leider geht beides nur teilweise :(
Ich habe alles genau eingetippt bis auf 8 habe ich 6Uhr genommen
hier die ergebnisse.
Anfang Ende Gesamt Tag Std Nacht Std
20:00 06:00
18:00 07:00 13 1 12 f
17:00 07:00 14 1 13 f
18:00 06:00 12 2 10 OK
02:00 05:00 3 18 -15 f
02:00 07:00 5 1 4 OK
20:00 04:00 8 0 8 OK
18:00 02:00 8 2 6 OK
Vieleicht habt Ihr ja noch ein wenig zeit für eine verbesserung ich komme leider auch nicht mit euren formel weiter
Gruß Bernd
Antwort 3 von pelle
Hallo Bernd,
sorry, habe dir eine falsche Version zugeschickt!
(Hau mich schon selber)!
Neue Tabelle mit deinen Beispielen ist unterwegs!
@Arnim
ist keine Geheimniskrämerei!
Die Formeln sind nur ziemlich komplex und es bestehen ein paar Hilfsspalten!
Sie hier zu posten, halte ich nicht für sinnvoll!
Habe die Tabelle, frech wie ich bin, auch mal geschickt!
gruss
pelle
sorry, habe dir eine falsche Version zugeschickt!
(Hau mich schon selber)!
Neue Tabelle mit deinen Beispielen ist unterwegs!
@Arnim
ist keine Geheimniskrämerei!
Die Formeln sind nur ziemlich komplex und es bestehen ein paar Hilfsspalten!
Sie hier zu posten, halte ich nicht für sinnvoll!
Habe die Tabelle, frech wie ich bin, auch mal geschickt!
gruss
pelle
Antwort 4 von Arnim
Hallo Bernd,
man sollte eben als Nachtheimkehrer ins Bett gehen anstatt Forenbeiträge zu beantworten! :-(
Ich habe mir mein Werk nochmals angesehen - ist ja furchtbar!!!
Nun eine andere Version ohne Hilfsspalte. Wenn Du willst, kann auch ich sie schicken.
So sieht es in etwa aus:
A1: Überschrift "Beginn"
B1: "Ende"
Spalten A und B im Formet hh:mm
C1: "Gesamtstunden"
D1:"Tag-Stunden"
E1: Nacht-Stunden
D2 und E2: Benutzerdefiniertes Format: "bis" hh:mm
In D2 schreibe dann 20:00 und in E2 06:00
Zeiten werden ab A3 und B3 eingetragen.
Formel in C3:
=WENN(B3="";"";REST(B3-A3;1)*24)
Formel in D3:
=WENN(C3="";"";C3-E3)
Formel in E3 (Nachtzeit):
=WENN(C3="";"";WENN(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3))>0,418;WENN(B3>D$2;(B3-D$2)*24;0);WENN(E$2>=A3;(E$2-A3)*24;(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3)))*24)))
Alle Formeln runterkopieren
Gruß Arnim
man sollte eben als Nachtheimkehrer ins Bett gehen anstatt Forenbeiträge zu beantworten! :-(
Ich habe mir mein Werk nochmals angesehen - ist ja furchtbar!!!
Nun eine andere Version ohne Hilfsspalte. Wenn Du willst, kann auch ich sie schicken.
So sieht es in etwa aus:
A1: Überschrift "Beginn"
B1: "Ende"
Spalten A und B im Formet hh:mm
C1: "Gesamtstunden"
D1:"Tag-Stunden"
E1: Nacht-Stunden
D2 und E2: Benutzerdefiniertes Format: "bis" hh:mm
In D2 schreibe dann 20:00 und in E2 06:00
Zeiten werden ab A3 und B3 eingetragen.
Formel in C3:
=WENN(B3="";"";REST(B3-A3;1)*24)
Formel in D3:
=WENN(C3="";"";C3-E3)
Formel in E3 (Nachtzeit):
=WENN(C3="";"";WENN(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3))>0,418;WENN(B3>D$2;(B3-D$2)*24;0);WENN(E$2>=A3;(E$2-A3)*24;(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3)))*24)))
Alle Formeln runterkopieren
Gruß Arnim
Antwort 5 von Arni
Hallo pelle,
ich danke Dir für die Mail nebst Anhang! Die Tabelle sieht sehr professionell aus! Ichwerde mich demnächst reinknieen.
Hallo Bernd,
durch die Mail von pelle bin ich darauf gestoßen, dass meine Mammutformel noch für den Fall, dass der Beginn vor 6:00 liegt, erweitert werden muss durch:
WENN(A3<E$2;(E$2-A3)*24
Es wird also noch mehr gewennt!:-(
Gesamtformel in E3 also:
=WENN(C3="";"";WENN(A3<E$2;(E$2-A3)*24;WENN(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3))>0,418;WENN(B3>D$2;(B3-D$2)*24;0);WENN(E$2>=A3;(E$2-A3)*24;(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3)))*24))))
Wenn ich mal Zeit und Muße habe, mache ich mich nochmals an dieses Ding dran!
Gruß Arnim
ich danke Dir für die Mail nebst Anhang! Die Tabelle sieht sehr professionell aus! Ichwerde mich demnächst reinknieen.
Hallo Bernd,
durch die Mail von pelle bin ich darauf gestoßen, dass meine Mammutformel noch für den Fall, dass der Beginn vor 6:00 liegt, erweitert werden muss durch:
WENN(A3<E$2;(E$2-A3)*24
Es wird also noch mehr gewennt!:-(
Gesamtformel in E3 also:
=WENN(C3="";"";WENN(A3<E$2;(E$2-A3)*24;WENN(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3))>0,418;WENN(B3>D$2;(B3-D$2)*24;0);WENN(E$2>=A3;(E$2-A3)*24;(1-MAX(D$2;A3)+WENN(B3-A3>0;B3;WENN(B3>E$2;E$2;B3)))*24))))
Wenn ich mal Zeit und Muße habe, mache ich mich nochmals an dieses Ding dran!
Gruß Arnim
Antwort 6 von Sn01ow
Erst mal ein großes Danke, auch wenn beide noch nicht so richtig funktioniert.
Ich habe euch beiden mal die Mustertabel geschickt,
aber ich Denke das es da keine Formel gibt die es perfekt macht,
ich schicke euch noch eine Tabele dann seht ihr auch wo ich sie brauche.
Gruß Bernd
Ich habe euch beiden mal die Mustertabel geschickt,
aber ich Denke das es da keine Formel gibt die es perfekt macht,
ich schicke euch noch eine Tabele dann seht ihr auch wo ich sie brauche.
Gruß Bernd
Antwort 7 von Arnim
Hallo Bernd,
ich habe Deine Zeitwerte in eine Tabelle eingetragen, zusäzlich zu anderen Zahlen, und sie Dir und pelle geschickt. M.E. kommen die richtigen Ergebnisse raus.
Gruß Arnim
ich habe Deine Zeitwerte in eine Tabelle eingetragen, zusäzlich zu anderen Zahlen, und sie Dir und pelle geschickt. M.E. kommen die richtigen Ergebnisse raus.
Gruß Arnim
Antwort 8 von pelle
Hallo Bernd,
hallo Arnim,
Info ist unterwegs!
Sorry Arnim, an einer Stelle passt deine Tabelle auch noch nicht so ganz!
gruss
pelle
hallo Arnim,
Info ist unterwegs!
Sorry Arnim, an einer Stelle passt deine Tabelle auch noch nicht so ganz!
gruss
pelle
Antwort 9 von Sn01ow
So nochmal Danke,
Pelle bei Dir sind einige sachen die ich noch nicht konnte und somit gleich bei mir intrigiert habe,
aber auch wie bei Arnim geht die Tabelle auch nicht so richtig sobald man das 3.mal ind den bereich von den Nachtstd kommt,
es kommt [ZENS] selten vor aber im Sicherheitsdienst macht man schon mal 24 STD
und bei beiden Tabelle und Formel kommt ein falsches Ergebniss bei Start 2:00 ende 1:00.
Bei Pelle = 23 Nachtstd.
Bei Arnim = 4 Nachtstd.
Aber ich bin froh es bring mich einiges weiter habe einfach ein Kommentar eingefügt nur tagweise die Schicht eintragen so funzt alles :)) freu
somit habe ich mal 8-10 Std weniger sortierarbeit im Monat
Gruß Bernd
DANKE AN EUCH BEIDE
Pelle bei Dir sind einige sachen die ich noch nicht konnte und somit gleich bei mir intrigiert habe,
aber auch wie bei Arnim geht die Tabelle auch nicht so richtig sobald man das 3.mal ind den bereich von den Nachtstd kommt,
es kommt [ZENS] selten vor aber im Sicherheitsdienst macht man schon mal 24 STD
und bei beiden Tabelle und Formel kommt ein falsches Ergebniss bei Start 2:00 ende 1:00.
Bei Pelle = 23 Nachtstd.
Bei Arnim = 4 Nachtstd.
Aber ich bin froh es bring mich einiges weiter habe einfach ein Kommentar eingefügt nur tagweise die Schicht eintragen so funzt alles :)) freu
somit habe ich mal 8-10 Std weniger sortierarbeit im Monat
Gruß Bernd
DANKE AN EUCH BEIDE
Antwort 10 von pelle
Hallo Bernd,
danke für deine Rückmeldung!
Meine Beispieltabelle habe ich auch für Jemanden
aus dem Bereich Sicherheitsdienst erstellt!
*g*
Er hatte einen besseren Arbeitgeber!
Keinen 24 STD Dienst!
Gibt es da nicht ein "Arbeitszeitgesetz", dass
solche Arbeitszeiten verbietet?
Wie sieht es da mit den gesetzlich vorgeschriebenen Pausenzeiten aus??
Diese Information habe ich bei deiner Fragestellung vermisst!
Bei einer solchen Konstellation, hätte ich die Tabelle
anders aufgebaut und die Formeln wären wesentlich
einfacher!
Du wolltest mir dochmal deine Tabelle schicken!
gruss
pelle
Ps:
> somit habe ich mal 8-10 Std weniger sortierarbeit im Monat <
trinke mal gerne ein Veltins!!! *g*
danke für deine Rückmeldung!
Meine Beispieltabelle habe ich auch für Jemanden
aus dem Bereich Sicherheitsdienst erstellt!
*g*
Er hatte einen besseren Arbeitgeber!
Keinen 24 STD Dienst!
Gibt es da nicht ein "Arbeitszeitgesetz", dass
solche Arbeitszeiten verbietet?
Wie sieht es da mit den gesetzlich vorgeschriebenen Pausenzeiten aus??
Diese Information habe ich bei deiner Fragestellung vermisst!
Bei einer solchen Konstellation, hätte ich die Tabelle
anders aufgebaut und die Formeln wären wesentlich
einfacher!
Du wolltest mir dochmal deine Tabelle schicken!
gruss
pelle
Ps:
> somit habe ich mal 8-10 Std weniger sortierarbeit im Monat <
trinke mal gerne ein Veltins!!! *g*