Som.als met tijd: uren nachtdienst van dagdienst scheiden

[l3ob]

Hallo,

Ik wil graag uitrekenen in een Excel 2010 hoeveel nachttoeslag ik krijg. Kan iemand mij alsjeblieft helpen met deze raadsels? Ik kom er niet uit!

Ik krijg tussen 00:00 uur en 8:00 uur nachttoeslag.
Bij een nachtdienst van 1:00uur tot 9:00uur heb ik dus 8 uur gewerkt en krijg ik voor 7 uren een nachttoeslag.
Ik probeer dit te doen met de =som.als( functie, maar kom er totaal niet uit hoe. Hoe vertaal ik: 'Tel op hoeveel uren er van deze twee tijden (start - en eindtijd van dienst) tussen 00:00 en 8:00uur vallen?'

Een ander vraagje is hoe ik avond tot ochtend uren bij elkaar kan optellen. Ik gebruik nu eenvoudig Eindtijd - Starttijd = Totaal, maar dat werkt niet als een dienst om 23:00uur s avonds begint en in de ochtend eindigt!..

Heb een voorbeeld documentje toegevoegd, waarin de grijze cellen voor mij de raadsels zijn.
Bekijk bijlage Voorbeeld berkening nachttoeslag.xlsx

 

[plongske]

Zie bijlage

 

[abevleeming]

Aanvullend op bijlage plonske:

De urentotaal iets korter:

=REST(C2-B2;24)

En mocht je geen hulpcellen willen gebruiken voor nachttoeslag

=ALS($C2<$B2;MAX(0;MIN("8:00";1)-MAX("0:00";$B2))+MAX(0;MIN("8:00";$C2)-MAX("0:00";0));MAX(0;MIN("8:00";$C2)-MAX("0:00";$B2)))

 

[zapatr]

De urentotaal iets korter:

=REST(C2-B2;24)

Dat is geen juiste oplossing voor alle situaties.
Voer als begin- en eindijd maar eens een tijden in van resp. vóór- en na 24.00 uur
Er zijn overigens wel iets kortere oplossingen mogelijk voor zowel 'uren totaal' als voor het aantal uren dat de nachttoeslag aangeeft.

 

[abevleeming]

Om het totaal te bereken pakt ie inderdaad een onjuiste waarde, deze doet dat beter/wel goed:

=REST(C2-B2;1)

 

[l3ob]

Ik heb even gewacht voor het weer tijd weer was voor de administratie, maar nu de uren van augustus ingevuld moeten worden kan ik er niet meer omheen Ik heb veel ontzag voor jullie begrip van excel. Ik dacht dat ik wel slim was, maar de logica van excel gaat mij blijkbaar toch echt een stap te ver. Ik snap er bijvoorbeeld geen hout van hoe de functie 'rest' "het restgetal bij de deling van een getal door een deler", terwijl er alleen wordt opgeteld en afgetrokken met tijd (?). Hoe dan ook, het werkt en dat vind ik er erg leuk.

Nu ik de uren van deze maand heb ingevuld zie ik dat ik een paar dingen over het hoofd heb gezien. Als ik nu bijvoorbeeld een dagdienst heb en ik begin een kwartier eerder, wordt een kwartier tijd als nachttoeslag gerekend. Ik heb niet aan mijn baas gevraagd wat de officiele tijd voor het nachttarief is, maar denk dat ik er vanuit kan gaan dat dit de gehele nachtdienst is van 23:00 tot 8:30 's ochtends. Alleen als ik dit aanpas in de 'als' regels/functie dan komt alles op 00:00 te staan

Iemand een idee hoe dit (eenvoudig) op te lossen?

Zie ook bijlage

 

[plongske]

Ik heb niet aan mijn baas gevraagd wat de officiele tijd voor het nachttarief is

Toch maar even navragen bij uw baas wat de officiële tijden zijn.
Er zullen dan waarschijnlijk ander toeslagen bijkomen. Ik kan me niet voorstellen dat een dagdienst dan van 8:00 tot 23:00 loopt.
Het lijkt mij ook logisch dat, als je iets vroeger begint en die tijd valt dan tijdens de nachtdienst, dat je die tijd vergoed wordt

 

[plongske]

Ik heb niet aan mijn baas gevraagd wat de officiele tijd voor het nachttarief is

Toch maar even navragen bij uw baas wat de officiële tijden zijn.
Er zullen dan waarschijnlijk ander toeslagen bijkomen. Ik kan me niet voorstellen dat een dagdienst dan van 8:00 tot 23:00 loopt.
Het lijkt mij ook logisch dat, als je iets vroeger begint en die tijd valt dan tijdens de nachtdienst, dat je die tijd vergoed wordt

 

[plongske]

Plaats deze in E2 en doortrekken naar beneden.

=(MAX(0;MIN("8:30"+(C2<B2);C2+(C2<B2))-MAX(0+(C2<B2);B2))+MAX(0;MIN(1;C2+(C2<B2))-MAX("23:00";B2)))

 

[VenA]

Even obv jouw eerste voorbeeldje. Hoe kan je op zondag 02-08 om 23:00 starten en om 08:15 eindigen. Dan ben je denk ik op maandag 03-08 beland? Maar op 03-08 is de begintijd 00:15? Dit soort dingen zie ik ook weer terug komen in jouw laatste voorbeeldje.

Om de berekeningen eenvoudig te houden, houdt je aan het 24 uurs systeem. Een dag begint om 0:00 uur en eindigt om 23:59 uur. (even los van laatste minuut) Je splitst dan de 'diensten' op per datum. In het voorbeeldje de eerste tabel in H1.

Of je voert een datum en tijd in bij start en eind. (zie tweede tabel) Maar deze maakt het alweer een stuk complexer om de nachtdiensten eruit te halen. (heb ik dus ook geen formule gemaakt)

 

[l3ob]

Plongske, u bent geniaal. Hij werkt perfect! ))

Nu ik heb waar ik naar op zoek was, wordt ik een beetje nieuwsgierig hoe ik een vraagstuk als dit de volgende keer zelf op zou kunnen lossen. Mag ik vragen hoe je bij zo'n perfect werkende formule komt? Ik bedoel, schrijf je dat zo uit je hoofd; hoe veel tijd kost dit voor jou?
Ik probeer de PHP regel die je net noemde te bevatten, om te vormen in menselijke taal, maar ik kom er echt niet bij met mn gedachte.

antwoord in cel = maximale getal van (twaalf uur s nachts OF minimale getal van (half acht PLUS waar of onwaar OF tijd in cel c2 PLUS waar of onwaar)MIN (nul PLUS waar of onwaar OF tijd in cel B2) + maximale getal van (twaalf uur s nachts OF minimale getal van (één (???) OF tijd in cel c2 PLUS waar of onwaar) MIN maximale getal van (23:00uur OF waarde in cel b2))

Dit uitschrijven en onderverdelen in kleuren kost mij al een kwartier. Het helpt wel om het te begrijpen, maar snappen doe ik het nog niet. Plongske, wat gebeurt er nu eigenlijk in de formule. En hoe heb jij deze taal van Max, Min en Als etc ooit geleerd??

 

[l3ob]

Vena, dank voor je suggestie. Zo zou ik het inderdaad ook kunnen doen (al weet ik niet of bovenstaande formules dan nog zouden werken).

De starttijden van het lijstje van 1,2 en 3 aug zou dan zo worden:
zaterdag 1 augustus 0:30
zaterdag 1 augustus 23:00
maandag 3 augustus 00:15

Zou technisch gezien wel beter zijn, maar voor het oog is het niet echt overzichtelijker, aangezien het 3 nachtdiensten zijn die elkaar opvolgen (vrijdag op zaterdag-, zaterdag op zondag- en zondag op maandag-nacht). Op de werkvloer is hier ook regelmatig verwarring over, voornamelijk rond de vroegen uren (zeg je nu goedenavond, goedenacht of goedemorgen als een klant om bijvoorbeeld 2:30uur aankomt? en als een collega om 6:30uur tegen je zegt dat hij morgen een avonddienst heeft (die van 17:00 tot 23:00uur duurt), bedoelt hij dan met morgen nadat hij geslapen heeft (10,5 uur na nu) of 'echt' morgen en dat hij over 34,5 uur later vanaf nu weer moet werken, aangezien je beide al minimaal sinds de avond wakker bent? (ik weet het, het is niet echt relevant voor excel, maar deze gevoelens van verwarring wilde ik toch even kwijt, nu ik toch op een forum zit :d)

 

[plongske]

Ik schrijf dit zeker niet uit mijn hoofd
Dit zijn formules die ik heb opgeslagen op mijn computer, maar de basis komen van collega's uit de forums waar ik actief op ben.
Wat je vooral moet onthouden is dat Excel rekent met getallen en dat datums en tijden omgezet worden naar getallen.
1 is voor Excel( in datumnotatie) 1 jan 1900. 2 staat voor 2 jan 1900(dus voor excel is 24u gelijk aan 1), voor vandaag is dit het getal 42257.Tijden worden weer gegeven door cijfers na de komma. Daardoor kunnen we door de restfunctie die AB Vleeming aanbracht, de tijden berekenen.

antwoord in cel = maximale getal van (twaalf uur s nachts OF minimale getal van (half acht PLUS waar of onwaar OF tijd in cel c2 PLUS waar of onwaar)MIN (nul PLUS waar of onwaar OF tijd in cel B2) + maximale getal van (twaalf uur s nachts OF minimale getal van (één (???) OF tijd in cel c2 PLUS waar of onwaar) MIN maximale getal van (23:00uur OF waarde in cel b2))

De nul in de max. van de formule is een fout afhandeling .
De waar of onwaar geeft te maken met het feit dat er al dan niet een dag(1) moet worden bijgeteld, gezien de eindtijd al dan niet de volgende dag is.
die 1 staat gewoon voor een volgende dag.
Laat maar horen indien dit onvoldoende is.

 

[prutsmonteur]

aanpassinkje

Beste Plongske,

Ik heb de file zoals jullie bespreken gedownload en wat aangepast.
Het lukt mij echter niet deze zodanig aan te passen dat de gewerkte uren opgesplitst gaan worden in de juiste tijdzone's
In dit voorbeeld heb ik uren ingevuld die redelijk overeen komen met de realiteit.
Het doel van deze file is mijn gewerkte uren zo compleet mogelijk aan te leveren bij het bedrijf waar ik voor werk.
Mijn vraag is als eerste of ik de door jullie gemaakte file mag gebruiken, en mijn tweede vraag is of je me wil helpen deze file helemaal werkend te krijgen.
Ik heb natuurlijk wel wat geprobeerd, maar ik kom er niet echt helemaal uit.

bij voorbaat dank,

Joop de prutsmonteur.Bekijk bijlage urenregistratie.xlsx

 

[plongske]

Joop,
Allereerst welkom op het forum.
Natuurlijk mag je de files gebruiken
Hierbij uw versie aangepast.

 

[prutsmonteur]

dat is snel

Beste Plongske,

Dat is wel een heel snel antwoord, dankjewel daarvoor.
Ik ga nu proberen of het me lukt de toeslag in euro's te berekenen(daar gaat het ten slotte om)

Dank en vriendelijke groet,

Joop