Datums in Excel
In Excel kun je met datums werken. Dit betekent dat je er mee kunt rekenen en dat is hartstikke handig. Zo kun je zien welke dag het vandaag is en op welke dag van de week Sinterklaas valt en hoeveel dagen het nog duurt voordat het zover is. Laten we maar eens de voorbeeldjes bekijken:
In cel E1 staat de formule met de functie VANDAAG(). Deze functie geeft de huidige (systeem) datum.
In cel E2 heb ik gewoon de datum 5-12-2017 getikt en Excel ziet dit onmiddellijk als een datum.
In cel E3 heb ik de formule: =E2-E1. Hier worden dus twee datums van elkaar afgetrokken. Het resultaat is een waarde die het aantal dagen tussen de twee datums voorstelt.
Excel kent intern een getal toe aan iedere datum. Dat gebeurt, uiteraard, opvolgend. Daardoor is het mogelijk om met datums te rekenen.
In cel F2 staat de formule met de functie =WEEKDAG(E2). Deze functie geeft een getal van 1 tot 7 die de dag van de week representeert waarbij zondag=1 en zaterdag=6.
Dit wetende kun je een tabelletje maken en met de functie VERT.ZOEKEN de dag van de week in woorden erbij vinden, zoals in G2 gedaan is.
Nog wat voorbeeldjes om te zien welke handige datum-functies Excel nog meer kent:
In cel E1 staat weer de datum van vandaag met de functie VANDAAG().
In cel E2 haal ik de dag uit de datum met de functie DAG(E1).
In cel E3 haal ik de maand uit de datum met de functie MAAND(E1).
In cel E4 haal ik het jaar uit de datum met de functie JAAR(E1).
In de cellen F2:F4 tel ik steeds 1 op bij de resultaten uit E2:E4.
In cel F5 maak ik daar weer een datum van met de functie DATUM(F4;F3;F2); dus als argumenten eerst het jaar, dan de maand en te slotte de dag.
In cel E5 bepaal ik welk deel van het jaar het vandaag is met de functie JAAR.DEEL(“1-1-2017”;E1;1). Die laatste 1 is nodig om aan te geven dat Excel moet rekenen met het echte aantal dagen in het jaar. Voor 2017 is dat 365. In cel H1 staat het getal dat hoort bij VANDAAG en in cel H2 staat het getal dat hoort bij 1-1-2017. In cel H3 staat het verschil tussen de twee datums in dagen. In cel H4 staat het aantal dagen in 2107. In cel H5 staat de formule: =H3/H4, wat precies dezelfde waarde geeft als in cel E5. Zo ziet u hoe Excel intern rekent.
Ten slotte staat in cel E6 welke datum het is over 7 werkdagen. Hiervoor gebruik je de functie WERKDAG(E1;7). Deze functie houdt rekening met de weekenden en telt deze dagen niet mee. Als derde argument kan ook nog een reeks met andere datums worden opgegeven, zoals vakantie en feestdagen, die er dan ook uitgefilterd worden.
Hoeveel dagen zitten er in een jaar?
In onze kalender hangt dat af of het jaar een schrikkeljaar is of niet. Een schrikkeljaar heeft 366 dagen en de andere jaren hebben 365 dagen. We moeten dus weten wanneer een jaar schrikkel is. Normaal is dat elke 4 jaar, wanneer het jaartal deelbaar is door 4. Dus 2016 is schrikkel, want 2016 is deelbaar door 4. Maar 2017 is niet schrikkel, want 2017 is niet deelbaar door 4; er blijft een rest 1 over.
Maar er zijn wat uitzonderingen. Eeuwwisselingen zijn niet schrikkel. Dus bijvoorbeeld 1800 en 1900 waren niet schrikkel. Maar ook daar zijn weer uitzonderingen op, want één keer in de 400 jaar is de eeuwwisseling wel weer schrikkel. Het jaar 2000 was dus wel degelijk schrikkel, maar wel een bijzondere…
Al deze regeltjes bij elkaar levert dan de volgende formule op om het aantal dagen bij een gegeven jaartal te berekenen:
=365+ALS(EN(REST(F1;4)=0;OF(REST(F1;100)<>0;REST(F1;400)=0));1;0) , waarbij dus in cel F1 het jaartal staat.
Conlcusie
Omdat Excel zo slim is om een datum om te zetten in een getal kan er mee worden gerekend en zijn er allerlei handige functies die u ter beschikking staan.
Problemen
Ik verkeer in de gelukkige omstandigheid dat mijn beide ouders nog leven, hoogbejaard weliswaar, maar toch… Ik vroeg mij af wanneer mijn moeder ouder zou zijn dan mijn oma is geworden. Daartoe maakte ik het volgende sheet:
In cel E1 staat de geboortedatum van mijn oma. In cel E2 staat de overlijdensdatum van mijn oma en in cel F1 staat de geboortedatum van mijn moeder.
In cel F2 wil ik het verschil in dagen weten tussen de geboortedatum en de overlijdensdatum van mijn oma zodat ik dit aantal kan optellen bij de geboortedatum van mijn moeder, zodat ik weet wanneer mijn moeder ouder is dan mijn oma is geworden.
Ik werd echter verrast door de foutmelding. Uit onderzoek blijkt dat Excel alleen maar met datums kan werken vanaf de 20e-eeuw.
En het wordt nog erger: Volgens Excel bestaat 29-2-1900, terwijl we eerder gezien hebben dat 1900 géén schrikkeljaar is. Microsoft zegt zelf dat ze dit gedaan hebben vanwege compatibiliteits- redenen ten opzichte van Lotus 1-2-3 (zie ook het artikel van Microsoft hierover). Dit heeft gevolgen voor de functie WEEKDAG die vanaf 1-1-1900 tot 29-2-1900 een verkeerde uitkomst geeft.
Ook voor genealogen, waartoe ik mezelf inmiddels reken, is dit vervelend, omdat deze groep mensen al snel te maken heeft met datums van voor 1900.
HJGSoft heeft echter een oplossing voor dit probleem bedacht en geïmplementeerd.
In de add-in Handigheidjes (versie 2017_v1) zijn een 12-tal functies opgenomen om met “alle” datums te kunnen werken.
Het systeem werkt als volgt:
Afhankelijk van de ingevoerde datum bekijkt Handigheidjes of de datum Gregoriaans of Juliaans is.
Alle datums vanaf 15-10-1582 zijn Gregoriaans. Alle datum tot 4-10-1582 zijn Juliaans. Paus Gregorius heeft “zijn” kalender laten ingaan op 15-10-1582, één dag na 4-10-1582. Er zit dus een gat van 10 dagen in onze kalender. Dit moest gebeuren omdat de Juliaanse kalender verkeerd omging met schrikkeljaren. De Juliaanse kalender gaat er namelijk vanuit dat een zonnejaar 365,25 dagen duurt, waardoor er iedere 4 jaar een schrikkeljaar moet zijn om weer één dag in te lopen. Een zonnejaar duurt echter 365,2425 jaar wat een extra correctie moet opleveren. Vandaar de ietwat ingewikkelde berekening voor een schrikkeljaar zoals we eerder in dit artikel hebben gezien.
Handigheidjes zet een datum ook om in een getal en wel als volgt: 15-10-1582 krijgt 0 en van daaraf wordt bij iedere dag 1 opgeteld. Dus alle datums in de Gregoriaanse kalender hebben een positief geheel getal. Bij alle datum teruggerekend vanaf 4-10-1582 wordt steeds 1 afgetrokken. Dus 4-10-1582=-1, 3-10-1582=-2 etc. Daarmee krijgen alle Juliaanse datums een negatief getal.
Verder bestaat in onze jaartelling het jaar 0 niet. Dus de dag voor 1-1-1 is 31-12- -1 ofwel 31-12-1 v.C. (voor Christus).
Met dit systeem kan nu ook weer gerekend worden. Dit geschiedt door speciaal daarvoor ontwikkelde functies. Een datum in dit systeem moet worden ingevoerd als tekst in het formaat dd-mm-yyyy.
Even een aantal belangrijke functies in dit systeem:
HJG_UDatum2Nummer zet een datum om naar een getal
HJG_Nummer2UDatum zet een getal om naar een datum
HJG_UDagen berekent het verschil in dagen tussen twee datums
HJG_UDateAdd telt een aantal jaren, kwartalen, maanden, weken of dagen op bij een datum
HJG_UWeekdag bepaalt de dag van de week, waarbij zondag=0 en zaterdag=6
HJG_UDatumFormaat zet een datum neer in een bepaald formaat
En met al deze tools kunnen we mijn probleem nu wel oplossen:
In de cellen E1, E2 en F1 staan respectievelijk de geboortedatum van mijn oma, de sterfdatum van mijn oma en de geboortedatum van mijn moeder.
In cel F2 staat de formule: =HJG_UDagen(E1;E2)+1. HJG_UDagen bepaalt het aantal dagen dat mijn oma geleefd heeft.
In cel F3 staat de formule: =HJG_UDatumFormaat(HJG_UDateAdd(“d”;F2;F1);”Dddd d mmmm yyyy”).
De functie HJG_UDateAdd(“d”;F2;F1) telt het aantal (F2) dagen (“d”) op bij de datum (F1).
Vervolgens wordt deze datum door de functie HJG_UDatumFormaat omgezet naar het formaat Dddd d mmmm yyyy, waarbij Dddd de dag van de week voluit schrijft, te beginnen met een hoofdletter, gevolgd door de dag zonder voorloopnul, gevolgd door de maand voluit geschreven en ten slotte gevolgd door een 4-cijferig jaartal.
Mijn moeder zal dus op Dinsdag 6 juni 2017 ouder zijn dan mijn oma is geworden. En nu maar hopen dat ze dat haalt…
Update: Mijn moeder heeft het gehaald!