Excel Nachtstunden berechnen?
Moin, moin,
ich habe in einer Tab mit Stundenberechnungen das Problem, dass eine Schicht schon mal vor 22:00 Uhr beginnt. Also z.Bsp. ab 20:00 Uhr. Nachtstunden aber nur zwischen 22:00 - 6:00 Uhr bezahlt werden. Die "Normalstunden" ist kein Problem, die errechne ich mit der "Restformel". Aber wenn eine Schicht früher anfängt, hab ich keinen Plan wie man das lösen kann. Würde gerne einen Ausschnitt der Tabelle mal hier einfügen weis aber nicht wie man das macht.
Für einen guten Tipp wäre ich Euch dankbar
Gruss Jo
2 Antworten
=Rest(Endzeit-Anfangszeit;1)
[ anders formuliert: =Rest(Gehen-Kommen;1) ]
geht bis <24 h auch über Mitternacht
Zahlen-Beispiel
(Die Zeiten können aber auch ein Bezug sein):
=Rest("6:15"-"21:45";1) ergibt 8:30
=REST("16:15"-"8:05";1) ergibt 8:10, geht also auch den ganzen Tag.
Um die Formel zu verstehen, muss man sich mit dem Verhalten von Rest bei positiven und auch bei negativen Zahlen befassen.
Hallo lamiam,
ja du hast recht, muss ich mich für entschuldigen. Hab die letzten Tage ziemlich zu tun gehabt, habe aber jetzt hilfreich angeklickt. Doch ja der Tipp von DeeDee war Super.
Da hätt' ich noch eine Frage. In meiner Stunden-Tab ändere ich den Monat jeweils über eine Dropdownliste auf den neuen Monat. In einer Jahresübersicht der Stunden sind aber bei Änderung die Daten vom Vormonat weg, oder?
Wie könnte ich das denn lösen?
Danke für einen Vorschlag Gruss Jo.
ad 1: Hilfreich und die Hilfreichste sind zweierlei. Letzteres nennt man auch den Stern. (wird leider nur bei mindestens 2 Antworten angeboten. Kann aber sein, dass sich mit dem neuen Design da was prinzipiell geändert hat)
ad 2: Damit machst du ein völlig neues Fass auf. Die Lösung geht m.E. nur, indem du das Blatt kopierst und für jeden Monat ein neues anlegst, so dass Du Jahresdateien mit 12 Monatsblättern bekommst.
Eine andere Lösung könnte sein, die Datümer fortlaufend zu generieren und jeweils Monatsabschnitte zu erstellen. Dann brauchst du auch keine Dropdown-Liste, denn die Datümer sind dann ja schon vorgegeben und eine Auswertung kann Monatsnamen u.v.a.m. liefern.
Ist besser, wenn man viele MA hat, dann kann man zB mit Spaltenbereichen pro MA arbeiten, dazu Titelzeilen und -Spalten fixieren zum frei navigieren. Mit den heutigen Bildschirmen sollte dafür Platz genug sein, wenn man nicht gerade Seniorenschriftgrößen verwendet.
Stell ggf eine neue Frage.
Es gibt da aber schon recht viel im Netz, auch auf GF!
Annahme: Anfangszeit in A1, Endzeit in B1
Die einfache Restformel sieht bisher so aus
=REST(B1-A1;1)
Die darin enthaltenen Nachtstunden zwischen 22:00 und 6:00 berechnest du mit der erweiterten Formel
=REST(MIN(6/24;B1)-MAX(22/24;A1);1)
Sie funktioniert allerdings nur, wenn Arbeitsbeginn immer vor Mitternacht ist. Ansonsten muss man sie nochmal um eine Prüfung erweitern.
Hab wieder mal sowohl Frage wie Deine AW nur oberflächlich gelesen und gedacht: warum so kompliziert? Dass die Komplikation durch den Fragenzusatz "nur Nachtstunden" notwendig wird, hab ich erst nach Beantwortung und nochmaligem Durchlesen geschnallt.
Aber so ist es wenigstens eine zweite AW für eine Hilfreichste!
Hallo DeeDee07. Erstmal vielen Dank für die schnelle Antwort. Konnte allerdings erst heute Morgen mal ausgiebig testen. Deine Formel funktioniert ganz toll, wie du aber schon sagtest nur vor 0:00 Uhr. Speditionen sind natürlich rund um die Uhr unterwegs. Somit sind auch Anfangszeiten vor und nach 0:00 Uhr üblich. Jetzt meine Frage: wenn möglich die Formel so abzuändern das auch eine Anfangszeit nach 0:00 Uhr berücksichtigt wird. Muss nicht sofort sein, wie du Zeit u. Lust dazu hast. Danke schon mal. Gruss Jo
Puh, gar nicht so einfach... nach einigem Tüfteln (meine 1. Formel hatte noch weitere Schwächen) für jede Eventualität bin ich auf diese - immer längere und weniger elegante - Lösung gekommen:
=WENN(UND(A1>=6/24;B1<=22/24;B1>A1);0;REST(WENN(B1>22/24;B1;MIN(6/24;B1))-WENN(A1<6/24;A1;MAX(22/24;A1));1))
etwas leserlicher:
=WENN(
UND(A1>=6/24;B1<=22/24;B1>A1);
0;
REST(
WENN(
B1>22/24;
B1;
MIN(6/24;B1))
-
WENN(
A1<6/24;
A1;
MAX(22/24;A1));
1)
)
Dennoch gibt es einen Sonderfall, wo sie versagt; nämlich wenn die Anfangszeit und Endzeit in der Nacht liegt, aber die ganze Tagzeit dazwischenliegt, z.B. 05:00 bis 23:00 Uhr. Ist das relevant?
Teste sie bitte genau.
Vielleicht fällt noch jemandem eine bessere und elegantere Lösung ein.
:-)) Hallo Deedee. Hab grade geschaut und getestet. Jou so muss das sein.
Muss aber jetzt nochmal aus dem Haus und werde die Formel nachher in die Zellen eingeben. Jetzt auf die schnelle nur mal in einer Zeile getestet und um das Ergebnis als ganz Zahl zu erhalten, am Ende noch *24 erweitert. Und schon war's so wie ich mir das gedacht habe. Ist aber auch 'ne lange Formel. Puhh ja.
Vielen lieben Dank dafür. Gruss Jo
Moin DeeDee07 :-))))
also, hab die Formel ins ganze Blatt eingegeben und rauf und runter mit sämtlichen Zeiten getestet die vorkommen könnten. So ist das Super.
Konnte keine Fehler feststellen. Suuuuper. Vielen Dank. Ich muss neidisch zugeben das hätte ich so nicht hingekriegt.
LG Jo
Warum vergebt ihr alle keine Hilfreichste? Nur die erhalten den Expertenstatus. War die von DeeDee dir nicht gut genug?
Ich finde es einfach ungehörig, das nicht zu tun!