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!
Hi,
du solltest uns erst einmal erklären, was Melde— und Lösungspunkte sind. Das hat sicherlich niemand begriffen.
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.
Hi,
ich hatte am Handy leider nicht die Möglichkeit, meinen Vorschlag zu testen. Das habe ich jetzt nachgeholt. Siehe Tabellenausschnitt.
Tabelle1 | A | B | C | D | E |
| 1 | Beginn | Ende | Gesamtstd. | HS Beginn | HS Ende |
| 2 | 07:15 | 16:15 | 08:15 | 08:00 | 16:15 |
| 3 | 07:59 | 17:01 | 09:00 | 08:00 | 17:00 |
| 4 | 08:01 | 16:59 | 08:58 | 08:01 | 16:59 |
| 5 | 08:30 | | | 08:30 | |
| 6 | | | | | |
| 7 | | | 26:13 | | |
| Formeln der Tabelle |
| Zelle | Formel | | 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);"")
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!
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: [
attachment=2118]
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? [
attachment=2119]
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!
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.
Hallo,
würde ich so angehen (Arbeitszeit v. 8:00 bis 17:00):
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C | D |
| 1 | Anfang | Ende | | |
| 2 | 21.04.2015 17:44 | 22.04.2015 09:15 | 1,25 | 1:15 |
| 3 | 21.04.2015 17:44 | 23.04.2015 09:15 | 10,25 | 10:15 |
| Zelle | Formel |
| 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 |
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.
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 |
| 2 | 01.01.2014 |
| 3 | 01.05.2014 |
| Zelle | Format | Wert |
| F2 | TT.MM.JJJJ | 01.01.2014 |
| F3 | TT.MM.JJJJ | 01.05.2014 |
| Zelle | Formel |
| 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 |
| 2 | 01.01.2014 |
| 3 | 01.05.2014 |
| 4 | 18.04.2014 |
| 5 | 20.04.2014 |
| Zelle | Formel |
| 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 |