Gast hat geschrieben:Würde es dir denn Umstände machen mir deine Idee zu erklären?
Ja, das wird schon ein bisschen länglich…
Zunächst mal musst du wissen, wie mit Zeiten gerechnet wird. Siehe dazu
RechnenMitDatumUndZeit.
Das für uns Wichtigste in aller Kürze:
- »1/24« steht für 1 Stunde oder »heute 01:00 Uhr".
- »1+8/24« steht für »1*24+8=32« Stunden oder »morgen 08:00 Uhr«
- Uhrzeiten werden (hier) mit HH:MM formatiert,
- Zeitdauern werden (hier) mit [HH]:MM formatiert.
Ausserdem: Dass die »von-Zeit« kleiner ist als die »bis-Zeit« habe ich nirgendwo überprüft. Alle Formeln gehen also davon aus, dass Ai
* < Bi
* gilt. Dafür bietet sich eine →
Bedingte Formatierung an, die dem Benutzer schon bei der Zeiterfassung auf die Finger klopft.
Zur Sache:
Die Differenz MIN($B2;23/24)-MAX($A2;12/24) machst du dir am besten mit einem kleinen Bildchen klar:
Code: Alles auswählen
Zeit 10 11 12 13 22 23 24
Zeitfenster 12:00 – 23:00 ├──┼──┼── ··· ─┼──┼──┤
Arbeitszeit 10:30 – 23:30 ├──┼──┼──┼──┼──┼── ··· ─┼──┼──┼──┤
↑ ↑
Zeitabdeckung (1) (2)
Der Punkt (1) wird bestimmt durch das
Maximum der Anfangszeiten der erfassten Zeit (→ $A2) und des Zeitfensters (→ 12/24), also MAX(10:30;12:00) = 12:00 Uhr.
Der Punkt (2) wird bestimmt durch das
Minimum der Endzeiten der erfassten Zeit (→ $B2) und des Zeitfensters (→ 23:00) , also Min(23:30;23:00) = 23:00 Uhr.
Angerechnet wird somit eine Zeitdauer von 23:00 Uhr – 12:00 Uhr = 11:00 Std.
Das alles kannst du mit unterschiedlichen Anfangs- und Endzeiten durchspielen, also auch Teil-Überdeckungen der Intervalle vorne und hinten, usw. Die Formel gilt für die Spalten C und D, also Zeiten am heutigen Tag. Wenn der morgige Tag mit reinkommt, wird's komplizierter.
In den Spalten C und D reicht es aus, negative Ergebnisse auszuschließen (→ MIN(0; <min-max-Differenz>). Die ergeben sich in Differenzen, in denen Arbeitszeiten am morgigen Tag (→ »
1+Uhrzeit«) Zeitfenstern am heutigen Tag (→ »
0+Uhrzeit«) gegenüberstehen.
Für die Spalte E muss dagegen unterschieden werden zwischen den Fällen
- Arbeitszeit beginnt morgen (→ Ai* > 1)
Daraus folgt, dass sie auch morgen endet (s.o.: Ausserdem: … → Bedingte Formatierung).
- Arbeitszeit beginnt heute (→ Ai* <= 1)
Dann darf nur das auf morgen entfallende Zeitintervall betrachtet werden.
In beiden Fällen kommt dann wieder die oben beschrieben Differenz »MIN(…) – MAX(…)« zum Zuge.
Gruß
lorbass
_________________
* Ai steht für Zelladressen
A2,
A3, …;
Bi entsprechend.
[quote="Gast"]Würde es dir denn Umstände machen mir deine Idee zu erklären?[/quote]
Ja, das wird schon ein bisschen länglich…
Zunächst mal musst du wissen, wie mit Zeiten gerechnet wird. Siehe dazu [url=http://www.ooowiki.de/CalcFunktionenDatum(2f)RechnenMitDatumUndZeit.html]RechnenMitDatumUndZeit[/url].
Das für uns Wichtigste in aller Kürze:
[list][*] »1/24« steht für 1 Stunde [b]oder[/b] »heute 01:00 Uhr".
[*] »1+8/24« steht für »1*24+8=32« Stunden [b]oder[/b] »morgen 08:00 Uhr«
[*] Uhrzeiten werden (hier) mit [i]HH:MM[/i] formatiert,
[*] Zeitdauern werden (hier) mit [i][HH]:MM[/i] formatiert.[/list]
Ausserdem: Dass die »von-Zeit« kleiner ist als die »bis-Zeit« habe ich nirgendwo überprüft. Alle Formeln gehen also davon aus, dass Ai[color=#AA0000][b]*[/b][/color] < Bi[color=#AA0000][b]*[/b][/color] gilt. Dafür bietet sich eine → [i]Bedingte Formatierung[/i] an, die dem Benutzer schon bei der Zeiterfassung auf die Finger klopft.
Zur Sache:
Die Differenz MIN($B2;23/24)-MAX($A2;12/24) machst du dir am besten mit einem kleinen Bildchen klar:
[code]Zeit 10 11 12 13 22 23 24
Zeitfenster 12:00 – 23:00 ├──┼──┼── ··· ─┼──┼──┤
Arbeitszeit 10:30 – 23:30 ├──┼──┼──┼──┼──┼── ··· ─┼──┼──┼──┤
↑ ↑
Zeitabdeckung (1) (2)[/code]
Der Punkt (1) wird bestimmt durch das [b]Maximum der Anfangszeiten der erfassten Zeit (→ $A2) und des Zeitfensters (→ 12/24)[/b], also MAX(10:30;12:00) = 12:00 Uhr.
Der Punkt (2) wird bestimmt durch das [b]Minimum der Endzeiten der erfassten Zeit (→ $B2) und des Zeitfensters (→ 23:00) [/b], also Min(23:30;23:00) = 23:00 Uhr.
Angerechnet wird somit eine Zeitdauer von 23:00 Uhr – 12:00 Uhr = 11:00 Std.
Das alles kannst du mit unterschiedlichen Anfangs- und Endzeiten durchspielen, also auch Teil-Überdeckungen der Intervalle vorne und hinten, usw. Die Formel gilt für die Spalten C und D, also Zeiten am heutigen Tag. Wenn der morgige Tag mit reinkommt, wird's komplizierter.
In den Spalten C und D reicht es aus, negative Ergebnisse auszuschließen (→ MIN(0; <min-max-Differenz>). Die ergeben sich in Differenzen, in denen Arbeitszeiten am morgigen Tag (→ »[b]1[/b]+Uhrzeit«) Zeitfenstern am heutigen Tag (→ »[b]0[/b]+Uhrzeit«) gegenüberstehen.
Für die Spalte E muss dagegen unterschieden werden zwischen den Fällen
[list][*] Arbeitszeit beginnt morgen (→ Ai[color=#AA0000][b]*[/b][/color] > 1)
Daraus folgt, dass sie auch morgen endet (s.o.: Ausserdem: … → [i]Bedingte Formatierung[/i]).
[*] Arbeitszeit beginnt heute (→ Ai[color=#AA0000][b]*[/b][/color] <= 1)
Dann darf nur das auf morgen entfallende Zeitintervall betrachtet werden.[/list]
In beiden Fällen kommt dann wieder die oben beschrieben Differenz »MIN(…) – MAX(…)« zum Zuge.
Gruß
lorbass
_________________
[color=#AA0000][b]*[/b][/color] [i]Ai[/i] steht für Zelladressen [i]A2[/i], [i]A3[/i], …; [i]Bi[/i] entsprechend.