Symptomen
- De gebruiker houdt in een rekenblad het aantal gewerkte uren van de medewerkers in de organisatie bij, en berekent daarna de loonkosten. Maar in de totaalberekening leidt de urentelling niet tot de juiste bedragen.
Remedie
Twee zaken kunnen het probleem veroorzaken: de opgetelde uren vermenigvuldigen met bedragen, en de urentelling die over de daggrens gaat. Hieronder de oplossing voor beide.
Verdieping
Rekenen met tijdwaarden is – net als met datums – wat lastiger dan met ‘gewone’ getallen. Dat heeft te maken met de afwijkende getalsystemen waar u dan mee rekent: een dag kent nu eenmaal 24 uur en een uur 60 minuten. Zolang u binnen de hoeveelheid van 24 uren blijft en u niet over de daggrens gaat, kunt u berekeningen op de tijdwaarden toepassen alsof het gewone getallen waren. Zo kunt u werktijden (die in cellen staan van een rekenmodel) gewoon van elkaar aftrekken en zo uitrekenen hoeveel uren of minuten tussen begin en eindtijd zijn verstreken.
In onderstaande figuur ziet u hoe de begin- en eindwerktijden en de lengte van de pauzes als uren worden genoteerd in een model en hoe dan het aantal gewerkte uren wordt uitgerekend met behulp van de formule in de formulebalk.
De formule (zie de formulebalk voor cel F5) moge duidelijk zijn: eindtijd minus begintijd minus pauze, levert 5½ gewerkte uren op.
Maar zodra het aantal uren over de daggrens heen gaat (24:00/0:00 uur) kan het rekenprogramma geen waarde weergeven die binnen de klok valt, omdat de formule een negatieve waarde oplevert. Om dit te voorkomen kunt u de berekening als volgt formuleren:
=(D5-C5+(D5<C5))-E5
Hierin wordt gekeken of de eindtijd kleiner is dan de begintijd (D5<C5), want dan moet dat deel van 24 uur bij het verschil tussen begin- en eindtijd worden opgeteld.
Een andere kwestie is of u de uren- en kostenberekening voor de week in één keer zou willen doen en niet per dag zoals in het voorbeeld hierboven. Daarbij is het waarschijnlijk dat het aantal uren hoger wordt dan 24 en als u daar geen rekening mee houdt, wordt dat niet juist weergegeven en gebruikt bij de berekening van de bedragen. In de eindformule voor de hele week mag u een compensatie voor daggrensoverschrijding zoals hierboven niet meenemen in de berekening. Dus als u de werkuren voor de hele week gaat optellen van iemand die vijf dagen in de week 8 uur werkt, dan is de som: 1 etmaal plus 16 uur, en de urentelling moet dan op 40 uur uitkomen. Het model invullen per werkdag is waarschijnlijk handiger te gebruiken en geeft meer inzicht in hoe de totalen tot stand komen. Eventueel zou u de ‘dagtotalen’ nog kunnen verbergen (als kolom).
De berekening van het aantal gewerkte uren in de week kan met een voor de handliggende formule, zoals u in onderstaande figuur kunt zien bijvoorbeeld met een =SOM()-functie.
De zeven opgetelde dagtotalen in cel AE5 – vijf werkdagen van 8 uur en een vrij weekend – geeft als uitkomst ofwel 1,67 ofwel 16:00(:00). De eerste uitkomst is het geval als u de Getalnotatie van cel AE5 heeft ingesteld op Getal. De tweede uitkomst verschijnt als u nog geen notatie had ingesteld (dan stelt Excel dat voor u in op formaat uu:mm:ss), of als u zelf voor een urennotatie uu:mm had gekozen.
Maar beide uitkomsten zijn niet goed. De uitkomst 1,67 is het gevolg van de manier waarop Excel rekent met tijdwaarden. Daarvoor gebruikt het programma getallen tussen 0 en 1: zo komt 12 uur overeen met 0,5 en 8 uur met (afgerond) 0,333 uur, et cetera. De waarde 1 vertegenwoordigt 24 uur, en 1,67 komt zodoende overeen met 40 uur. Datumwaarden werken vergelijkbaar, van elke datum zijn de decimalen dus de uren, minuten en seconden van die dag (datum).
Om de onderliggende waarde waarmee Excel rekent zichtbaar te maken, zou u voor een van de dagtotalen even in een lege cel een omzettingsfunctie kunnen gebruiken: voor het dagtotaal van maandag bijvoorbeeld de formule =WAARDE(F5). Die levert als uitkomst een getal tussen 0 en 1 op.
De uitkomst in urenopmaak klopt ook niet, en eigenlijk om dezelfde reden: alleen de uren (0,67) en niet het etmaal (1,0 = 24 uur) wordt door de Getalnotatie weergegeven.
Maar met al deze optellingen en weergaven is het uurloon (formule natuurlijk [Aantal uren] maal [Uurloon]) niet juist te berekenen.
In principe zou u nu alle tijden in de berekeningen met functies als hierboven tot de gewenste aantallen kunnen omvormen, maar de simpelste manier om in cel AE5 van het voorbeeld het juiste aantal uren te verkrijgen, is door de uitkomst met 24 te vermenigvuldigen en de Getalnotatie in te stellen op Getal (eventueel met decimalen). Dan luidt de formule voor het gewenste uurloon dus:
=SOM(F5;J5;N5;R5;V5;Z5;AD5)*24
Het resultaat van de formule is dan wel een aantal, dus 5,50 betekent dan 5½ uur (5 uur plus 0,5 van een uur) en 7,25 is dus 7 uur en één kwartier (0,25 van een uur). En dit aantal vermenigvuldigd met het uurloon in kolom B (zie eerste figuur) geeft in AF5 het juiste totaal.
Tip
Om de aantallen uren consequent vorm te geven in het model als aantallen uren met delen daarvan in de vorm van decimalen – in plaats van uren met minuten gescheiden door een dubbele punt:
- Vermenigvuldig in de formule in de dagtotalen alvast met 24, en wel als volgt:
=((D5-C5+(D5<C5))*24)-(E5*24). - Pas voor de betrokken cellen de Getalnotatie aan (Getal in plaats van Uren).
- Verwijder de vermenigvuldig met 24 uit de eindoptelling (als u die daar heeft gebruikt).
Zie ook
Afronding.
Datum en tijd.