Bekijk bijlage ORT uitbreiding MB.xls
Bijgaand een geheel herziene versie.
Ik heb wat hulpkolommen (M t/m V) toegevoegd met indicaties (WAAR/ONWAAR) of er gewerkt is (vandaag en de volgende dag, indien tot na middernacht gewerkt is) en of dat dan op een feestdag, zaterdag, zondag of een gewone werkdag was.
Desgewenst kun je die kolommen verbergen.
Van die gegevens heb ik ook namen gedefinieerd om de formules voor de gewerkte uren wat beter te kunnen lezen.
Die formules bestaan allemaal telkens uit 2 delen: eerst het deel voor de huidige dag en daarbij opgeteld het deel voor de volgende dag, indien tot na middernacht is gewerkt.
Verder heb ik de tijdblokken die gelden voor ma-vr als tijd gedefinieerd en over 2 regels verspreid, zodat ze in de formules gebruikt kunnen worden.
Ik heb ze ook in chronologische volgorde geplaatst.
Zo kwam ik tot de volgende formule in E9:
Code:
=ALS(Vandaag_gewerkt_op_een_gewone_werkdag;MAX(0;MIN(ALS(Gewerkt_tot_na_middernacht;1;$C9);E$7)-MAX($B9;E$6));0)+ALS(Gewerkt_tot_na_middernacht;ALS(Volgende_dag_gewerkt_op_een_gewone_werkdag;MAX(0;MIN($C9;E$7)-E$6);0);0)
Het eerste deel, voor de uren die vandaag zijn gewerkt, loopt tot aan de +. Het deel daarna is voor de uren die na middernacht zijn gewerkt.
In het eerste deel wordt geverifieerd of er vandaag gewerkt is op een gewone werkdag; zo ja dan worden de uren berekend die in het betreffende tijdblok zijn gewerkt:
- de eindtijd is het minimum van het einde van het tijdsblok (E$7) en hetzij 1 (= 24:00) indien gewerkt tot na middernacht, hetzij de eindtijd in C9
- de begintijd is het maximum van begin van het tijdblok (E$6) en begin werktijd ($B9)
deze worden van elkaar afgetrokken met een maximum van 0 (dus niet negatief).
In het tweede deel wordt geverifieerd of er gewerkt is tot na middernacht en of dat dan op een gewone werkdag was.
Zo ja, dan is het aantal uren het minimum van het einde van het tijdsblok (E$7) en de eindtijd in C9, min het begin van het tijdblok (E$6), de uitkomst maximaal 0 (dus niet negatief). De werktijd in dit deel begint altijd op 0:00.
De formule in E9 is doorgetrokken naar I39, dus 1 uniforme formule voor de uren die gewerkt zijn op normale werkdagen.
De formules in J, K, L zijn afwijkend voor zaterdagen, zondagen en feestdagen.
Deze formules zijn wat eenvoudiger omdat op die dagen geen bloktijden gelden.
De totale uren in D41 worden nu eenvoudig berekend met een somproduct van de totale gewerkte uren en de bijbehorende percentages:
Code:
=SOMPRODUCT(E40:L40;E8:L8)
De formule in kolom D (totaal uren) heb ik ook aangepast.
Verder heb ik de feestdagen in Blad1 aangepast naar die van 2015 en hiervoor weer een naam gedefinieerd, die niet alleen in kolom O en P wordt gebruikt om te bepalen of er gewerkt is op een feestdag, maar ook in voorwaardelijke opmaak die ervoor zorgt dat feestdagen een gele achtergrond krijgen.
De grijze achtergrond voor weekenddagen is nu ook middels voorwaardelijke opmaak geregeld. Feestdagen in een weekeinde blijven geel.
Tenslotte wordt nu geen gebruik meer gemaakt van gegevens uit andere werkmappen (uurloon, start-/eindtijden en totaal brutoloon; feestdagen): als dat wél moet, dan moet je dat even terug aanpassen.
Ik geloof dat ik alles nu wel zo'n beetje heb toegelicht.
Mijn inziens werkt het geheel goed, maar uiteraard moet je alles goed uittesten, want je blijft natuurlijk zelf verantwoordelijk.
Ik ben benieuwd naar je bevindingen.
Edit: ik zie in jouw versie dat de totale uren op feestdagen worden verhoogd met het aantal uren dat er in die maand méér gewerkt is dan 152 uur. In mijn versie is die verhoging weggevallen. Zonodig even aanpassen.
