Excel-tijdformules: tijd in Excel berekenen, opmaken en bewerken

Excel maakt werken met getallen makkelijk, maar met tijd omgaan is (vind ik) een ander verhaal. Het verwarrende deel (waar ik hieronder op inga) is dat Excel tijdwaarden opslaat en verwerkt als fracties van dagen, wat tot onverwachte resultaten kan leiden als je dat niet weet.

In deze tutorial laat ik je alles zien: van het aanmaken en opmaken van tijdwaarden tot het oplossen van veelvoorkomende problemen zoals negatieve duur en tijd die over de dag heen loopt. Aan het einde kun je slimmere, betrouwbaardere spreadsheets bouwen voor projectplanning, loonadministratie of waar je ook mee bezig bent.

Het datum­stelsel van Excel begrijpen

We moeten beginnen met het datumstelsel van Excel. Excel slaat datums op als opeenvolgende serienummers, beginnend op 1 januari 1900, dat wordt weergegeven door de waarde 1. Elke volgende dag verhoogt het serienummer met 1. Bijvoorbeeld:

• 1 januari 1900 = 1

• 2 januari 1900 = 2

• 31 december 2023 = 45205

Tijdwaarden worden opgeslagen als fracties van een dag. Bijvoorbeeld:

• 6:00 uur 's ochtends = 0.25 (6 uur is 25% van een dag van 24 uur)

• 12:00 uur 's middags = 0.5

• 18:00 uur = 0.75

Datums en tijden combineren is zo simpel als de twee waarden bij elkaar optellen. De onderstaande formule combineert bijvoorbeeld een datum en tijd:

=A2 + TIME(10, 30, 0)

Als A2 45205 (31 december 2023) bevat, is het resultaat 45205.4375, wat 10:30 uur 's ochtends op 31 december 2023 voorstelt.

Tijdwaarden maken in Excel

Laten we kijken naar de twee meest voorkomende manieren om geldige tijdwaarden in Excel te maken: met de functie TIME() en door tekst te converteren met TIMEVALUE().

Gebruik de functie TIME()

Deze noemde ik al. Met de functie TIME() bouw je een tijdwaarde uit losse onderdelen: uur, minuut en seconde. De syntaxis is als volgt:

=TIME(hour, minute, second)

Als je bijvoorbeeld 10:30 uur 's ochtends wilt weergeven, kun je schrijven:

=TIME(10, 30, 0)

Dit creëert een geldige Excel-tijdwaarde voor 10:30 uur 's ochtends, die intern wordt opgeslagen als 0,4375 (omdat 10 uur en 30 minuten 43,75% van een dag van 24 uur is).

Excel is flexibel met de waarden die je invoert. Als je normale tijdsgrenzen overschrijdt, rolt Excel de extra tijd door. Bijvoorbeeld:

=TIME(27, 0, 0)

Dit geeft 3:00 uur 's ochtends terug, omdat 27 uur gelijkstaat aan één volledige dag (24 uur) plus 3 uur. Op dezelfde manier:

=TIME(10, 75, 0)

Dit wordt geïnterpreteerd als 11:15 uur 's ochtends, omdat 75 minuten 1 uur en 15 minuten wordt.

Dit soort randgevallen is belangrijk om te begrijpen, vooral als je tijdwaarden dynamisch met formules genereert.

Gebruik de functie TIMEVALUE()

Je kunt tekst naar tijd converteren met TIMEVALUE(). Als je spreadsheet tijd bevat die als tekst is geschreven, zoals “2:30 PM” of “14:45”, behandelt Excel dat niet altijd automatisch als tijdwaarde. Daar komt TIMEVALUE() om de hoek kijken.

=TIMEVALUE("2:30 PM")

Dit zet de tekenreeks om in een Excel-tijdwaarde. Je kunt die nu net als elke andere tijd in berekeningen gebruiken.

Het is de moeite waard om het verschil tussen TIME() en TIMEVALUE() te benoemen:

• TIME() bouwt een tijd uit losse getallen (uur, minuut, seconde)

• TIMEVALUE() zet een teksttekenreeks om in een tijd

Beide geven waarden terug die Excel herkent als geldige tijden, en dat is wat je nodig hebt voor nauwkeurige berekeningen en opmaak.

Tijdsverschillen berekenen

Zodra je geldige tijdwaarden in Excel hebt, is een van de meest voorkomende taken berekenen hoeveel tijd er verstreken is tussen twee momenten. Of je nu de lengte van een dienst meet of de tijd om een taak te voltooien, Excel biedt meerdere manieren om het antwoord te krijgen.

Laten we de nuttigste aanpakken verkennen, van eenvoudige aftrekking tot wat geavanceerdere trucjes.

Twee tijden direct aftrekken

De meest directe manier om een tijdsverschil te berekenen is de begintijd van de eindtijd aftrekken. 

Als bijvoorbeeld cel A1 10:00 uur aangeeft en B1 13:30 uur, ziet de formule er zo uit:

=B1 - A1

Het resultaat is 0,145833. Dat lijkt misschien vreemd, maar onthoud dat Excel tijd weergeeft als een fractie van een dag. In dit geval komt 0,145833 overeen met 3 uur en 30 minuten.

Om het resultaat nuttiger te maken, wil je het meestal omzetten naar een leesbaar formaat.

Tijdsverschillen omzetten naar uren, minuten of seconden

Je kunt het resultaat van een tijdaftrekking vermenigvuldigen om het verschil in uren, minuten of seconden uit te drukken. Zo zet je een tijdsverschil om naar uren:

=(B2 - A2) * 24

Zo zet je een tijdsverschil om naar minuten:

=(B2 - A2) * 1440

Zo zet je een tijdsverschil om naar seconden:

=(B2 - A2) * 86400

Deze formules werken omdat er 24 uur, 1.440 minuten en 86.400 seconden in een dag zitten. Als het tijdsverschil drieënhalf uur is, krijg je 3,5 met de urenformule, 210 met de minutenformule en 12.600 met de secondenformule.

Specifieke tijdseenheden extraheren

Als je slechts één deel van de tijd wilt ophalen, bijvoorbeeld alleen de minuten of uren, kun je HOUR() gebruiken. HOUR() geeft het uurgedeelte van een tijd terug.

=HOUR(B2 - A2)

MINUTE() geeft het minutengedeelte terug.

=MINUTE(B2 - A2)

SECOND() geeft de seconden terug.

=SECOND(B2 - A2)

Deze functies zijn handig, maar ze resetten na 23 uur, 59 minuten of 59 seconden. Als de duur langer is dan een volledige dag, geven ze dus niet de totale verstreken tijd weer. In die gevallen kun je beter het verschil vermenigvuldigen of aangepaste notaties gebruiken, waar we later op ingaan.

Tijdwaarden boven 24 uur optellen

Als je uren moet optellen die boven de 24 uur uitkomen, werkt direct gebruik van TIME() niet zoals verwacht. In plaats daarvan kun je rekenen met fracties van een dag.

Zo tel je uren boven 24 op.

=A2 + (28/24)  ' Adds 28 hours

Zo tel je minuten boven 60 op.

=A2 + (90/1440)  ' Adds 90 minutes

Zo tel je seconden boven 60 op.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel slaat tijd op als een decimaal, waarbij 1 dag gelijk is aan 1, dus delen door 24, 1440 of 86400 past de waarde aan om een deel van een dag weer te geven.

Negatieve tijdwaarden afhandelen

Standaard, als je een grotere tijd van een kleinere tijd aftrekt (bijv. 10:00 AM - 2:00 PM), geeft Excel een fout of een reeks ### weer.

Om negatieve tijdwaarden veilig af te handelen, gebruik je de functie MOD(). Deze functie voorkomt effectief negatieve resultaten door ze terug te vouwen naar een geldige tijd.

Om bijvoorbeeld veilig 3 uur af te trekken:

=MOD(A2 - TIME(3, 0, 0), 1)

Deze formule zorgt ervoor dat het resultaat altijd een positieve tijdwaarde is, waardoor ongewenste fouten of weergaveproblemen worden voorkomen.

Tijdresultaten correct opmaken

Tijdberekeningen in Excel leveren vaak verwarrende decimale waarden of onverwachte resultaten op, vooral bij duur langer dan 24 uur of negatieve tijdwaarden. Met de juiste opmaak verander je ruwe uitkomsten in leesbare, gebruiksvriendelijke formaten die makkelijker te interpreteren en te presenteren zijn.

Basisopmaak voor tijd gebruiken

Als je een tijdsverschil als “3:30” wilt weergeven in plaats van een decimaal, gebruik dan de functie TEXT().

=TEXT(B2 - A2, "h:mm")

Dit formatteert het resultaat als uren en minuten. Houd er wel rekening mee dat de uitvoer tekst is: het ziet er goed uit, maar je kunt het niet in verdere berekeningen gebruiken tenzij je het terug converteert.

Aangepaste tijdsnotaties toepassen

De ingebouwde tijdsnotaties van Excel werken goed voor standaardtijd, maar aangepaste notaties bieden meer flexibiliteit voor het weergeven van duur, uitgebreide uren of specifieke tijdsonderdelen.

Zo pas je een aangepaste tijdsnotatie toe:

1. Selecteer de cel(len) met de tijdwaarden.
2. Klik met de rechtermuisknop en kies Celeigenschappen.
3. Selecteer op het tabblad Getal de optie Aangepast.
4. Voer de gewenste code voor de notatie in.

Hier zijn enkele veelvoorkomende aangepaste tijdsnotaties:

• [h]:mm: Geeft totale uren en minuten weer, ook voorbij 24 uur. Voorbeeld: 36:45 (36 uur en 45 minuten)

• d "days" h:mm:ss: Geeft dagen, uren, minuten en seconden weer. Voorbeeld: 1 days 12:30:00

• h:mm AM/PM: Geeft tijd weer met AM/PM-notatie. Voorbeeld: 10:30 PM

• mm:ss: Geeft alleen minuten en seconden weer. Voorbeeld: 45:15

Met aangepaste notaties kun je tijdsgegevens presenteren op manieren die intuïtiever zijn voor specifieke toepassingen, zoals urenstaten of werklogs.

Vriendelijke tijdsverschillen weergeven

Als je een tijdsverschil wilt weergeven als een goed leesbare tekst, zoals “2 dagen, 4 uur en 15 minuten”, kun je meerdere functies in één formule combineren.

Hier is een praktisch voorbeeld:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

In deze formule haalt de functie INT() het aantal volledige dagen op, terwijl HOUR() en MINUTE() de resterende uren en minuten ophalen.

Om te voorkomen dat nullen in de uitvoer verschijnen, kun je deze berekeningen nesten in IF()-verklaringen. Bijvoorbeeld:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Deze formule toont alleen relevante eenheden, waardoor de uitvoer schoner en beter leesbaar is.

Belangrijke Excel-tijdfuncties om te kennen

Zoals je tot nu toe hebt gezien, bevat Excel een krachtige set tijdfuncties. In het belang van een completer overzicht, hier is een korte beschrijving van de belangrijkste Excel-tijdfuncties.

Tijdwaarden maken met de functie TIME()

TIME() construeert een geldige tijdwaarde uit drie onderdelen: uren, minuten en seconden. Deze functie is ideaal als je programmatisch een tijdwaarde moet opbouwen.

Hier is een goed voorbeeld:

=TIME(14, 30, 0)  

Bovenstaande geeft 14:30 uur (2:30 PM) terug, als resultaat van deze syntaxis.

=TIME(hour, minute, second)

Tekst naar tijd converteren met TIMEVALUE()

Als je een tijd als teksttekenreeks hebt (bijv. "2:45 PM"), gebruik dan TIMEVALUE() om die te converteren naar een geldige Excel-tijdwaarde die je in berekeningen kunt gebruiken.

=TIMEVALUE("2:45 PM")

Dit geeft bijvoorbeeld 0.614583 terug, wat een fractie van een dag is:

=TIMEVALUE("14:45")

De huidige datum en tijd ophalen met NOW()

NOW() geeft de huidige datum en tijd terug als één waarde. Deze wordt automatisch bijgewerkt wanneer het werkblad herberekent.

=NOW()

Hier is een goed voorbeeld. Als de huidige tijd 15:15 uur is, kan NOW() 44710.63542 teruggeven, waarbij 44710 de datum is en .63542 de tijd weergeeft.

De huidige datum ophalen met TODAY()

Als je alleen de huidige datum zonder tijd nodig hebt, gebruik dan TODAY(). Deze functie is vooral handig voor het berekenen van deadlines of het ouder worden van data.

=TODAY()

Tijdsonderdelen ophalen met HOUR(), MINUTE() en SECOND()

Met deze functies kun je specifieke delen van een tijdwaarde isoleren, wat handig is voor rapportage en conditionele logica.

HOUR() geeft het uurgedeelte van een tijdwaarde (0-23) terug.

=HOUR(A2)

MINUTE() geeft het minutengedeelte (0-59) terug.

=MINUTE(A2)

SECOND() geeft het secondengedeelte (0-59) terug.

=SECOND(A2)

Tijdwaarden opmaken met TEXT()

TEXT() zet een tijdwaarde om naar een specifiek formaat, ideaal om duur weer te geven of aangepaste tijdstrings te maken. Syntaxis:

=TEXT(A2, "h:mm:ss AM/PM")

Negatieve tijdwaarden afhandelen met TEXT()

Bij het aftrekken van tijdwaarden zorgt MOD() ervoor dat het resultaat altijd positief is, zodat fouten door negatieve tijdwaarden worden voorkomen.

=MOD(A2 - B2, 1)

Datumwaarden maken met DATE()

DATE() laat je een datum maken uit losse onderdelen: jaar, maand en dag. Dit is handig om datums en tijden in één waarde te combineren.

=DATE(year, month, day)

Hier is een goed voorbeeld:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Wat 31-12-2023 14:30 oplevert.

Werkdagen creëren met NETWORKDAYS()

NETWORKDAYS() berekent het aantal werkdagen tussen twee datums, exclusief weekenden en optioneel opgegeven feestdagen. Deze functie is ideaal voor het berekenen van projectduren of loonperiodes.

=NETWORKDAYS(start_date, end_date, [holidays])

Bijvoorbeeld:

=NETWORKDAYS(A2, B2, C2:C5)

Als A2 12/1/2023 bevat en B2 12/31/2023, is het resultaat het aantal werkdagen in december, exclusief feestdagen die in het bereik C2:C5 staan.

Werkdagen en weekenden instellen met WORKDAY.INTL()

WORKDAY.INTL() is een geavanceerde versie van WORKDAY() waarmee je kunt aanpassen welke dagen als weekend gelden.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Bijvoorbeeld:

=WORKDAY.INTL(A2, 10, "0110000")

Hier geeft 0110000 aan dat dinsdag (0) en woensdag (1) weekenden zijn. De formule berekent de datum 10 werkdagen na A2, exclusief dinsdag en woensdag.

Conclusie

Met tijd omgaan in Excel kan ingewikkeld aanvoelen, maar met de juiste functies wordt het een stuk beheersbaarder.

Wil je je Excel-vaardigheden verder versterken, bekijk dan onze Introduction to Excel voor basisvaardigheden of de cursus Data Analysis in Excel om te oefenen met data uit de echte wereld.

Voor meer gevorderde toepassingen biedt de skill track Excel Fundamentals begeleide projecten en oefeningen om je te helpen tijdcalculaties en meer te beheersen.