Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Arbeitszeit berechnen
#1
In einem Datensatz sind Melde- und Lösungszeitpunkt im Datums-und Uhrzeit Format dd.mm.yy mm:hh angegeben. Daraus ergibt sich eine Bruttozeit von Lösungzeitpunkt-Meldezeitpunkt. Diese Bruttozeit ist jedoch leicht verfälscht, da sie nicht die tatsächliche Arbeitszeit bzw. Anwesenheit von 8-17 Uhr werktags berücksichtigt. Ich möchte jedoch nur die tatsächliche Arbeitszeit berechen.

Um mein Problem an einem Beipspiel deutlich zu machen:
Meldezeitpunkt
A1=04.05.2015 7:15
Lösungszeitpunkt
A2=A1=04.05.2015 16:15

Die Lösung sollte hier 8:15 h (16:15-8:00) betragen und nicht 9 h.

Bisher habe ich =NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;Freie_Tage) benutzt. Die Formel berücksichtigt also nur die Feiertage und Wochenende. Wie kann ich jedoch eine Gültigkeitsregel für Melde- und Lösungszeitpunkt von 7-18 Uuhr bestimmen?

Vielen Dank im Voraus!
Antworten Top
#2
Hi,
du solltest uns erst einmal erklären, was Melde— und Lösungspunkte sind. Das hat sicherlich niemand begriffen.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#3
Hi,

ich glaube, dass ich dein Problem jetzt verstanden. Schreibe in zwei Hilfsspalten folgende Formeln:
Zitat:=WENN(A1<"08:00";"08:00";A1) z.B  in D1 und
=WENN(B1>"17:00";"17:00";B1) z. B. in E1

In A1 kommt dann deine Anfangszeit; in B1 deine Endzeit. In C1 schreibst du
Zitat:=REST(E1-D1;1)

Bin am Handy, kann deshalb kein Beispiel Posten.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#4
Hi,

ich hatte am Handy leider nicht die Möglichkeit, meinen Vorschlag zu testen. Das habe ich jetzt nachgeholt. Siehe Tabellenausschnitt.

Tabelle1

ABCDE
1BeginnEndeGesamtstd.HS BeginnHS Ende
207:1516:1508:1508:0016:15
307:5917:0109:0008:0017:00
408:0116:5908:5808:0116:59
508:3008:30
6
726:13
Formeln der Tabelle
ZelleFormel
C2=WENNFEHLER(REST(E2-D2;1);"")
D2=WENN(A2="";"";WENN(A2<"08:00"*1;"08:00"*1;WENN(A2>"08:00"*1;A2;A2)))
E2=WENN(B2="";"";WENN(B2>"17:00"*1;"17:00"*1;WENN(B2<"17:00"*1;B2;B2)*1))
C3=WENNFEHLER(REST(E3-D3;1);"")
D3=WENN(A3="";"";WENN(A3<"08:00"*1;"08:00"*1;WENN(A3>"08:00"*1;A3;A3)))
E3=WENN(B3="";"";WENN(B3>"17:00"*1;"17:00"*1;WENN(B3<"17:00"*1;B3;B3)*1))
C4=WENNFEHLER(REST(E4-D4;1);"")
D4=WENN(A4="";"";WENN(A4<"08:00"*1;"08:00"*1;WENN(A4>"08:00"*1;A4;A4)))
E4=WENN(B4="";"";WENN(B4>"17:00"*1;"17:00"*1;WENN(B4<"17:00"*1;B4;B4)*1))
C5=WENNFEHLER(REST(E5-D5;1);"")
D5=WENN(A5="";"";WENN(A5<"08:00"*1;"08:00"*1;WENN(A5>"08:00"*1;A5;A5)))
E5=WENN(B5="";"";WENN(B5>"17:00"*1;"17:00"*1;WENN(B5<"17:00"*1;B5;B5)*1))
C6=WENNFEHLER(REST(E6-D6;1);"")
D6=WENN(A6="";"";WENN(A6<"08:00"*1;"08:00"*1;WENN(A6>"08:00"*1;A6;A6)))
E6=WENN(B6="";"";WENN(B6>"17:00"*1;"17:00"*1;WENN(B6<"17:00"*1;B6;B6)*1))
C7=SUMME(C2:C6)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die in meinem vorigen Beitrag genannten Formeln mussten ergänzt werden. In den blau hinterlegten Hilfsspalten, die du ausblenden kannst, werden die anzurechnenden Zeiten geschrieben; die Formel in der Spalte C bezieht sich auf diese Zeiten. Ziehe die Formeln einfach so weit runter, wie du sie benötigst.

Mit den Ergebnissen kannst du ganz normal weiterrechnen - siehe in Rot die addierten Stunden.

Oder ohne Hilfsspalten:


=WENNFEHLER(REST(WENN(B2="";"";WENN(B2>"17:00"*1;"17:00"*1;WENN(B2<"17:00"*1;B2;B2)*1))-WENN(A2="";"";WENN(A2<"08:00"*1;"08:00"*1;WENN(A2>"08:00"*1;A2;A2)));1);"")
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#5
Hallo,

einfacher:


Code:
=min(A2;16,25/24)-max(a1;1/3)

Allerdings wird das mit Nettoarbeitstage so nicht funktionieren! Ein paar mehr Infos wären notwendig!
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#6
Vielen Dank schon mal für die kompetenten Beiträge. WillWissen hat die Problematik bereits gut erfasst.

Kurz zum Hintergrund des Problems. Es soll handelt sich um ein Fehlermedlungssystem. Der Kunde kann uns jederzeit bei Problemen kontaktiert. Im System geht dann die Fehlermeldung zum "Meldezeitpunkt" ein. Ist das Problem von uns gelöst und wird dem Kunden geantwortet, wird in dem System der "Lösungszeitpunkt" vermerkt. Aus Controlling Sicht möchte ich nun überprüfen, wie lange wir durschnittlich an einer Fehlermeldung arbeiten. Jedoch soll dabei nur die reine Anwesenheit bzw. Arbeitszeit von 8-17 Uhr berücksichtig werden. Also werden Feiertage und Wochenenden ebenfalls nicht berücksichtigt.

WillWissens Formel ist bereits ein sehr guter Ansatz. Jedoch erhalte ich in E = HS Ende immer 17:00. Die Formel müsste analog wie für D = HS Beginn funktionieren, konnte meinen Fehler nicht entdecken. Sieht wer das Problem? siehe Abbilddung:    

Weiterhin ist es möglich, dass der Meldezeitpunkt bei 17:44 und der Lösungszeitpunkt am nächsten Tag bei 09:15 Uhr liegt. Berücksichtigt dies die Formel von WillWissen?    

Und wie können freie Tage, also Wochenenden und Feiertage berücksichtigt werden? Dies hatte ich mit dem Ansatz =NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;Freie_Tage) versucht zu lösen.

Vielen Dank an die klugen Köpfe für die vielen Fragen!
Antworten Top
#7
Hi,

stelle doch bitte mal deine Datei zur Verfügung - wie du an meinem Beispiel gesehen hast, sieht ein Nachbau immer anders aus. Und eine Datei nach einem Bildchen nachbauen mag niemand.

So kannst du Dateien hochladen. (Klick aufs Rote)

Und so ganz habe ich das jetzt mit deinen Melde- und Lösungspunkten nicht verstanden. Bin jetzt aber nur noch ca. 1 Stunde am Rechner und dann erst wieder ab 22:30. Und auf meinem Handy kann ich leider nicht effektiv arbeiten.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#8
Hallo,

würde ich so angehen (Arbeitszeit v. 8:00 bis 17:00):

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCD
1AnfangEnde  
221.04.2015 17:4422.04.2015 09:151,251:15
321.04.2015 17:4423.04.2015 09:1510,2510:15

ZelleFormel
C2=MAX(0;NETTOARBEITSTAGE($A2+1;$B2-1))*9+MAX(0;17/24-MAX(1/3;REST($A2;1)))*24+MAX(0;MIN(17/24;REST($B2;1))-1/3)*24
D2=MAX(0;NETTOARBEITSTAGE($A2+1;$B2-1))*3/8+MAX(0;17/24-MAX(1/3;REST($A2;1)))+MAX(0;MIN(17/24;REST($B2;1))-1/3)
C3=MAX(0;NETTOARBEITSTAGE($A3+1;$B3-1))*9+MAX(0;17/24-MAX(1/3;REST($A3;1)))*24+MAX(0;MIN(17/24;REST($B3;1))-1/3)*24
D3=MAX(0;NETTOARBEITSTAGE($A3+1;$B3-1))*3/8+MAX(0;17/24-MAX(1/3;REST($A3;1)))+MAX(0;MIN(17/24;REST($B3;1))-1/3)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#9
Ich habe nun einen Ausschnitt der Excel Liste hochgeladen, kommentiert und mit der manuell ausgerechneten Musterlösung und den bisher vorgeschlagenen Formelansätzen ergänzt.
Unter der Arbeitszeit sollen die tatsächlich angefallenen Stunden, in der Anwesenheitszeit von 8-17 Uhr berechnet werden (siehe Musterlösung). Lösungs- und Meldezeitpunkte könne außerhalb dieser Arbeitszeit von 08-17 Uhr liegen. Deshalb muss dann von den Zeitgrenzen morgens 8 Uhr an bis nur 17 Uhr gerechnet werden. Manchmal liefert das System auch keine Zeitpunkte, sodass diese beiden Felder leer bleiben und es keine Lösung gibt.


Angehängte Dateien
.xlsx   Zeiten.xlsx (Größe: 18,21 KB / Downloads: 12)
Antworten Top
#10
Hallo, hier in diesem Ausschnitt unten (aus deiner Datei) gibt es nur ein Datum..!!! Wenn die ersten beiden auch Text sein sollen, wie das in B5 brauchst du die Klammern nicht, wenn es ein richtiges Datum sein soll, reicht ..:

Arbeitsblatt mit dem Namen 'Feiertage'
F
201.01.2014
301.05.2014

ZelleFormatWert
F2TT.MM.JJJJ01.01.2014
F3TT.MM.JJJJ01.05.2014

ZelleFormel
F2=(1&-A2)+0
F3=(5&-A2)+0
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen 'Feiertage'
B
201.01.2014
301.05.2014
418.04.2014
520.04.2014

ZelleFormel
B2=("01.01.")&$A$2
B3=("01.05.")&$A$2
B4=$B$5-2
B5=TEXT(WENN(D7-31 < 1;D7;D7-31);"0#")&"."&WENN(D7 > 31;"04.";"03.")&$A$2
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste