Wil je weten hoe je gewerkte uren in Excel kunt berekenen? In dit artikel leer je stap voor stap hoe je eenvoudig het verschil tussen een begintijd en een eindtijd berekent. Dit kun je bijvoorbeeld gebruiken voor het bijhouden van een urenregistratie.
Maar wat als je over middennacht werkt? In dat geval werkt een simpele rekensom niet altijd, en moet je een iets andere formule gebruiken. Geen zorgen, ook dat wordt in dit artikel duidelijk uitgelegd!
Stappen om gewerkte uren te berekenen
De eerste stap is het invoeren van de tijden.
In dit voorbeeld wordt de begintijd ingevuld in cel A2. Bijvoorbeeld 9:00
De eindtijd wordt ingevuld in cel B2. Bijvoorbeeld 17:00.
Stap 2 is het verschil berekenen door middel van een formule.
In cel C2 wordt het verschil tussen de eindtijd en de begintijd berekent. Dit doe je met de eenvoudige formule =B2-A2
Het enige wat deze formule doet is dus de eindtijd aftrekken van de begintijd.
Standaard geeft Excel mogelijk het resultaat weer als een tijd, niet als een decimaal getal.
Als je het resultaat als een decimaal wilt weergeven (bijv. 8,5 uur in plaats van 8:30), kun je de formule vermenigvuldigen met 24. De formule wordt dan als volgt:
=(B2-A2)*24
De haakjes zijn hierbij van belang wegens de volgorde van bewerkingen. Eerst worden de haakjes weggewerkt en daarna volgt het vermenigvuldigen.
Om het resultaat goed weer te geven is het wel belang dat je voor de juiste celnotatie kiest.
- Wil je het resultaat tonen als een tijd? Kies dan voor Aangepast en kies in de lijst voor
[u]:mm - Wil je het resultaat tonen als een getal? Kies dan voor Getal, zoals in het onderstaande voorbeeld is weergegeven.
Zoals je kunt zien kan je het verschil tussen een begin- en eindtijd met een eenvoudige formule berekenen. Maar wat als je met nachtelijke uren te maken hebt?
Stel je begint om 22:00 en werkt tot 7 uur in de ochtend. Dan werkt deze formule niet meer:
Zoals je ziet is het resultaat een negatieve waarde, omdat de eindtijd op de volgende ligt.
Werken met nachtelijke uren
Dit probleem valt op te lossen met een kleine aanpassing in de formule, namelijk door 24 uur aan te eindtijd toe te voegen wanneer de eindtijd vóór de begintijd ligt.
Het is belangrijk om te begrijpen dat in Excel datums en tijden intern opslaat als getallen.
- 1 dag wordt weergegeven als het getal 1. Dit betekent dat 1 dag gelijk is aan 24 uur.
- 0,5 staat voor een halve dag, oftewel 12 uur.
- 0,25 staat voor een kwart dag, oftewel 6 uur.
Als je in cel A2 de tijd 22:00 hebt en in cel B2 02:00, en je de formule =B2-A2 gebruikt, krijg je een negatief resultaat, omdat Excel denkt dat 02:00 vóór 22:00 komt. Door 1 (oftewel 24 uur) toe te voegen met de formule =B2-A2+1, wordt het verschil correct weergegeven als 4 uur.
Je wilt echter alleen die +1 toevoegen indien er sprake is van nachtelijke uren.
En wanneer is dat het geval? Wanneer de eindtijd kleiner is dan de begintijd.
Als je dat weet, kun je Excel op 2 eenvoudige manieren enkel +1 laten toevoegen indien daar sprake van is.
Manier 1: de ALS functie
Met de ALS functie kun je in Excel een voorwaarde stellen en dan Excel laten weten wat er moet gebeuren indien er wel of niet aan die voorwaarde wordt voldaan.
In dit voorbeeld wordt de formule dan: =B2-A2+ALS(B2<A2;1;0)
Het eerste gedeelte van deze formule hebben we net al behandeld. Nu gaan we verder in op het tweede gedeelte met de ALS functie.
Deze functie bestaat uit 3 onderdelen:
- De voorwaarde: B2<A2. Hiermee controleert Excel of de eindtijd (in cel B2) eerder is dan de begintijd (in cel A2). Bij nachtelijk diensten is de eindtijd (bijvoorbeeld 07:00) namelijk kleiner dan de begintijd (bijvoorbeeld 22:00).
- Waarde als waar: Als inderdaad de inhoud van cel B2 kleiner is dan A2, dan voegt de formule 1 dag (24 uur) toe aan het verschil. Dit doe je door Excel de waarde 1 te tonen. Dan krijg je dus de volgende som: =B2-A2+1
- Waarde als niet waar: Als B2 niet kleiner is dan A2 (de eindtijd ligt na de begintijd op dezelfde dag), dan wordt 0 toegevoegd, omdat er geen correctie nodig is.
Op deze manier krijg je altijd het correcte aantal gewerkte uren, ook als de dienst doorloopt in de volgende dag. De SOM wordt dan dus: =B2-A2+1
Als er dus geen sprake is van nachtelijke uren, dan is de inhoud van cel B2 niet kleiner dan A2, en volgt er geen correctie en wordt de som =B2-A2+0
Manier 2: Logische expressie
Veel mensen gebruiken de ALS functie tijdens hun Excel werkzaamheden, maar je kunt ook hetzelfde bereiken zonder gebruik te maken van een functie. Namelijk door een logische expressie te gebruiken.
Dit werkt als volgt:
- WAAR wordt intern als Excel als 1 behandeld
- ONWAAR wordt intern als Excel als 0 behandeld
Als je dus een logische expressie maakt en volgens Excel is dat WAAR, dan krijg je een 1 als resultaat.
De logische expressie wordt in dit voorbeeld =(B2<A2)
Stel je hebt in cel A2 de begintijd 22:00 en in cel B2 de eindtijd 23:00. De eindtijd is dan dus niet kleiner dan de begintijd. De logische expressie (B2<A2) resulteert dan in ONWAAR. Dit wordt dus als 0 gezien door Excel.
Maar stel je hebt in cel A3 de begintijd 22:00 en in cel B3 de eindtijd 02:00. De tijd van cel B3 is hierbij kleiner dan de tijd in cel A3, dus (B3<A3) = WAAR, wat gelijk is aan 1.
Nu je dit weet, dan kun je dit gedeelte dus toevoegen aan de oorspronkelijke som, waardoor je de volgende formule krijgt:
=B2-A2+(B2<A2)
Steeds indien de eindtijd kleiner is dan de begintijd, krijg je +1 als correctie. En als dat niet het geval is +0, waardoor je dus wederom het juiste aantal gewerkte uren kunt berekenen.
Nachtelijke uren als decimaal
Eerder in dit artikel heb je kunnen leren dat je de som kunt vermenigvuldigen met 24 om het aantal gewerkte uren als getal weer te geven. Dit kun je ook combineren met de formule van de nachtelijke uren zoals net is uitgelegd. Het enige waar je op moet letten is het juist plaatsen van de haakjes, zodat het vermenigvuldigen als laatste plaats vindt.
De formule wordt dan dus:
=(B1-A1+(B1<A1))*24
Het eerste gedeelte B1-A1+(B1<A1) zorgt ervoor dat Excel correct rekent met nachtelijke uren door 1 dag (24 uur) toe te voegen wanneer de eindtijd vóór de begintijd ligt. En het tweede gedeelte *24 vermenigvuldigt het resultaat met 24 om het aantal uren als een decimaal getal weer te geven in plaats van in tijdnotatie.
Met deze formule krijg je altijd het juiste aantal gewerkte uren, ongeacht of de eindtijd over middernacht heen gaat, en het resultaat wordt weergegeven als een decimaal getal.
