Hi Zusammen,
ich habe hier ein kleines Problem mit einer Zeittabelle. Ich habe eine Ist Arbeitszeit, welche nach bestimmten Kriterien ab bzw aufgerundet werden soll, doch mit Obergrenze und Sverweis komme ich hier nicht weiter, vielleicht kann mir ja jemand helfen.
Gegeben
a )Minutengenaue Arbeitszeit (MAZ)
Gesucht
b )Ist Zeit welche in der Tabelle in einer
Spalte erscheinen sollen
MAZ IstZeit
6:07 - 6:00
(wenn Er 6:08 hat dann 6:15)
6:22 -> 6:15
(wenn Er 6:23 hat dann 6:30)
6:37 -> 6:30
(wenn Er 6:38 hat dann 6:45)
6:52 -> 6:45
(wenn Er 6:53 hat dann 7:00)
Danke für die Hilfe
Gruß
Monitorschauer
MAZ IstZeit
6:07 - 6:00
(wenn Er 6:08 hat dann 6:15)
6:22 -> 6:15
(wenn Er 6:23 hat dann 6:30)
6:37 -> 6:30
(wenn Er 6:38 hat dann 6:45)
6:52 -> 6:45
(wenn Er 6:53 hat dann 7:00)
hallo Screenrain,
angenommen deine MAZ steht in A1, dann
=ZEITWERT(STUNDE(A1)&":"&SVERWEIS(MINUTE(A1);Tabelle2!$A$1:blush:B$5;2;1))
Dazu bruachst du dieses Hilfsblatt:
Tabellenblatt: [Mappe1]!Tabelle2
│ A │ B │
──┼────┼────┤
1 │ 0 │ 0 │
──┼────┼────┤
2 │ 8 │ 15 │
──┼────┼────┤
3 │ 23 │ 30 │
──┼────┼────┤
4 │ 38 │ 45 │
──┼────┼────┤
5 │ 53 │ 60 │
──┴────┴────┘
A1:B5
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Hallo,
wenn ich das richtig verstehe, soll die Zeit auf volle Viertelstunden ab- und aufgerundet werden?
Trick mit SVERWEIS ist gut.
Eine andere Methode ist, die Uhrzeit in eine Zahl umzuwandeln, diese zu runden und anschließend in eine Uhrzeit zurückzuwandeln.
Beispiel (schrittweise; geht auch in einer einzelnen langen Formel):
A1: Uhrzeit, z.B. 6:20
A2: =RUNDEN((STUNDE(A1)+MINUTE(A1)/60)*4;0)/4
A3: =ZEIT(ABRUNDEN(A2;0);(A2-ABRUNDEN(A2;0))*60;0)
Dann steht in A3 die auf 15 Minuten gerundete Zeit, hier also 6:15.
Erklärung:
STUNDE(A1)+MINUTE(A1)/60
- macht aus einer Uhrzeit eine vergleichbare Zahl, also z.B. aus 6:20 die Zahl 6,33333… (Sechs und ein Drittel)
Multiplikation mit 4
- für die Rundung auf Viertelstunden
RUNDEN(…;0)
- na das Runden eben
Dividieren durch 4
- …um nach dem Runden wieder ins ursprüngliche Größenverhältnis zu gehen
ZEIT(…)
- Wandelt die Zahl wieder in einen Zeitwert um, wobei der Nakommaanteil erst mit 60 multipliziert werden muss - so wie umgekehrt bei der Umwandlung von Zeit in Zahl die Minuten durch 60 dividiert wurden.
Gruß
Anja
Grüezi Monitorschauer
ich habe hier ein kleines Problem mit einer Zeittabelle. Ich
habe eine Ist Arbeitszeit, welche nach bestimmten Kriterien ab
bzw aufgerundet werden soll, doch mit Obergrenze und Sverweis
komme ich hier nicht weiter, vielleicht kann mir ja jemand
helfen.
MAZ IstZeit
6:07 - 6:00
(wenn Er 6:08 hat dann 6:15)
6:22 -> 6:15
(wenn Er 6:23 hat dann 6:30)
6:37 -> 6:30
(wenn Er 6:38 hat dann 6:45)
6:52 -> 6:45
(wenn Er 6:53 hat dann 7:00)
Das ist IMO eine klassische Rundung auf 0.25 Stunden.
Rechne daher den Zeitwert in Stunden um (*24), runde auf 15 Minuten (/0.25) und rechne am Ende wieder zurück in Stunden.
Mit einer einzigen Formel geht das dann so, wenn dein Zeitwert in A1 steht:
=RUNDEN(A1*24/0.25;0)*0.25/24
Mit freundlichen Grüssen
Thomas Ramel
Mit einer einzigen Formel geht das dann so, wenn dein Zeitwert
in A1 steht:
=RUNDEN(A1*24/0.25;0)*0.25/24
Na super… 
Ich frag mich grad, welchen Tippfehler ich wohl gemacht habe, dass diese Kurzversion bei mir nicht funktionierte.
Gruß
A
Grüezi Anja
Mit einer einzigen Formel geht das dann so, wenn dein Zeitwert
in A1 steht:
=RUNDEN(A1*24/0.25;0)*0.25/24
Na super…
Ich frag mich grad, welchen Tippfehler ich wohl gemacht habe,
dass diese Kurzversion bei mir nicht funktionierte.
Keine Ahnung was da nicht geklappt hat - aber der Begriff ‚Kurzversion‘ hat mich darauf gebracht, dass es pro Tag ja genau 96 Viertelstunden gibt und das daher der Multiplikator sein muss.
Es geht also noch kürzer 
=RUNDEN(A1*96;0)/96
Mit freundlichen Grüssen
Thomas Ramel
ich geh davon aus, daß du die deutsche Version hast, somit Komma „,“ und nicht „.“
=RUNDEN(A1*24/0,25;0)*0,25/24
Gruss
M@x
Wow,
vielen Dank
jetzt kann ich noch ein bisserl basteln
VG
Monitorschauer
=RUNDEN(A1*96;0)/96
Grüezie Thomas,
ja, ist sehr kurz, isses aber auch wie gewünscht?
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │
──┼───────┼───────┼───────┤
1 │ 06:06 │ 06:00 │ 06:00 │
──┼───────┼───────┼───────┤
2 │ 06:07 │ 06:15 │ 06:00 │ \*\*\*
──┼───────┼───────┼───────┤
3 │ 06:08 │ 06:15 │ 06:15 │
──┼───────┼───────┼───────┤
4 │ 06:09 │ 06:15 │ 06:15 │
──┼───────┼───────┼───────┤
5 │ 06:21 │ 06:15 │ 06:15 │
──┼───────┼───────┼───────┤
6 │ 06:22 │ 06:30 │ 06:15 │ \*\*\*
──┼───────┼───────┼───────┤
7 │ 06:23 │ 06:30 │ 06:30 │
──┼───────┼───────┼───────┤
8 │ 06:24 │ 06:30 │ 06:30 │
──┴───────┴───────┴───────┘
Benutzte Formeln:
B1: =RUNDEN(A1\*96;0)/96
B2: =RUNDEN(A2\*96;0)/96
B3: =RUNDEN(A3\*96;0)/96
usw. in B
C1: =ZEITWERT(STUNDE(A1)&":"&SVERWEIS(MINUTE(A1);Tabelle2!$A$1:blush:B$5;2;1))
C2: =ZEITWERT(STUNDE(A2)&":"&SVERWEIS(MINUTE(A2);Tabelle2!$A$1:blush:B$5;2;1))
C3: =ZEITWERT(STUNDE(A3)&":"&SVERWEIS(MINUTE(A3);Tabelle2!$A$1:blush:B$5;2;1))
usw. in C
A1:C8
haben das Zahlenformat: hh:mm
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Hi Reinhard,
bei mir kommt mit der einfachen Rundenformel aber das richtige raus
also 6:07 > 6:00
6:08 > 6:15
ein anderes Resultat wär ja auch widersinnig,
überprüf mal, ob sich da bei dir ein paar Sekunden dazugeschwindelt haben.
Gruss
M@x
Hi Reinhard,
bei mir kommt mit der einfachen Rundenformel aber das richtige
raus
also 6:07 > 6:00
6:08 > 6:15
ein anderes Resultat wär ja auch widersinnig,
überprüf mal, ob sich da bei dir ein paar Sekunden
dazugeschwindelt haben.
Gruss
Hallo M@x,
ja, das war’s, danke dir.
Bei den beiden entsprechenden Zeiten waren je 30 sec zuviel.
Wie das kam, ich benutzte die Formel =A1+1/24/60, bleibt bislang im Dunklen.
Alle Versuche diesen Fehler nachzustellen schlugen bislang fehl.
Aber okay, diese Beitragsfoge ist ja gelöst.
Gruß
Reinhard