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.

20141008_1

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.

20141008_2

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

20141008_3

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.

20141008_4

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.

20141008_6    20141008_7

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:

TekenOmschrijving
dDagen als 1-31
ddDagen als 01-31
dddDagen als Ma-Zo
ddddDagen als Maandag-Zondag
mMaandag als 1-12
mmMaanden als 01-12
mmmMaanden als Jan-Dec
mmmmMaanden als Januari-December
mmmmmMaanden als J-D (eerste letter van de maand)
yyJaren als 00-99
yyyyJaren als 1900-9999
hUren als 0-23
hhUren als 00-23
mMinuten als 0-59 (moet in combinatie met h of s gebruikt worden, anders wordt de maand weergegeven)
mmMinuten als 00-59 (moet in combinatie met h of s gebruikt worden, anders wordt de maand weergegeven)
sSeconden als 0-59
ssSeconden als 00-59
hh:mm AM/PMTijd als 09:56 AM
hh:mm A/PTijd als 09:56 A
[h]:mmVerstreken tijd in uren
[mm]:ssVerstreken tijd in minuten
[ss]Verstreken tijd in seconden
hh:mm:ss.00Fracties 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:

20141008_12

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:

20141008_22

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:

20141008_23

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

20141008_24

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:

20141008_13

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.

20141008_14

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:

20141008_15

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’.

20141008_16

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.

20141008_17

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…

20141008_18

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:

20141008_19

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

20141008_20

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:

20141008_21

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’

20141008_8  20141008_9  20141008_10

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’.

20141008_11

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.

FunctieOmschrijvingOpmerking
DAGConverteert een serieel getal naar een dag van de maand
DAGENGeeft als resultaat het aantal dagen tussen twee datumssinds Office 2013
DAGEN360Berekent het aantal dagen tussen twee datums op basis van een jaar met 360 dagen
DATUMGeeft als resultaat het seriële getal van een opgegeven datum
DATUMVERSCHILPeriode tussen twee datums, in dagen, maanden of jaren
DATUMWAARDEConverteert een datum in de vorm van tekst naar een serieel getal
JAARConverteert een serieel getal naar een jaar
JAAR.DEELGeeft als resultaat het gedeelte van het jaar, uitgedrukt in het aantal hele dagen tussen begindatum en einddatum
LAATSTE.DAGGeeft als resultaat het seriële getal van de laatste dag van de maand voor of na het opgegeven aantal maanden
MAANDConverteert een serieel getal naar een maand
MINUUTConverteert een serieel getal naar een minuut
NETTO.WERKDAGENGeeft als resultaat het aantal hele werkdagen tussen twee datums
NETWERKDAGEN.INTLGeeft het aantal volledige werkdagen tussen twee datums met aangepaste weekendparameters
NUGeeft als resultaat het seriële getal voor de huidige datum en tijd
SECONDEConverteert een serieel getal naar een seconde
TIJDGeeft als resultaat het seriële getal van een opgegeven tijd
TIJDWAARDEConverteert een tijd in de vorm van tekst naar een serieel getal
UURConverteert een serieel getal naar een uur
VANDAAGGeeft als resultaat het seriële getal van de huidige datum
WEEKDAGConverteert een serieel getal naar een weekdag
WEEKNUMMERConverteert een serieel getal naar een weeknummer
ISO.WEEKNUMMERGeeft als resultaat het ISO-weeknummer van het jaar voor een bepaalde datumsinds Office 2013
WERKDAGGeeft als resultaat het seriële getal van de datum voor of na een bepaald aantal werkdagen
WERKDAG.INTLGeeft het seriële getal van de datum voor of na een opgegeven aantal werkdagen met aangepaste weekendparameters
ZELFDE.DAGGeeft als resultaat het seriële getal van een datum die het opgegeven aantal maanden voor of na de begindatum ligt