[excel2010]Uurrooster maken

[fabstar81]

ik ben bezig een uurrooster te maken in excel voor verpleegpersoneel. zij maken gebruik van 5 verschillende percentages waar ze onregelmatigheidstoeslag voor ontvangen (ORT). 22/38/47/52/60 en dan ook nog een verdeling in tijd en dag.

tussen 06:00 uur en 07:00 uur en tussen 20:00 uur en 22:00 uur op maandag tot en met vrijdag tussen 06:00 uur en 08:00 uur en tussen 12:00 uur en 22:00 uur op zaterdag
tussen 00:00 uur en 06:00 uur en tussen 22:00 en 24:00 uur op maandag tot en met vrijdag tussen 00:00 uur en 06:00 uur en tussen 22:00 en 24:00 uur op zaterdag
tussen 00:00 uur en 24:00 uur op zon- en feestdagen en tussen 18:00 uur en 24:00 uur op 24 en 31
december

ik wil alleen dat de invuller begintijd en eindtijd invult en hoe lang ze pauze hebben gehad. simpel a2-a1-pauze is dan aantal uren wat ze gewerkt hebben. dat is niet zo moeilijk. de uren uit elkaar trekken adhv de dagen in de week is ook niet zo moeilijk met een WEEKDAY (1 tm 7), als <>1 en <>7 is dan door de weeks, etc.

het moeilijke is om de uren uit elkaar te trekken die na 0:00 op de klok komen. simpel aftrekken gaat dan niet meer. je krijgt dan overlap en dan snapt ie het niet meer.

wat ik tot nu toe heb:
simpel uit elkaar trekken van de uren en valideren adhv weekdag, tijdvak

wat ik geprobeerd heb om de uren uit elkaar te trekken die na 0:00 komen:
een concatenate maken van datum en tijd (aangezien ik alleen wil dat de dames de uren invullen) zodat ik excel een beetje help op welke datum nu juist de eindtijd valt.

dat laatste werkt alleen niet. ik krijg niet goed een datum aan elkaar geplakt die als uitkomst heeft: dd-mm-jjj hh:mm. indirect, datevalue, data, etc. heb ik al geprobeerd, maar ik krijg altijd weer een getal uit de formule.

iemand nog tips? ik heb engelse excel en 2010 verschilt niet echt veel tov 2007.

[Falcon]

http://tinyurl.com/y8rkd2g

Maar even serieus wat heb jij verder opgezocht op internet?

[fabstar81]

zo flauw he. ik moet geen rooster maken. ik wil een urenregistratie maken die calculeert hoeveel onregelmatigheidsuren in welke tijdvakken je hebt gemaakt, ALLEEN aan de hand van het invullen van een begintijd en eindtijd.

let me google this for you

met je tiny=url zodat ie het niet ziet

[fabstar81]

als het zo makkelijk was dan had ik niet op GOT hoeven zoeken...

[F_J_K]

Falcon: doe ajb niet dat soort links (zeker niet als het duidelijk is dat de TS wel heeft nagedacht) en als je dat wel doet lees eerst de vraag

fabstar81: [google=excel rekenen met tijden] geeft wel antwoord: http://www.martonline.org...0datum%20en%20tijden.html

De formule nader bekeken: =((H6-F6+(H6<F6))*24)

Eindtijd - begintijd+(de tijd voor 00:00 uur) x 24 wordt gebruikt om de decimale uren in minuten om te zetten.

En dat kan je aanvullen met IF-statements voor bepaalde tijdstippen. Zou ik voor het overzicht in een verborgen hulptabel doen: tel de uren in normaal tarief los van de uren in overwerktariefX.

offtopic:
Oeh en ajb geen extra post maken maar de edit-knop gebruiken, zoals ik nu doe, als je binnen 24uur iets wilt toevoegen aan een post

[ Voor 33% gewijzigd door F_J_K op 06-04-2010 15:08 . Reden: edit ]

[fabstar81]

ja, die stap kwam ik ook al achter. had ik al gedaan *24. dat helpt inderdaad.

waar ik nog niet uitkom is het volgende:

ik heb al een aantal hulptabellen.

de kolommen heb ik als volgt neergezet:
22% met daaronder:
6:00 7:00 20:00 22:00

per percentage dus 4 kolommen als je ook naar de regels kijkt in de OP.

code:

=IF(AND($B7<>7;$B7<>1);IF(AND($C7>=K$3;$C7<=L$3);(L3-$C7)*24;FALSE);FALSE)

7 en 1 zijn de weeknummers. c7 is in te vullen begintdijd van de dienst, K en L dus de begin en eindtijd van de dienstvakken. tot nu toe alleen ff FALSE etc. erin gezet om iig de uren uit te laten rekenen.

even proberen met onderaan die tips over tijden over 24 uur.

[ Voor 4% gewijzigd door fabstar81 op 06-04-2010 15:17 ]

[Falcon]

Excuses!

[fabstar81]

https://www.qdrive.net/en...MjLLxXEFKX2Hm5D58Kx7Xhgm7

daar staat de sheet tot nu toe. ik probeer die formule van de website in te bakken, maar damn...het is wel pittig. vooral om dus de uren uit elkaar te trekken.
misschien wordt het wel duidelijker nu wat ik met de tijdvakken bedoel.

[F_J_K]

Even geen tijd om uitgebreid te kijken, maar toch: zo te zien is het probleem dat je verschillende eenheden door elkaar haalt. Zet alles in dezelfde opmaak als kolom H. Bijvoorbeeld gaat kolom I goed met I7 =(D7-C7-E7)+ALS(C7>D7;1)-J7 en dan natuurlijk opmaak hetzelfde als kolom H. Aangenomen dat J7 goed staat dan.

Idem kolom K en verder (let op: je bent daar ook wat $ vergeten en reken je opeens met L2, L3, etc. Ik neem aan dat je steeds L$3 bedoelt. Verder zou ik het skimpel houden en K:V nog verder uitsplitsen en voor de leesbaarheid geen onwaar gebruiken, maar simpelweg het aantal uren tussen 6u en 7u tellen: dat kan dus vaak 0 zijn.
Het idee is verder helemaal goed, volgens mij. (Alleen kolomF snap ik niet).

[Verwijderd]

zonder dingen uit mekaar te hoeven trekken kan het gewenste resultaat bekomen worden door1 dag op te tellen bij het einduur als dit einduur kleiner of gelijk is aan het startuur:

code:

=(VANDAAG()+B1+(B1<=A1))-(VANDAAG()+A1)

deze formule is zonder de pauze maar dat kan je makkelijk zelf toevoegen.

[F_J_K]

Als ik het goed heb (correct me if I'm wrong) is het komen tot gewerkte uren al gelukt, nu nog uren buiten de normale werktijden extra laten wegen.

offtopic:
Overigens gaat een slim iemand nu altijd een pauze opnemen, aan het begin of einde vd dag. Je houdt immers geen rekening met de timing vd pauze

[fabstar81]

het probleem is dat de verpleegsters vaak niet aan pauze toekomen. dat is nu juist waarom dus de pauze apart wordt neergezet.

het moeilijke is ook om het dus simpel te houden. de formule reproduceren en de uren uit laten rekenen gaat daarom wel goed, totdat ie over de 0:00 uur grens gaat. ik denk dat ik eerst de formule opnieuw ga bouwen zodat iig alle uren goed worden berekend, pas daarna erin zetten dat ie gaat kijken op wat voor dag het valt.

het is ook erg moeilijk wanneer een dag in verschillende dagen komt. stel: op zaterdag gaat de nachtdienst in om 20.00 uur. dan moet de formule dus rekening houden dat na 22.00 tot 0:00 het nog zaterdag is, maar daarna zondag... -> ander percentage. ik denk dat dat de moeilijkste wordt.

wat je bedoelt met nog verder uit elkaar trekken begrijp ik niet helemaal. je had wel gelijk dat ie altijd naar kolom L3 had moeten verwijzen.

[fabstar81]

F_J_K schreef op woensdag 07 april 2010 @ 12:21:
Even geen tijd om uitgebreid te kijken, maar toch: zo te zien is het probleem dat je verschillende eenheden door elkaar haalt. Zet alles in dezelfde opmaak als kolom H. Bijvoorbeeld gaat kolom I goed met I7 =(D7-C7-E7)+ALS(C7>D7;1)-J7 en dan natuurlijk opmaak hetzelfde als kolom H. Aangenomen dat J7 goed staat dan.

Idem kolom K en verder (let op: je bent daar ook wat $ vergeten en reken je opeens met L2, L3, etc. Ik neem aan dat je steeds L$3 bedoelt. Verder zou ik het skimpel houden en K:V nog verder uitsplitsen en voor de leesbaarheid geen onwaar gebruiken, maar simpelweg het aantal uren tussen 6u en 7u tellen: dat kan dus vaak 0 zijn.
Het idee is verder helemaal goed, volgens mij. (Alleen kolomF snap ik niet).

kolom F had ik toegevoegd om een concatenate te maken van de datum en tijd omdat ie niet goed rekende met uren/minuten. maar door die *24 te doen omzeil je dat. die kan weg dus nu...

het is ook dat de tijden in TIME worden genoteerd, maar als de uren uit elkaar worden getrokken, hij het beste rekent met gewone getallen. maar die laten zich dan weer niet van elkaar aftrekken...

[ Voor 8% gewijzigd door fabstar81 op 07-04-2010 21:50 ]

[F_J_K]

Nee, je wilt juist altijd met tijd-opmaak werken en nooit met integers. Onder de motorkap is een tijd ook gewoon en getal (zie ook wat er gebeurt als je 12:30 omzet naar GETAL).

Anyway, waar zit je nu nog vast?

Mijn suggestie over splitsen is overigens juist om alle keuzes (zoals ook voor/na middernacht en wel/niet weekend) zo simpel mogelijk te maken. Als alles werkt kan je (als het aantal kolommen al uitmaakt) altijd nog weer samenvoegen.

[fabstar81]

maar als ik de berekeningen doe (resultaat formules) komen er dus rare getallen uit. 4,0234 etc.

ik zit dus voornamelijk vast of ik de formules helemaal opnieuw ga schrijven. eerst proberen om in alle kolommen iig de juiste aantal uren in tijdsformaat te laten noteren. daarna de IF's doen vwb de dag van de week en overslag na twaalfen.

deze formule:

code:

=IF(AND($C6>=I$3;$C6<=J$3);(J$3-$C6+IF($C6>J$3;1))*24;0)

op I6 (i6) komt 1 uit. en die krijg ik met geen mogelijkheid op 1:00:00, dit zorgt er dus voor dat mn simpele berekening van uren gewerkt - onregelmatige uren niet uitgevoerd kan worden.

[ Voor 28% gewijzigd door fabstar81 op 07-04-2010 22:38 ]

[pedorus]

fabstar81 schreef op woensdag 07 april 2010 @ 22:33:
deze formule:

code:

=IF(AND($C6>=I$3;$C6<=J$3);(J$3-$C6+IF($C6>J$3;1))*24;0)

op I6 (i6) komt 1 uit. en die krijg ik met geen mogelijkheid op 1:00:00, dit zorgt er dus voor dat mn simpele berekening van uren gewerkt - onregelmatige uren niet uitgevoerd kan worden.

Je moet ook niet in uren rekenen, maar in dagen van 24u. Al laat je die *24 weg, kun je dit wel als 1:00 laten zien.. Alternatief zou je op het einde nog door 24 kunnen delen, maar dat is suf als je eerst *24 doet..

[fabstar81]

ok, dat helpt ja.
eerst de formules bakken zonder die 24. dan lukt het inderdaad om de tijd juist te laten zien.
nu nog de formules goed in elkaar knutselen met alle if's...

damn...da's nie gemakkelijk. ben ze nu opnieuw aan het schrijven per tijdvak. als daar de uren goed uitkomen in alle gevallen (=hell), dan ga ik de validaties inbouwen of ie die uren dan moet laten zien adhv de dag van de week.

ik dacht serieus dat dit niet zo moeilijk zou zijn...ben er al meer uren mee bezig dan ik zou denken...

ik loop tegen allerlei problemen aan. de formule

code:

=IF(AND($D6>=K$3;$D6<=L$3);(L$3-$D6);IF($D6>L$3;2;0))

werkt al niet als D eindtijd is en K en L begin en eindtijd van het tijdvak. iedereen vult zn uren nl in zoals ie gewend is: 8:00 - 23:00. maar wat nou als de eindtijd na het tijdvak ligt? dan moet ie automatisch de uren vullen waar dat tijdvak van is. in mijn ma tm vrij voorbeeld, 2 uur in het avondtarief van 20-22. hij zou automatisch 2 moeten vullen omdat je hebt gewerkt tot 23.00. je loopt echter zoals je ziet in de formule tegen een probleem aan. hij komt nooit in de 2e IF.

zal het lukken als ik voor elke IF een aparte kolom ga maken? je omzeilt dan iig de situatie dat iemand altijd in ronde uren zijn tijden opschrijft. begintijd is vanaf 22 uur. maar eindtijd kan ook op 22 uur liggen. de formule kan daar alleen geen rekening mee houden.

[ Voor 57% gewijzigd door fabstar81 op 07-04-2010 23:37 ]

[fabstar81]

even opnieuw beginnen:

laten we maar met 1 ding beginnen. stel je volgende regel voor: 22% extra voor uren gemaakt tussen 06:00 uur en 07:00 uur en tussen 20:00 uur en 22:00 uur op maandag tot en met vrijdag (op dagen ga ik later valideren, eerst maar dit aan de gang krijgen)

ik heb dan volgens mij deze situaties:

Begintijd ligt tussen 6 en 7
Eindtijd vorige dag ligt tussen 6 en 7
Eindtijd vorige dag ligt na 7, maar begintijd is voor 7 begonnen met een max tot 6
Begintijd ligt voor 6, maar loopt tot na 6 (tot 7, of later)
Begintijd ligt voor 20, maar loopt tot na 20 (tot 22, of later)
Begintijd ligt tussen 20 en 22
Eindtijd ligt tussen 20 en 22
Eindtijd ligt na 22, maar begintijd is voor 22 begonnen met een max tot 20

volgens mij heb ik zo alle situaties opgesomd. het makkelijkste lijkt mij om nu voor elke situatie een formule te schrijven in een aparte kolom?