Excel bevat een heleboel ingebouwde datum- en tijdfuncties waarmee je best complexe berekeningen kunt uitvoeren. Met enkele hiervan heb je op deze blog al kunnen kennismaken. Denk maar aan DATUMVERSCHIL(); die we gebruikt hebben voor het berekenen van iemands leeftijd of anciënniteit. Of NETTO.WERKDAGEN(), die we nodig hadden voor het  berekenen van het aantal werkdagen in een jaar. Ken je de functies NU() en VANDAAG() misschien al? Die hebben we gebruikt bij het maken van een timestamp.

Echter als je echt zelf aan de slag wil met datums, uren, minuten, enzovoort… is het belangrijk dat je begrijpt hoe Excel hiermee om gaat. Rekenen met datums en tijden is niet zo moeilijk eens je het onder de knie hebt.

Laat dat nu precies zijn wat we gaan bespreken in deze post.

Begrijpen hoe Excel rekent met datums en tijden

In Excel worden datums opgeslagen als gehele getallen en tijden als decimale getallen. Dat systeem zorgt ervoor dat we in Excel met tijden en datums kunnen rekenen; maar maakt het wel enigszins verwarrend.

Enkele voorbeelden die je kan uitproberen:

Voorbeeld 1: datum

1. Voer in een cel in Excel een datum in (bv. 08/10/2014).
2. Excel zal de datum in de cel laten staan.

3. Verander de getalopmaak naar ‘Standaard’. Je zal zien dat Excel geen datum bewaard heeft maar een geheel getal. In ons geval is dit 41920.

Voorbeeld 2: tijd

1. Voer in een cel in Excel een tijd in (bv. 13:30)
2. Excel zal de tijd in de cel laten staan

3. Verander de getalopmaak naar ‘Standaard”. Je zal zien dat Excel geen datum bewaard heeft maar een decimaal getal. In ons geval is dit 0,5625.

Hoe komt Excel nu eigenlijk aan deze getallen?

Wel; Excel begint te rekenen vanaf de datum 01/01/1900. Deze datum is gelijkgesteld met het getal 1. In ons eerste voorbeeld hebben we dus een datum ingevoerd die 41919 dagen groter is dan 01/01/1900.

Maar hoe komt Excel dan aan die decimalen voor de tijd?

We weten ondertussen dat 1 dag gelijk is aan 1. Eén dag bestaat uit 24 uren; dus 1 uur is gelijk aan 1/24 (=0,0416666…) Om ons voorbeeld even na te rekenen: om 13:30 is onze dag 13,5 uren bezig.

Dus: (1/24) * 13,5 is gelijk aan …? Juist! 0,5624.

Zo: nu weet je hoe Excel rekent met onze datums en tijden, en bovendien begrijp je nu ook waarom een datum vóór 01/01/1900 niet als datum maar als tekst herkend wordt door Excel. (Ja, echt waar. Probeer het maar eens 😉 )

Getalopmaak van datums en tijden

Zoals je hebt gemerkt in onze 2 voorbeelden hierboven; zal Excel datums en tijden automatisch herkennen en de getalopmaak hierop aanpassen. Hoe de notatie juist zal zijn; wordt mede bepaald door je Land- en taalinstellingen van je besturingssysteem.

In Windows 7 kan je deze instellingen terugvinden onze Configuratiescherm > Land en taal.

Excel neemt voor de datum standaard de ‘Korte datumnotatie’ en voor de tijd standaard de ‘Lange tijdnotatie’

In het lint heb je de mogelijkheid om te switchen tussen een lange en korte datumnotatie.

Via de celeigenschappen heb je onder het tabblad ‘getal’  zowel voor data als voor tijden enkele standaardkeuzes.

   

Uiteraard kan je zelf zoveel variaties maken hierop als je maar wil. Om dit te doen kies je niet voor ‘datum’ of ’tijd’, maar voor ‘aangepast’. Hier kan je volledig zelf kiezen hoe je datum en/of tijd wil weergeven op je werkblad. In de post over het opmaken van getallen hebben we dit in detail besproken; maar hier nog eens het overzicht:

Teken	Omschrijving
d	Dagen als 1-31
dd	Dagen als 01-31
ddd	Dagen als Ma-Zo
dddd	Dagen als Maandag-Zondag
m	Maandag als 1-12
mm	Maanden als 01-12
mmm	Maanden als Jan-Dec
mmmm	Maanden als Januari-December
mmmmm	Maanden als J-D (eerste letter van de maand)
yy	Jaren als 00-99
yyyy	Jaren als 1900-9999
h	Uren als 0-23
hh	Uren als 00-23
m	Minuten als 0-59 (moet in combinatie met h of s gebruikt worden, anders wordt de maand weergegeven)
mm	Minuten als 00-59 (moet in combinatie met h of s gebruikt worden, anders wordt de maand weergegeven)
s	Seconden als 0-59
ss	Seconden als 00-59
hh:mm AM/PM	Tijd als 09:56 AM
hh:mm A/P	Tijd als 09:56 A
[h]:mm	Verstreken tijd in uren
[mm]:ss	Verstreken tijd in minuten
[ss]	Verstreken tijd in seconden
hh:mm:ss.00	Fracties van een seconde

Het verschil tussen 2 datums berekenen

Aantal jaren EN maanden EN dagen tussen 2 datums

Open een nieuw werkblad en vul dit als volgt:

Plaats nu volgende formule in D1:

=JAAR(B1)-JAAR(A1)-ALS(OF(MAAND(B1)<MAAND(A1);EN(MAAND(B1)=MAAND(A1); DAG(B1)<DAG(A1)));1;0)&" jaar, "&MAAND(B1)-MAAND(A1)+ALS(EN(MAAND(B1) <=MAAND(A1);DAG(B1)<DAG(A1));11;ALS(EN(MAAND(B1)<MAAND(A1);DAG(B1) >=DAG(A1));12;ALS(EN(MAAND(B1)>MAAND(A1);DAG(B1)<DAG(A1));-1)))&" maanden en "&B1-DATUM(JAAR(B1);MAAND(B1)-ALS(DAG(B1)<DAG(A1);1;0);DAG(A1))&" dagen"

Als je dit goed gedaan hebt; zal het resultaat van de formule zijn:

0 jaar, 9 maanden en 7 dagen

Aantal jaren OF maanden OF dagen tussen 2 datums

De eenvoudigste manier hier is het gebruik van DATUMVERSCHIL(). Hiervoor verwijs ik je graag naar onze eerdere post waarin dit topic in detail besproken werd.

Een datum in de toekomst berekenen

Om een datum in de toekomst te berekenen, kan je best gebruik maken van ingebouwde functies. Je moet namelijk rekening houden met schrikkeljaren; en die ‘regeling’ is niet eenvoudig te implementeren. Hieronder twee voorbeelden hoe het kan:

Maak een nieuw werkblad als volgt:

In C2 zet je volgende formule:

=DATUM(JAAR(A2)+B2;MAAND(A2);DAG(A2))

In D2 zet je volgende formule:

=ZELFDE.DAG(A3;B3*12)

Beide formules geven hetzelfde resultaat:

Wil je weten op welke weekdag de toekomstige datum valt? Verander dan je getalopmaak naar ‘dddd’ of ‘dddd dd mmmm jjjj’

Het echte werk

Nu gaan we onze opgedane kennis eens in de praktijk opzetten. Hiervoor gebruiken we de 2 voorbeeldsituaties (je kan de file downloaden onderaan deze post)

Voorbeeld 1

Stel: je hebt een tuinbouwbedrijfje gestart; en je wil graag een overzicht maken van de kosten die je moet factureren. Je plaatst al je klanten in Excel:

Om te beginnen moet je gaan berekenen hoeveel uren er gewerkt werd. Gezien je de uitkomst opnieuw als tijd wil zien verschijnen; kunnen we gewoon de twee uren van elkaar aftrekken. Plaats in cel E2 volgende formule:

=C2-B2

Als je deze formule door voert; zie je dat voor elke lijn het juiste aantal uren berekend wordt.

Nu willen we gaan berekenen hoeveel de kosten bedragen voor deze gewerkte uren per klant. Deze zijn niet enkel afhankelijk van het aantal uren; maar ook van het afgesproken uurtarief. Nu moeten we beroep doen op onze pas vergaarde kennis.

Om tot het juiste resultaat te komen op rij 2, moeten we €40 vermenigvuldigen met 7. In  D2 staat 40, dat is OK. In E2 daarentegen staat een formule die de uren weergeeft. Het seriële getal hierachter is echter niet 7 maar 0,291666… (1/24 * 7).

We kunnen dus niet simpelweg het uurtarief vermenigvuldigen met het aantal gewerkte uren. De formule heeft een kleine aanpassing nodig. We moeten namelijk cel E2 eerst nog vermenigvuldigen met 24. Plaats volgende formule in cel F2:

=(E2*24)*D2

Wijzig de opmaak van F2 van ’tijd’ naar ‘valuta’, en voer de formule verder door. Het resultaat:

Rest ons nog het maken van de eindtotalen. De kosten samentellen in Kolom F is snel gebeurd. Zet in F8 volgende formule:

=SOM(F2:F7)

Bij het aantal gewerkte uren moeten we opnieuw 2x nadenken vooraleer te beginnen. Als we het totaal van de gewerkte uren willen; kunnen we niet gewoon de som van bereik E2:E7 maken. Opnieuw moeten we vermenigvuldigen met 24.

Plaats volgende formule in cel E8:

=SOM(E2:E7)*24

Wijzig de opmaak van F2 van ’tijd’ naar ‘getal’.

Voorbeeld 2

Stel: je hebt een transportbedrijf met 3 chauffeurs. Je chauffeurs worden per gereden uur betaald; en dus wil een overzicht van de rijtijden bijhouden om hun loon te berekenen.

Om te beginnen moeten we de gereden uren gaan berekenen. Het enige verschil met de situatie hierboven is dat we rekening moeten houden met de rustpauzes. Deze zijn namelijk onbetaald.

Zet in F2 volgende formule:

=(E2-B2)-(D2-C2)

Voer deze formule nu door naar beneden…

Oei! Hier is duidelijk iets mis!? We krijgen een fout in cel F4. Dit komt doordat het uur van vertrek groter is dan het uur van aankomst. Onze chauffeur heeft namelijk ’s nachts gereden.

Dit is gelukkig eenvoudig te omzeilen. Plaats in elke cel die een uur bevat niet enkel het uur, maar ook de datum. Een datum en een uur in dezelfde cel invoeren doe je bijvoorbeeld zo: ‘7/10/2014 6:00’.

Je zal zien dat dit de formule in cel F5 wél correct doet werken:

Indien je de datum niet wil zien in je overzicht; kan je die opnieuw verbergen door de getalopmaak te wijzigen naar ’tijd’.

Nu ziet ons blad er al goed uit. Rest ons enkel nog het toevoegen van de formules om het loon te berekenen. Ervan uitgaande dat het uurloon €18 is, zet je in cel G2 volgende formule:

=18*(F2*24)

Voer de formule door naar beneden en zet de opmaak naar ‘valuta’. Hier is dan ons eindresultaat:

Sorteren van datums en tijden

Data en tijd worden gesorteerd op het seriële getal. Dat is logisch en wenselijk. Echter het seriële getal is meestal niet hetzelfde als het getal dat wordt weergegeven. Daarom kan je soms onverwachte resultaten krijgen.

Als voorbeeld zetten we de eerste dag van elke maand onder elkaar. We wijzigen de getalopmaak naar ‘aangepast’ en kiezen voor ‘mmmm’

   

We krijgen nu in enkel de maanden zichtbaar. Je kan deze kolom nu wel oplopend sorteren, echter Excel zal kiezen om te sorteren van ‘oud naar nieuw’, en niet van ‘A naar Z’.

Ingebouwde datumfuncties

Zoals eerder vermeld: als er een ingebouwde functie bestaat voor wat je wil doen, gebruik deze dan! Daarom even een overzicht van alle datum- en tijdfuncties.

Functie	Omschrijving	Opmerking
DAG	Converteert een serieel getal naar een dag van de maand
DAGEN	Geeft als resultaat het aantal dagen tussen twee datums	sinds Office 2013
DAGEN360	Berekent het aantal dagen tussen twee datums op basis van een jaar met 360 dagen
DATUM	Geeft als resultaat het seriële getal van een opgegeven datum
DATUMVERSCHIL	Periode tussen twee datums, in dagen, maanden of jaren
DATUMWAARDE	Converteert een datum in de vorm van tekst naar een serieel getal
JAAR	Converteert een serieel getal naar een jaar
JAAR.DEEL	Geeft als resultaat het gedeelte van het jaar, uitgedrukt in het aantal hele dagen tussen begindatum en einddatum
LAATSTE.DAG	Geeft als resultaat het seriële getal van de laatste dag van de maand voor of na het opgegeven aantal maanden
MAAND	Converteert een serieel getal naar een maand
MINUUT	Converteert een serieel getal naar een minuut
NETTO.WERKDAGEN	Geeft als resultaat het aantal hele werkdagen tussen twee datums
NETWERKDAGEN.INTL	Geeft het aantal volledige werkdagen tussen twee datums met aangepaste weekendparameters
NU	Geeft als resultaat het seriële getal voor de huidige datum en tijd
SECONDE	Converteert een serieel getal naar een seconde
TIJD	Geeft als resultaat het seriële getal van een opgegeven tijd
TIJDWAARDE	Converteert een tijd in de vorm van tekst naar een serieel getal
UUR	Converteert een serieel getal naar een uur
VANDAAG	Geeft als resultaat het seriële getal van de huidige datum
WEEKDAG	Converteert een serieel getal naar een weekdag
WEEKNUMMER	Converteert een serieel getal naar een weeknummer
ISO.WEEKNUMMER	Geeft als resultaat het ISO-weeknummer van het jaar voor een bepaalde datum	sinds Office 2013
WERKDAG	Geeft als resultaat het seriële getal van de datum voor of na een bepaald aantal werkdagen
WERKDAG.INTL	Geeft het seriële getal van de datum voor of na een opgegeven aantal werkdagen met aangepaste weekendparameters
ZELFDE.DAG	Geeft als resultaat het seriële getal van een datum die het opgegeven aantal maanden voor of na de begindatum ligt