Bekijk bijlage Tijden verdelen over dagdelen.xlsx
Hierbij mijn oplossing.
Kort samengevat: ik heb hulpkolommen gemaakt (N-V) waarin ik de gewerkte tijd verdeel over 2 etmalen met de relevante tijdblokken en die breng ik dan weer over naar de 3 kolommen van de einduitkomst (F-H).
In detail:
Om te testen worden de begin- en eindtijden in de
kolommen A en D willekeurig gegenereerd.
Met F9 kun je een nieuwe set genereren en verder wordt een nieuwe set gegenereerd zodra je iets wijzigt in het werkboek.
In het echte bestand voer je natuurlijk zelf de begin- en eindtijden in.
In
kolom B heb ik de weekdag opgenomen i.p.v. het dagnummer (die staat bij mij in kolom J).
In
kolom C staat de begintijd, berekend uit A.
In
kolom E staat de eindtijd, berekend uit D en er is 1 bij opgeteld als voorbij middernacht is gewerkt (dat zie je niet, maar het is zo).
In
kolom F de gewerkte uren van ma 20:30-vr 07:45, formule in F3 (en gekopieerd naar beneden):
Code:
=ALS(J3=1;U3;ALS(J3=2;SOM(Q3;R3;U3);ALS(EN(J3>=3;J3<=4);SOM(N3;Q3;R3;U3);ALS(J3=5;SOM(N3;Q3;R3);ALS(J3=6;N3;0)))))
Als begonnen is op zondag (1), dan de uren tussen 20:30-0:00 van dag 2 (kolom U).
Als begonnen is op maandag (2), dan de uren tussen 20:30-0:00 van dag 1 (kolom Q) + de uren tussen 0:00-7:45 van dag 2 (kolom R) + de uren tussen 20:30-0:00 van dag 2.
Als begonnen is op dinsdag t/m woensdag (3 t/m 4), dan de uren tussen 0:00-7:45 en 20:30-0:00 van dag 1 en 2 (kolommen N,Q,R,U).
Als begonnen is op donderdag (5), dan de uren tussen 0:00-7:45 en 20:30-0:00 van dag 1 (kolom N en Q) en 0:00-7:45 van dag 2 (kolom R).
Als begonnen is op vrijdag (6), dan de uren tussen 0:00-7:45 van dag 1 (kolom N).
In
kolom G de gewerkte uren van vr 20:30-ma 07:45, formule in G3 (en gekopieerd naar beneden):
Code:
=ALS(J3=5;U3;ALS(J3=6;SOM(Q3:U3);ALS(J3=7;SOM(N3:U3);ALS(J3=1;SOM(N3:S3);ALS(J3=2;N3;0)))))
Als begonnen is op donderdag (5), dan de uren tussen 20:30-0:00 van dag 2 (kolom U).
Als begonnen is op vrijdag (6), dan de uren van 20:30 dag 1 t/m 0:00 van dag 2 (kolommen Q t/m U).
Als begonnen is op zaterdag (7), dan de som van alle uren op dag 1 en 2 (kolommen N t/m U).
Als begonnen is op zondag (1), dan de som van alle uren op dag 1, en 0:00-7:45 op dag 2 (kolommen N t/m S).
Als begonnen is op maandag (2), dan de uren tussen 0:00-7:45 op dag 1 (kolom N).
In
kolom H de gewerkte uren van ma 16:30-vr 20:30, formule in H3 (en gekopieerd naar beneden):
Code:
=ALS(J3=1;T3;ALS(EN(J3>=2;J3<=5);P3+T3;ALS(J3=6;P3)))
Vergelijkbare systematiek als de andere 2 blokken.
Kolom J dagnummer start
Kolom K dagnummer einde
Kolom L aantal gewerkte uren
Kolom M moet blanko blijven.
N2 t/m V2: starttijden van de tijdblokken. R2 t/m V2 zijn berekend als N2 t/m R2 + 1.
N3 t/m U3 en verder naar beneden: de gewerkte uren in het betreffende tijdblok.
Formule in N3, gekopieerd naar rechts en naar beneden:
Edit: zie volgende post voor een veel eenvoudiger versie.
Code:
=MIN($L3-SOM($M3:M3);MAX(MIN(O$2;$E3)-MAX(N$2;$C3);0))
Kort samengevat: zolang het totaal van de vorige kolommen kleiner is dan de totale gewerkte tijd, dan de gewerkte tijd in het huidige blok.
De gewerkte tijd in een blok is de eindtijd minus de begintijd, d.w.z. de kleinste van de begintijd van het volgende blok (O$2) en de eindtijd ($E3) minus de grootste van de begintijd van het huidige blok(N$2) en de begintijd ($C3).
Op veel plaatsen heb ik de tijden een aangepast nummerformaat gegeven die 0:00 onderdrukt:
uu:mm;;;
Dat was het wel geloof ik. Je ziet: het is nog niet zo eenvoudig.
En het slechte nieuws is dat je zelf nog even moet controleren of ik geen vergissingen heb gemaakt...
