Uhrzeiten automatisch zuordnen

fuhri1 · 2. November 2009 um 12:58

Hallo liebe Gemeinde,

ich habe folgendes Problem, welches ich ohne VBA lösen möchte, wo ich aber einfach nicht weiter komme.

Erklärung:

Ich habe ein Excelformular in dem ich die geleisteten Stunden von Mitarbeitern zur Verrechnung eingebe. Zum Beispeil: Dienstbeginn (Zeile H7) 06:00 - Dienstende (Zeile I7) 18:00. In Zeile J7 steht dann der errechnete Stundenwert von 12,00 Stunden. So weit ist alles klar.

Problem:

Jetzt müssen wir die Stunden wie wie folgt aufteilen:
06:00 - 20:00 Uhr
20:00 - 00:00 Uhr
00:00 - 06:00 Uhr

Jetzt möchte ich Excel anweisen, dass wenn ich bei Dienstbeginn (H7) 18:00 und bei Dienstende (I7) 06:00 Uhr einschreibe, Excel automatisch eine Aufteilung der Stunden wie oben beschrieben, in 6 weitere Zeilen (z.B.: K7 bis P7) vornimmt.

Dass heißt, im Ergebnis müsste dann stehen:
K7 und L7 = 18:00 und 20:00
M7 und N7 = 20:00 und 00:00
O7 und P7 = 00:00 und 06:00

Trage ich allerdings bei Dienstbeginn 15:00 und bei Dienstende 23:00 ein, soll bei M7 20:00 und bei N7 23:00 stehen und bei O7 und P7 darf nix stehen.

Ich habe es mit Wenn Bedingungen versucht, aber da hat Excel meines Erachtens ein Problem mit den Uhrzeiten und ich mit den Wenn Bedingungen.

Ich hoffe ich habe mein Problem verständlich erklärt, wenn nicht versuche ich es gerne noch einmal.

Alex_Winter · 2. November 2009 um 19:13

Hallo fuhri!

Ich hab das auch mit Wenn-Funktionen gelöst… Das erste Problem für dich war wahrscheinlich, dass du über den Tag (24:00) rauskommst, da musst du einen Trick anwenden, sonst macht Excel einen Aufstand *gg*. Ich hab aus diesem Grund folgende Formeln verwendet:

J7: =WENN(I7K6;H7;"")
L7: =WENN(ODER($I$7>L6;$I$7M6;$I$7N6+1;$I$7O6+1;$I$7"");I7;"")

In Zeile 6 hab ich die Intervalle eingetragen. So biste flexibel, wenn sich die mal ändern sollten.

Gruß Alex

fuhri1 · 3. November 2009 um 08:09

Hallo Alex,

erst einmal danke für deine Antwort, ich werde es probieren, soblad ich weiss, was du mit Intervallen meinst.

Würdest du mir hierfür ein paar Beispiele geben.

Danke vorab.

Alex_Winter · 3. November 2009 um 08:25

Hallo fuhri!

Das is nix spektakuläres sondern nur deine Zeiten, nach denen du aufsplittest. Also

K6: 06:00
L6: 20:00
M6: 20:00
N6: 00:00
O6: 00:00
P6: 06:00

Gruß Alex

fuhri1 · 3. November 2009 um 08:35

Super,
Danke,

werde es gleich probieren.

fuhri1 · 3. November 2009 um 10:33

Hallo Alex:

habe es ausprobiert und funktioniert zu 80 %.

zu Spalte K7: hier habe ich ein = nach kleiner als noch eingefügt, da er sonst nicht 06:00 anzeigt.

zu Spalte O7: gebe ich die Arbeitszeit 12:00 bis 00:00 Uhr, zeigt er mir hier auch 00:00 an, dass soll es aber nicht.

zu Spalte P7: alle Zeiten zwischen 00:00 und 06:00 Uhr werden nicht angezeigt.

Dürfte ich Dich nochmals um Deine Hilfe bitten.

Danke im Voraus.

fuhri1

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 3. November 2009 um 12:38

Schichtzeiten ermitteln

Jetzt müssen wir die Stunden wie wie folgt aufteilen:
06:00 - 20:00 Uhr
20:00 - 00:00 Uhr
00:00 - 06:00 Uhr

Jetzt möchte ich Excel anweisen, dass wenn ich bei
Dienstbeginn (H7) 18:00 und bei Dienstende (I7) 06:00 Uhr
einschreibe, Excel automatisch eine Aufteilung der Stunden wie
oben beschrieben, in 6 weitere Zeilen (z.B.: K7 bis P7)
vornimmt.

Hallo Fuhri,

andrer Ansatz:

Tabellenblatt: [Mappe1]!Tabelle1
 │ G │ H │ I │ J │ K │ L │ M │ N │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 1 │ Name │ von │ bis │ Ges │ 06:00 - 20:00 │ 20:00 - 00:00 │ 00:00 - 06:00 │ Prüf │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 2 │ Name1 │ 06:00 │ 18:00 │ 12:00 │ 12:00 │ 00:00 │ 00:00 │ 12:00 │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 3 │ Name2 │ 18:00 │ 05:00 │ 11:00 │ 02:00 │ 04:00 │ 05:00 │ 11:00 │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 4 │ Name3 │ 22:00 │ 06:05 │ 08:05 │ 00:05 │ 02:00 │ 06:00 │ 08:05 │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 5 │ Name4 │ 05:00 │ 21:45 │ 16:45 │ 14:00 │ 01:45 │ 01:00 │ 16:45 │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 6 │ Name5 │ 00:01 │ 23:59 │ 23:58 │ 14:00 │ 03:59 │ 05:59 │ 23:58 │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 7 │ │ │ │ │ │ │ │ │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 8 │ von │ bis │ │ │ │ │ │ │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
 9 │ 06:00 │ 20:00 │ │ │ │ │ │ │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
10 │ 20:00 │ 00:00 │ │ │ │ │ │ │
───┼───────┼───────┼───────┼───────┼───────────────┼───────────────┼───────────────┼───────┤
11 │ 00:00 │ 06:00 │ │ │ │ │ │ │
───┴───────┴───────┴───────┴───────┴───────────────┴───────────────┴───────────────┴───────┘
Benutzte Formeln:
J2 : =WENN(ODER(I2="";H2="");"";I2-H2+1\*(I2$H$9);I2+(H2\>I2))-MAX($G$9;H2))+MAX(;(MIN($H$9;I2+(H2\>I2))-H2)\*($G$9\>$H$9))+MAX(;MIN($H$9+($G$9\>$H$9);I2+0)-$G$9)\*(H2\>I2)
K3 : =MAX(;MIN($H$9+($G$9\>$H$9);I3+(H3\>I3))-MAX($G$9;H3))+MAX(;(MIN($H$9;I3+(H3\>I3))-H3)\*($G$9\>$H$9))+MAX(;MIN($H$9+($G$9\>$H$9);I3+0)-$G$9)\*(H3\>I3)
K4 : =MAX(;MIN($H$9+($G$9\>$H$9);I4+(H4\>I4))-MAX($G$9;H4))+MAX(;(MIN($H$9;I4+(H4\>I4))-H4)\*($G$9\>$H$9))+MAX(;MIN($H$9+($G$9\>$H$9);I4+0)-$G$9)\*(H4\>I4)
K5 : =MAX(;MIN($H$9+($G$9\>$H$9);I5+(H5\>I5))-MAX($G$9;H5))+MAX(;(MIN($H$9;I5+(H5\>I5))-H5)\*($G$9\>$H$9))+MAX(;MIN($H$9+($G$9\>$H$9);I5+0)-$G$9)\*(H5\>I5)
K6 : =MAX(;MIN($H$9+($G$9\>$H$9);I6+(H6\>I6))-MAX($G$9;H6))+MAX(;(MIN($H$9;I6+(H6\>I6))-H6)\*($G$9\>$H$9))+MAX(;MIN($H$9+($G$9\>$H$9);I6+0)-$G$9)\*(H6\>I6)
L2 : =MAX(;MIN($H$10+($G$10\>$H$10);I2+(H2\>I2))-MAX($G$10;H2))+MAX(;(MIN($H$10;I2+(H2\>I2))-H2)\*($G$10\>$H$10))+MAX(;MIN($H$10+($G$10\>$H$10);I2+0)-$G$10)\*(H2\>I2)
L3 : =MAX(;MIN($H$10+($G$10\>$H$10);I3+(H3\>I3))-MAX($G$10;H3))+MAX(;(MIN($H$10;I3+(H3\>I3))-H3)\*($G$10\>$H$10))+MAX(;MIN($H$10+($G$10\>$H$10);I3+0)-$G$10)\*(H3\>I3)
L4 : =MAX(;MIN($H$10+($G$10\>$H$10);I4+(H4\>I4))-MAX($G$10;H4))+MAX(;(MIN($H$10;I4+(H4\>I4))-H4)\*($G$10\>$H$10))+MAX(;MIN($H$10+($G$10\>$H$10);I4+0)-$G$10)\*(H4\>I4)
L5 : =MAX(;MIN($H$10+($G$10\>$H$10);I5+(H5\>I5))-MAX($G$10;H5))+MAX(;(MIN($H$10;I5+(H5\>I5))-H5)\*($G$10\>$H$10))+MAX(;MIN($H$10+($G$10\>$H$10);I5+0)-$G$10)\*(H5\>I5)
L6 : =MAX(;MIN($H$10+($G$10\>$H$10);I6+(H6\>I6))-MAX($G$10;H6))+MAX(;(MIN($H$10;I6+(H6\>I6))-H6)\*($G$10\>$H$10))+MAX(;MIN($H$10+($G$10\>$H$10);I6+0)-$G$10)\*(H6\>I6)
M2 : =MAX(;MIN($H$11+($G$11\>$H$11);I2+(H2\>I2))-MAX($G$11;H2))+MAX(;(MIN($H$11;I2+(H2\>I2))-H2)\*($G$11\>$H$11))+MAX(;MIN($H$11+($G$11\>$H$11);I2+0)-$G$11)\*(H2\>I2)
M3 : =MAX(;MIN($H$11+($G$11\>$H$11);I3+(H3\>I3))-MAX($G$11;H3))+MAX(;(MIN($H$11;I3+(H3\>I3))-H3)\*($G$11\>$H$11))+MAX(;MIN($H$11+($G$11\>$H$11);I3+0)-$G$11)\*(H3\>I3)
M4 : =MAX(;MIN($H$11+($G$11\>$H$11);I4+(H4\>I4))-MAX($G$11;H4))+MAX(;(MIN($H$11;I4+(H4\>I4))-H4)\*($G$11\>$H$11))+MAX(;MIN($H$11+($G$11\>$H$11);I4+0)-$G$11)\*(H4\>I4)
M5 : =MAX(;MIN($H$11+($G$11\>$H$11);I5+(H5\>I5))-MAX($G$11;H5))+MAX(;(MIN($H$11;I5+(H5\>I5))-H5)\*($G$11\>$H$11))+MAX(;MIN($H$11+($G$11\>$H$11);I5+0)-$G$11)\*(H5\>I5)
M6 : =MAX(;MIN($H$11+($G$11\>$H$11);I6+(H6\>I6))-MAX($G$11;H6))+MAX(;(MIN($H$11;I6+(H6\>I6))-H6)\*($G$11\>$H$11))+MAX(;MIN($H$11+($G$11\>$H$11);I6+0)-$G$11)\*(H6\>I6)
N2 : =SUMME(K2:M2)
N3 : =SUMME(K3:M3)
N4 : =SUMME(K4:M4)
N5 : =SUMME(K5:M5)
N6 : =SUMME(K6:M6)

Zahlenformate der Zellen im gewählten Bereich:
G2:G11,H1:H11,I1:I11,J1:J11,L2:L11,M2:M11
haben das Zahlenformat: [hh]:mm
G1,K1:N1
haben das Zahlenformat: Standard
K2:K11,N2:N11
haben das Zahlenformat: hh:mm

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

fuhri1 · 3. November 2009 um 13:20

Hallo Alex:

habe es ausprobiert und funktioniert zu 80 %.

zu Spalte K7: hier habe ich ein = nach kleiner als noch eingefügt, da er sonst nicht 06:00 anzeigt.

zu Spalte O7: gebe ich die Arbeitszeit 12:00 bis 00:00 Uhr, zeigt er mir hier auch 00:00 an, dass soll es aber nicht.

zu Spalte P7: alle Zeiten zwischen 00:00 und 06:00 Uhr werden nicht angezeigt.

Dürfte ich Dich nochmals um Deine Hilfe bitten.

Danke im Voraus.

fuhri1

Alex_Winter · 4. November 2009 um 08:48

Hallo fuhri!

zu Spalte O7: gebe ich die Arbeitszeit 12:00 bis 00:00 Uhr,
zeigt er mir hier auch 00:00 an, dass soll es aber nicht.

00:00 ist in Excel immer ein Problem. Versuch’s doch mal, wenn’s wirklich so is, 23:59. Dann klappts.

zu Spalte P7: alle Zeiten zwischen 00:00 und 06:00 Uhr werden
nicht angezeigt.

Mit dieser Formel müsste es klappen: =WENN(ODER($I$7&gt:stuck_out_tongue_winking_eye:6+1;$I$7

fuhri1 · 4. November 2009 um 17:20

Hallo Alex,

1000 Dank für deine Hilfe.

Schreibe ich bei Dienstzeit bis anstatt 00:00, 24:00 Uhr, dann erscheint auch die 00:00 Uhr in der Spalte P7 nicht mehr.

Nochmals danke für Deine Hilfe

Fuhri1