Ik kreeg onlangs de vraag hoe met behulp van voorwaardelijke opmaak bepaalde machines in een Excel-overzicht ‘gekleurd’ konden worden.
De achterliggende vraag was hoe aan de hand van een in-productie-name-datum de ‘ouderdom’ bepaald kon worden.
Algemener geformuleerd: hoe kunnen we de leeftijd van iets of iemand bepalen?
In dit artikel een simpele rechttoe-rechtaan-berekening, maar ook een methode waarbij gebruik gemaakt wordt van de ‘geheime’ Excel-formule Datumverschil.
Standaard leeftijd-berekening
Mijn eerste antwoord op de vraag was om het jaar van de startdatum af te trekken van het huidige jaar (zie cel C5 in het tabblad Standaard van het Voorbeeldbestand).
Om het systeem flexibeler te maken gebruiken we geen harde waarde voor het huidige jaar, maar nemen we het jaar van Vandaag() (cel C3; denk aan de ‘loze’ haakjes op het einde).
Direct kwam als reactie: Dank, maar ….
Het bekende leeftijd-probleem: het laatste jaar mag niet meetellen als de huidige dag-maand-combinatie vóór die van de startdatum ligt.
Snel opgelost (zie cel C6 in het voorbeeld):
=JAAR(C3)-JAAR(C2)-ALS(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”);1;0)
De berekening van cel C5 corrigeren we door te kijken of de maand-dag-combinatie van de einddatum daar aanleiding toe geeft.
Zo ja, dan trekken we 1 af van het resultaat en anders 0.
NB1 het isoleren van de dag en maand doen we in deze formule door van de datum teksten te maken, waarbij we alleen de maand en dag meenemen (zie het artikel Data (datums)).
NB2 let op de volgorde van dag en maand in de Tekst-functie.
NB3 de formule kan nog wat korter (zie cel C7):
=JAAR(C3)-JAAR(C2)-(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”))
We maken hierbij gebruik van het feit, dat, wanneer een vergelijking (in dit geval: kleiner dan) WAAR is, Excel intern hier de waarde 1 aan toekent en anders de waarde 0.
NB4 de werking van een formule kun je altijd bekijken met behulp van de optie Formule evalueren; te vinden in de menutab Formules in het blok Formules controleren.
Leeftijd
Maar nu we toch bezig zijn: de leeftijd kun je natuurlijk ook in dagen, weken of maanden uitdrukken. Hoe bereken je die?
- de leeftijd in dagen is in Excel heel gemakkelijk: =C3-C2
Hierbij maken we gebruik van de eigenschap dat in Excel een datum intern wordt vertaald naar het aantal dagen dat verstreken is sinds 1 jan 1900. - het aantal weken is het voorgaande resultaat gedeeld door 7.
Alleen geïnteresseerd in hele weken? In cel D11 van het Voorbeeldbestand, tabblad Standaard, staat de formule =GEHEEL(C10/7). - voor het aantal maanden hanteren we een benadering: de leeftijd in dagen gedeeld door het gemiddeld aantal dagen per maand.
In cel C12 staat de formule: =C10/DagenMnd.
NB1 deze benadering werkt goed als er flink wat jaren tussen begin- en einddatum zitten.
NB2 een exacte berekening staat in cel F12: =12*(JAAR(C3)-JAAR(C2)-1)+(12-MAAND(C2))+(MAAND(C3)-1)
NB3 DagenMnd is een verwijzing naar cel C3 in het tabblad BasisGeg van het Voorbeeldbestand . - het aantal jaren is in cel C13 benaderd door het aantal leeftijd-dagen te delen door een gemiddeld aantal dagen per jaar (365,25 in verband met de schrikkeljaren).
Hiervoor hebben we gezien hoe we de leeftijd in jaren kunnen bepalen.
Wil je ook nog weten hoeveel maanden of dagen er ondertussen in het laatste (onvolledige) jaar respectievelijk maand zijn verstreken dan hebben we nog 2 extra berekeningen nodig:
- Restmaanden: =D12-D13*12
ofwel de leeftijd in maanden minus de leeftijd in jaren maal 12 - Restdagen: =C10-D12*DagenMnd
ofwel de leeftijd in dagen minus de leeftijd in maanden maal het gemiddeld aantal dagen per maand
LET OP de laatste berekening levert een getal op met decimalen. Door de benadering kan het resultaat afwijken van de exacte waarde.
Niet iedereen kan het resultaat van zo’n berekening in de vorm van een tabel goed lezen.
Daarom kan het soms handig zijn om de resultaten op te nemen in een tekst.
Een voorbeeld is te vinden in cel B18:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&D16&” dagen”
Teksten en cel-verwijzingen worden aan elkaar gekoppeld met het &-teken.
LET OP om teksten en de waardes uit de cellen van elkaar te scheiden zijn aan de teksten op diverse plaatsen spaties toegevoegd.
Wil je geen decimalen bij de dagen weergeven:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&TEKST(D16;”0″)&” dagen”
LET OP met de Tekst-functie wordt het resultaat afgerond niet ‘afgehakt’.
Een iets andere vorm van deze formule:
=”Leeftijd is ” & D13 & ” jaar, ” & D15 & ” maanden en ” &
TEKST(D16;”0 “”dagen”””)
De tekst dagen is in de opmaak van cel D16 opgenomen.
LET OP Tekst binnen de opmaak moet tussen “-tekens staan; omdat dit binnen een andere set aanhalingstekens komt moeten er zelfs dubbele “-tekens gebruikt worden.
Functie Datumverschil
Waar we in het voorgaande alle berekeningen uitvoerden met eigen formules blijkt Excel ‘onder de motorkap’ een functie te hebben waarmee de resultaten sneller kunnen worden geproduceerd
(en exacter omdat er geen benaderingen nodig zijn voor de dagen per jaar en per maand).
Excel bevat namelijk de functie Datumverschil. Deze kent drie parameters/argumenten: een begindatum, een einddatum en een aanduiding welk resultaat berekend moet worden (het aantal dagen, maanden of jaren).
De functie wordt door Microsoft niet getoond in het overzicht bij Functie invoegen. Dus de functie moet handmatig ingevoerd worden: =datumverschil(arg1;arg2;arg3).
Ook zul je in het scherm Functieargumenten geen toelichting op de verschillende argumenten vinden.
NB Microsoft houdt wat slagen om de arm:
Als we nu het aantal dagen tussen een begin- en einddatum willen weten kunnen we de volgende formule gebruiken: =datumverschil(C2;C3;”d”), waarbij we er vanuit gaan dat cel C2 de begindatum bevat en C3 de einddatum.
Willen we het aantal maanden weten, dan vervangen we “d” door “m”. Om het aantal jaren te weten te komen, moet de derde parameter “y” zijn.
LET OP gebruik de y en niet de j. Hoofdletters zijn wel toegestaan. Het is niet duidelijk of dit vertaal-probleem in iedere versie van Excel optreedt. Experimenteren dus.
NB in het Voorbeeldbestand in het tabblad DatumVerschil staat overal als derde argument een celverwijzing. Dan worden de “-tekens weggelaten.
Maar … de functie kent nog meer opties voor de derde parameter: gebruik je yd, dan worden de resterende dagen berekend (dus het aantal dagen na de vorige “verjaardag”); gebruik je ym, dan is het resultaat het aantal resterende maanden en bij het invoeren van md wordt het aantal resterende dagen bepaald, die niet in een volledige maand zitten.
Dan nog een waarschuwing van Microsoft:
LET OP de oplossing die hier aangedragen wordt levert niet hetzelfde op als bedoeld met MD. Hier worden slechts de dagen geturfd in de lopende maand op het einde; de ‘losse’ dagen aan het begin worden niet meegenomen.