Formel für eine Excel-Tabelle gesucht ( Zeit- / Stunden- Berechnung )
Hallo Zusammen,
Ich benötige in einer Excel-Tabelle eine Zelle zur Berechnung bestimmter Arbeitsstunden an einem Arbeitstag der nur Samstags ist.
Die Arbeitszeit kann z.B. von 6:00 h bis 24:00 h gehen kann aber auch z.B. von 14:00 h bis 18:00 h gehen. Ich benötige aber nur die genaue Stunden/Minuten Angabe in der Zeit von 13:00 h bis 21:00 Uhr.
Als Beispiel: Ich Arbeite von 8:00 h bis 23:00 h, dann würde ich in der Zeit von 13-21h 8 Stunden Arbeiten. Oder von 14- 22h dann würde ich nur 7 Std. in der Zeit von 13-21h arbeiten. Oder von 7-19h dann würde ich nur 6 Stunden in der Zeit von 13-21h arbeiten. Und wie schon oben geschrieben, soll diese Berechnung nur für die Samstage sein. deshalb auch die erste Zelle von der Tabelle Wochentag und damm das Datum... siehe unten.
Die Tabelle hat die Zellen:
Tag – Datum – Arbeitsbeginn – Arbeitsende - Ges. Stunden – Arbeitsstunden 13-21 Uhr Montag - 11.11.13 - 10:00 - 23:00 - 13 Std. ???
Ich hoffe es kann mir jemand weiter helfen!
Vielen Dank Uwe
5 Antworten
So ich hab jetzt noch mal ne Version22. Die geht jetzt über Mitternacht hinaus. Die letzte Version war nur bis Mitternacht gedacht. Theoretisch müßte man eigentlich sagen. Der Tag geht bis Mitternacht. Und was dann darüber hinaus geht müßte in die nächste Zeile. Also nächstfolgende Datum, weil ja um Mitternacht Datumswechsel ist. Aber nun gut. Schadet ja auch nicht, wenn die Formel stimmt. Teste mal die neue Version. Sollte jetzt bis "23 Stunden 59 Minuten" theoretische Arbeitszeit gehen. Also z.B. von 12:00 Uhr bis 11:59 Uhr. Das wären dann 23 Std. 59 Min. Mußte da auch die Formel für die Ges.Std. in "Spalte E" etwas verändern.
http://www.dateiupload.net/download.php?file=370f93ea24a17f6238c4bd3710bd6a7a
Hallo merkurus... würdest du mir bitte noch eine Formel für meine Exceltabelle berechnen?? Habe sie Dir Grün gekenzeichnet. Die Berechnung darf nicht für Sonntag sein, also nur von Montag bis Samstag in der Zeit von 21h - 6h, also auch wieder über 0 Uhr. Schau dir doch mal die Formel an, die ich Rot gekenzeichnet habe, ob ich die so lassen kann? Die Tabelle ist ab Zeile 9 fixiert, nur als Hinweis. Würde mich sehr freuen, wenn du mir nochmal hilfst! Vielen Dank Uwe Tabelle: http://www.dateiupload.net/download.php?file=9aaab8261b5226598103ac4af2829473
Hab jetzt noch einen klitzekleinen Fehler gefunden. Die Formel für Spalte E, falls du die verwendest. Nachfolgend die geänderte Formel für E7 .Die kannst dann nach unten bzw. oben kopieren.
=WENN((STUNDE(D7)*60)+(MINUTE(D7))>=(STUNDE(C7)*60)+(MINUTE(C7));D7-C7;((ZEIT(23;59;0)+ZEIT(0;1;0))-C7)+ZEIT(STUNDE(D7);MINUTE(D7);SEKUNDE(D7)))
Bei Zeit 23 59 0 hatte ich geschrieben Zeit 23 59 1 . Das sind 1 Sekunde Fehler. Könnte beim Zusammenzählen von Stunden Probleme machen. Dies bitte noch ändern. Bei normaler Stunden Minuten Formatierung sieht man das halt nicht.
Lad dir mal folgende Exceldatei runter. Darin befindet sich die Tabelle "ZeitStunden". Nimm das mal als Beispiel und probier halt mal mit den Zeiten, obs da noch Probleme gibt.
http://www.dateiupload.net/download.php?file=2cec72c01e791bf70bba9ea12d511449
Wenn der Wochentag in Spalte A, Arbeitsbeginn in C und -ende in D steht, lautet die Formel
=WENN(A2="Samstag";WENN(D2>21/24;21/24;D2)-WENN(C2<13/24;13/24;C2);"")
Die Spalte mit dieser Formel sollte ebenfalls im Uhrzeitformat sein.
Die Formel ist allerdings nicht idiotensicher, so darf Arbeitsbeginn nicht nach 21:00 sein, Arbeitsende nicht vor 13:00. Melde dich, falls das berücksichtigt werden muss.
Hallo DeeDee07,
vielen Dank für Deine schnelle Hilfe, sicher kann es sein, wie du es schon geschrieben hast, dass auch die Arbeitszeiten "Beginn" nach 21h bzw Arbeitsende vor 13h sein kann. Hoffe auf eine weiter Lösung. Die Zellen sind alle im [h]:mm Format bis auf den Wochentag der ist als Textformat.
Spalte A Wochentag, Spalte B Datum, Spalte C Arbeitsanfang, Spalte D Arbeitsende, Spalte E Ges. Arbeitszeit, Spalte F Arbeitszeit 13-21 Uhr
Grüße Uwe
Ok, dann mit zusätzlicher Prüfung von Beginn und Ende:
=WENN(A2="Samstag";WENN(ODER(D2<13/24;C2>21/24);"";WENN(D2>21/24;21/24;D2)-WENN(C2<13/24;13/24;C2));"")
Ich hab jetzt noch mal eine verbesserte Version zum Download. Die erste Version war Fehler behaftet. Sollte jetzt besser klappen. Auch z.B. Eingabe mit 24 Uhr, Was ja so ein Problem ist im Allgemeinen bei Excel
http://www.dateiupload.net/download.php?file=cb94e23270d1c74f3ce800b74a9d4abd
Also echt SUPER die Antwort meine natürlich die Formel !!! Habe die Formel auch gleich getestet und allse bestens.
Bis auf einen kleinen Fehler aber das werde ich dann von Hand eingeben, da es so denke ich nicht so oft sein wird.
wenn Beginn z.B. 18:00h ist und Ende 2:00h (Morgens) dann kommen auch nur ### in den Zellen. Gibt es dafür auch noch eine Lösung? :-)))))
Sonst alles SUPER UWE
Wenn ich Dich richtig verstanden habe, sollte eigentlich:
=MIN(D2;21/24)-MAX(C2;13/24)
klappen.
Oder?
perfekt
Hallo.... Deine Formel die du mir ins Forum geschrieben hast ist eigendlich sehr gut !!! Nur bei einer Eingabe bei einer Anfangszeit 7h und Endzeit 12h kommt nur #### und bei Anfangzeit 22h bis 24h auch. Kann man das noch ändern das z.B. nur 0:00 oder nichts da steht. Und diese Anwendung soll sich nur auf den Samstag beziehen. ... Sonst SUPER Gruße Uwe
Hallo nochmal...
mit dem Samstag habe ich schon, so denke ich, selber herausbekommen. Hoffe das ist so richtig.
=WENN(B4="Samstag";MIN(E4;21/24)-MAX(D4;13/24);"0:00")
Meine Zellen sind hier etwas anderst, da ich sie aus der Exceltabelle kopiert und hier eigefügt habe.
Grüße Uwe
G4: =WENN(WOCHENTAG(C4;1)=7;MAX(MIN(E4;21/24)-MAX(D4;13/24);0);"")
in Spalte B steht ja bestimmt nicht das Wort "Samstag" sondern der 09.11.13 als TTTT formatiert.
Das Lob gilt dir, auf "=MIN(D2;21/24)-MAX(C2;13/24)" wäre ich nie im Leben gekommen.
Ohjeh... jetzt Blicke ich nichts mehr... sorry... bin da kein Excel-Profi
Bei mir steht der Wochentag als Wort und ist als Text formatiert sonst kann ich nicht noch andere Wochentage oder den Sonntag berechnen. Weis sonst nicht wie ich das eingeben soll.
Was bedeutet DH!????
Gruß Uwe
Aber in Spalte C steht ja ohnehin das Datum also sollte meine Formel funktionieren, egal was in Spalte B steht.
Hallo Merkurus...
SUPER SPITZE... diese Formel !!! Ich habe sie gleich mal in meine Tabelle eingefügt und siehe da, es wird alles autom. berechnet.
Wenn in Anfang-/End-Zeit keine Uhrzeit steht kommt zwar eien Fehlermeldung in der Zelle (#WERT) aber da gebe ich dann einfach bei Anfang-/End-Zeit eine 0:00 ein und dann ist alles ok.
Vielen Dank für deine SUPER Hilfe. Wenn es hier Sterne geben würde dann hättest du volle Anzahl bekommen 5 Sterne Plus ;-))
Grüße Uwe