Excel Zeiten erfassen?
Hallo zusammen,
ich habe folgende Problem in Excel. Ich möchte einen gewissen Zeitraum aus Stundenaufzeichnungen in Tag- und Nachtzeit unterscheiden.
Dabei geht die Tagzeit von 06:00 - 20:00 Uhr und die Nachtzeit von 21:59 - 05:59 Uhr.
In B4 steht die Uhrzeit des Arbeitsbeginn und in C4 das Ende. Diese sind in Format Uhrzeit formatiert. Die entsprechenden Zahlenformate zu der Uhrzeit sind in AB4 (Beginn) und AC4 (Ende).
Nun möchte ich, wenn die Arbeitszeit z.b. von 04:00 Uhr - 23:00 Uhr geht, dass entsprechend richtig die Stundenanzahl in Tag und Nacht unterschieden wird. Die Uhrzeit kann auch tagesübergreifend sein z.B. 19:00 - 07:00 Uhr. dabei ist jede erdenkliche Möglichkeit innerhalb eines Tages (24 Stunden) möglich.
Beispiel:
Bei der Arbeitszeit von 19:00 - 07:00 Uhr sind 2 Tagstunden (19:00-20:00 + 06:00 - 07:00) und 10 Nachtstunden (20:00 - 00:00 + 00:00 - 06:00).
fehlerhafte Formel für die Tageszeit als Zahl in Zelle Z4:
=WENN(ODER(D4<>"A";AB4="";AC4="");0;WENN(UND(AC4<=AB4;AC4>=Adm!$F$7);(Adm!$F$8-B4)+(C4-Adm!$F$7);WENN(UND(AB4>=6;AC4<=20);(C4-B4);WENN(UND(AB4>=6;AC4>=20);Adm!$F$8-B4;WENN(UND(AB4<=6;AC4<=20);C4-Adm!$F$7;WENN(UND(AB4<=6;AC4>=20);Adm!$F$8-Adm!$F$7;WENN(AC4<AB4;WENN(UND(AC4<AB4;AC4<=6);(Adm!$F$8-B4);""))))))))
fehlerhafte Formel für die Nachtzeit als Zahl in Zelle AA4:
=WENN(ODER(D4<>"A";B4="";C4="");0;WENN(UND(AB4<6;AC4>20);(Adm!$F$7-B4)+(C4-Adm!$F$8);WENN(AB4<6;Adm!$F$7-B4;WENN(AC4>20;C4-Adm!$F$8;0))))
In der Zelle: "Adm!$F$7" steht als Uhrzeit formatiert 06:00 und "Adm!$F$8" steht die Uhrzeiten 20:00 Uhr.
Vielleicht hat jemand eine Idee und kann die Formeln korrigieren oder hat entsprechend einen besseren/richtigen Ansatz. Problem bei der ganzen Sache ist einfach, das hier auch Tagesübergreifend gearbeitet wird und entsprechend das Ende "kleiner" ist als der Anfang. Ich habe die Vermutung, dass mit "+1" gearbeitet werden muss, aber bin da leider raus.
Gruß
4 Antworten
Hi,
ein anderer Lösungsansatz, die obere Formel ist für die Tagstunden und die untere für die Nachtstunden. Funktioniert nur wenn die Gesamtarbeitsdauer kleiner als 24 Stunden ist, aber das scheint bei dir ja stets der Fall zu sein.
- =WENN(C4>B4;MAX(0;MIN($M$5-B4-MAX(0;$M$4-B4);C4-B4-MAX(0;$M$4-B4)));MAX(0;$M$5-B4-MAX(0;$M$4-B4))+MIN($M$5-$M$4;MAX(0;C4-$M$4)))
- =WENN(C4>B4;C4-B4;1-B4+C4)-WENN(C4>B4;MAX(0;MIN($M$5-B4-MAX(0;$M$4-B4);C4-B4-MAX(0;$M$4-B4)));MAX(0;$M$5-B4-MAX(0;$M$4-B4))+MIN($M$5-$M$4;MAX(0;C4-$M$4)))
- M4:= Arbeitszeitbeginn Tag ; M5 = Arbeitszeitende Tag
Zunächst wird geprüft ob es einen Datumsumsprung gibt. Ist es nur 1 Tag wird das Minimum der Differenzen vom Arbeitsende und dem Arbeitszeitende abzüglich evtl morgentlicher Nachtstunden bestimmt. Gibt es einen Datumsumsprung wird stattdessen die Summe aus der Differenz von Arbeitszeitende-Arbeitsbeginn (0 falls negativ) und der Differenz von Arbeitszeitbeginn-Arbeitsende (0, falls negativ, maximale Tagesstunden, falls größer als diese) gebildet.
Die Nachtstunden werden als Differenz von Gesamtarbeitzeit und den auf die gleiche Art bestimmten Tagstunden ermittelt.
Ich habe es nicht ausprobiert, aber entweder du speicherst das Datum mit der Uhrzeit oder du musst in irgendeiner Form eine Abfrage einbringen, dass wenn die Endzeit kleiner ist als die Startzeit (24:00:00-Startzeit)+Endzeit gerechnet werden muss.
Wie werden die Uhrzeiten denn erfasst?
Wenn zu dem Zeitpunkt, zu dem erfasst wird, das aktuelle Datum mitgespeichert wird (TT.MM.JJJJ hh:mm:ss), dann braucht man auch keine Krücken mit dem Jahr 1900.
Dann hast du einmal einen Zeitstempel 06.04.2021 12:00:00 und einen Zeistempel 07.04.2021 02:00:00. Excel rechnet intern mit den Datumswerten in Fließkommazahlen und kann diese ganz normal subtrahieren.
(Man muss nur einmal überprüfen, ob die Einstellung Datei → Optionen → Erweitert → Beim Berechnen dieser Arbeitsmappe → 1904-Datumswerte verwenden korrekt gesetzt ist.)
Und meinen Gegenvorschlag scheinst du nicht richtig verstanden zu haben.
Wenn nur die Uhrzeit gespeichert wird, kannst du überprüfen, ob die Endzeit kleiner ist als die Startzeit. In deinem Fall ist 02:00:00 kleiner als 12:00:00. Ist dies der Fall, weißt du, dass mindestens ein Tageswechsel dazwischen liegt und wenn nur weniger als ein Tag dazwischen liegen darf, dann weißt du im umgekehrten Fall, dass kein Tageswechsel stattgefunden hat.
Also die Zeit wird in HH:MM:SS als benutzerdefiniert wiedergegeben. Ich werde die von dir genannte Prüfung mal versuchen einzubauen.
Du könntest folgendes machen um die Nachtstunden/Tageswechsel zu ermitteln:
z.B. in A1 (... oder sonst wo) 24:00 Stunden eintragen... und danach Bezug darauf nehmen ...
So bekomme ich die Gesamtzeit raus, dies habe ich schon. Ich brauche entsprechend eine Aufteilung der Gesamtzeit zwischen Tag- und Nachtzeit
Die Zeiten kann man ja in einer erweiterten Formel unterbringen.
Mein Vorschlag hatte das Ziel, die Problematik bei Excel/Tageswechsel zu übergehen/lösen.
Hallo, das ist eine sehr interessante Frage, die nicht leicht zu lösen ist.
Ich hoffe der Tag hat kein Loch, was ist zwischen 20:01 und 21:59= Wahrscheinlich Tippfehler, ist aber egal. Ich gehe davon aus, dass 6-20 Uhr Tag ist und der Rest Nacht.
Systematisch ginge ich es so an:
Der Mitarbeiter kann in 3 Zonen kommen und in 3 Zonen gehen, das sind:
Zone1 (00:00 - 05:59 Uhr)
Zone2 (06:00 - 20:00 Uhr)
Zone3 (20:01 - 00:00 Uhr)
Also gibt es theoretisch 3 Beginn-Zonen mal 3 Ende-Zonen, also 9 Fälle.
Die Zelle B2 (Tagbeginn) habe ich "Tag" getauft, C2 hat den Namen "Nacht".
Den Fall (Zelle F4 und runterkopieren) ermittle ich so:=3*WENN(STUNDE(B4)<6;0;WENN(STUNDE(B4)>20;2;1))+WENN(STUNDE(C4-1/24/3600)<6;1;WENN(STUNDE(C4-1/24/3600)>20;3;2))
Das Glied "-1/24/3600" musste ich nur für den Fall einbauen, wenn das Arbeitszeitende genau 00:00 Uhr ist , dann wird 1 Sekunde abgezogen, damit die Rechnung passt.
Die Tages-Arbeitszeit in allen 9 Fällen (Zelle G4 und runterkopieren):
=WAHL(F4;0;C4-Tag;Nacht-Tag;Nacht-B4;C4-B4;Nacht-B4;0;C4-Tag;0)
Die Nacht-Arbeitszeit in allen 9 Fällen (Zelle H4 und runterkopieren):
=WAHL(F4;C4-B4;Tag-B4;Tag-B4+C4-Nacht;1-Nacht+C4;0;C4-Nacht;B4-Nacht+C4;B4-Nacht+Tag;C4-B4)
Vielen Dank für deine Antwort, freut mich dass ich dich hoffentlich etwas herausfordern konnte. Ich bin bei der Aufgabe nämlich etwas am verzweifeln. Ich werde es mal ausprobieren.
Oh, natürlich ist die Tagzeit von 06:00 - 20:00 und die Nachtzeit von 20:01 - 05:59. Da hatte ich einen Tippfehler.
Die neun Fälle klingen logisch und plausibel, aber könnte doch theoretisch auch der Fall: 03:00 Uhr - 02:00 Uhr sein sprich mit tageswechsel. Dieses wäre nicht berücksichtigt oder habe ich da gerade ein Denkfehler
Hallo hannes1806
ich habe es mal getestet, jedoch treten folgende Fehler bei folgenden Konsultationen auf.
* Beginn: beliebig - Ende: 00:00
--> Im der Spalte Fall, sowie Tag und Nacht wird als Wert #zahl! ausgegeben.
* Beginn: beliebig - Ende: 21:00
--> Die Anzahl der Nachtzeit wird falsch berechnet. Die Nachtzeit beginnt ab 20:01. Im Beispiel 19:00 - 21:00 Uhr wird 2 Std Tagstunden gerechnet., wobei 1 Std & 1 Std korrekt wäre. Sofern die Nachtzeit im nächsten Tag liegt (01:00 Uhr), wird die Zeit korrekt berechnet.
* Beginn: 19:0 Uhr - Ende 07:00 Uhr-
* --> Es wird Fall 5 wiedergegeben, die Nachtzeit wird als 0 angezeigt und die Tagzeit als ###### wiedergegeben (Zelle nicht zu kurz)
* Beginn: 03:00 Uhr - Ende 02:00 Uhr
* --> Es wird Fall 1 wiedergegeben, die Tagzeit mit 0 gerechnet und die Nachtzeit mit #######
M.E.n. gibt es auch 12 Fälle -->
Fall: Beginn Ende
1 03:00 – 04:00
2 03:00 - 18:00
3 03:00 – 22:00
4 03:00 – 01:00
5 18:00 – 19:00
6 18:00 – 22:00
7 18:00 – 04:00
8 18:00 – 08:00
9 22:00 – 23:00
10 22:00 - 04:00
11 22:00 – 18:00
12 22:00 – 21:00
Hallo RobinM66 du hast in allem Recht, ich war zu voreilig. Aber wir kommen der Sache schon näher. Ich schau mir das nochmal in Ruhe an.
Aber wie würde denn Excel erkennen, wenn die Zelle entsprechend mit Datum und Uhrzeit gespeichert wird, dass es der Folgetag ist.
Eine Eingabe sollte entsprechen: 12:00 und dann 02:00 sein.
Dabei muss dann erkannt werden, dass 12:00 --> 01.01.1900 ist und 02:00 --> 02.01.1900 ist.