Google Spreadsheets kan worden gebruikt voor meer dan alleen gegevensverzameling en -organisatie. Je kunt het ook gebruiken om de huidige tijd te bepalen, grafieken te maken en de leeftijd te berekenen met behulp van een geboortedatum. Dit laatste wordt ontdekt door het gebruik van formules en functies die rechtstreeks in Google Spreadsheets zijn ingebouwd.
Zie ook ons artikel Rijen en kolommen toevoegen en verwijderen in Google Spreadsheets
Leeftijd bepalen vanaf geboortedatum in Google Spreadsheets
Wanneer u Google Spreadsheets gebruikt, hebt u twee overheersende manieren om de leeftijd te bepalen op basis van een geboortedatum. Er is DATEDIF, de flexibelere optie, en YEARFRAC, de eenvoudigere keuze. Aan het einde van het artikel moet je niet alleen de leeftijd van een persoon kunnen bepalen, maar ook die van meerdere groepen van verschillende personen tegelijk.
Ik zal beginnen met de DATEDIF-functie.
De DATEDIF-functie
Voordat we in de functie zelf kunnen duiken, moeten we weten hoe het werkt. Dit vereist het leren van de syntaxis voor gebruik met de DATEDIF-functie.
Syntaxis
= DATUMVERSCHIL (begindatum, einddatum, eenheid)
-
- begin datum
- De berekening zal moeten beginnen met de geboortedatum.
- einddatum
- Dit is de datum om de berekening af te ronden. Bij het bepalen van de huidige leeftijd is dit aantal waarschijnlijk de datum van vandaag.
- begin datum
- eenheid
-
- De outputkeuzes die bestaan uit: "Y", "M", "D", "YM", "YD" of "MD".
- Y - Totaal aantal volledige, verstreken jaren tussen zowel ingevoerde start- als einddatums.
- YM - De 'M' staat voor maanden. Deze output toont het aantal maanden volgend op de volledig verstreken jaren voor 'Y'. Het aantal zal niet hoger zijn dan 11.
- YD - De 'D' staat voor dagen. Deze output toont het aantal dagen na de volledig verstreken jaren voor 'Y'. Het aantal zal 364 niet overschrijden.
- M - Totaal aantal volledig verstreken maanden tussen zowel ingevoerde start- als einddatums.
- MD - Net als bij de andere eenheden staat 'D' voor dagen. Deze output toont het aantal dagen na de volledig verstreken maanden voor 'M'. Kan 30 niet overschrijden.
- D - Totaal aantal volledig verstreken dagen tussen zowel ingevoerde start- als einddatums.
De berekening
Nu u de syntaxis begrijpt die wordt gebruikt, kunnen we de formule instellen. Zoals eerder vermeld, is de DATEDIF-functie de meest flexibele optie bij het bepalen van de leeftijd vanaf een geboortedatum. De reden hiervoor is dat u alle details van de leeftijd kunt berekenen in een jaar-, maand- en dagnotatie.
Om te beginnen hebben we een voorbeelddatum nodig om in de cel te gebruiken. Ik heb besloten om de datum 7/14/1972 in cel A1 te plaatsen . We doen de formule in de cel rechts ervan, B1, als je wilt volgen om het onder de knie te krijgen.
We beginnen met de meest eenvoudige versie van de formule om de leeftijd te berekenen. Als u de bovenstaande syntaxis gebruikt om erachter te komen wat wat is, is A1 technisch de startdatum, vandaag is de einddatum en bepalen we de leeftijd in jaren met behulp van "Y" . Daarom zal de eerste formule die wordt gebruikt er zo uitzien:
= DATUMVERSCHIL (A1, vandaag ()”Y”)
Wanneer correct gedaan, zal het getal dat de berekende leeftijd aangeeft, in B1 worden geplaatst als ' 46 '.
Laten we dezelfde formule doen, alleen deze keer bepalen we de leeftijd in maanden met behulp van "M" in plaats van "Y".
= DATUMVERSCHIL (A1, nu (), ”M”)
Het totaal zou 559 maanden zijn. Dat is 559 maanden oud.
Dit nummer is echter een beetje absurd en ik denk dat we het een stuk lager kunnen maken door "YM" te gebruiken in plaats van alleen "M".
= DATUMVERSCHIL (A1, vandaag (), ”JM”)
Het nieuwe resultaat zou 7 moeten zijn, wat een veel beter beheersbaar aantal is.
Laten we eens kijken hoe de dagen eruit zullen zien met zowel "YD" als "MD".
= DATUMVERSCHIL (A1, vandaag (), ”YD”)
= DATUMVERSCHIL (A1, vandaag (), ”MD”)
Deze keer worden de resultaten voor "YD" getoond in B1 en het resultaat voor "MD" bevindt zich in cel B2.
Heb je het tot nu toe onder de knie?
Vervolgens zullen we deze allemaal samenvoegen in een poging om onszelf een meer gedetailleerde berekening te bieden. De formule kan een beetje hectisch zijn om uit te typen, dus kopieer en plak de meegeleverde in cel B1.
De te gebruiken formule is:
= gedateerd (A1, vandaag (), "Y") & "Jaren" & gedateerd (A1, vandaag (), "YM") & "maanden &" & gedateerd (A1, vandaag (), "MD") & "dagen ”
Het en-teken wordt gebruikt om elke formule samen te voegen als een kettingschakel. Dit is nodig om de volledige berekening te krijgen. Uw Google-spreadsheet moet dezelfde formule bevatten als:
Een volledige, gedetailleerde berekening heeft ons 46 jaar, 7 maanden en 26 dagen opgeleverd. U kunt ook dezelfde formule gebruiken met de functie ArrayFormula. Dit betekent dat u meer dan slechts één datum kunt berekenen, maar ook meerdere datums.
Ik heb willekeurig een paar datums gekozen en deze in extra cellen A2-A5 gestopt. Kies je eigen data en heb er wat plezier mee. Als u de functie ArrayFormula wilt gebruiken, kopieert en plakt u het volgende in cel B1:
= Matrixformule (gedateerd (B2, C2 (), "Y") & "Jaren" & gedateerd (B2, C2 (), "YM") & "maanden &" & gedateerd (B2, C2 (), "MD") & "Dagen")
Dit zijn mijn resultaten:
Laten we nu zeggen dat u elk deel van de datum in zijn eigen nette kleine kolom wilde scheiden om het belang te organiseren. Voeg in Google Spreadsheets uw begindatum (de geboortedatum) in een kolom en de einddatum in een andere kolom toe. Ik heb cel B2 gekozen voor de begindatum en C2 voor de einddatum in mijn voorbeeld. Mijn data correleren met de geboorten en recente sterfgevallen van beroemdheden Burt Reynolds, Johnny Cash en Luke Perry.
Zoals weergegeven, is kolom A de naam van het individu, bevat kolom B de begindatum en C de einddatum. Nu voeg ik nog vier kolommen toe aan de rechterkant. Een voor elk van "Y", "YM", "YD" en een combinatie van alle drie. Nu moet je gewoon de juiste formules toevoegen aan elke rij voor elke beroemdheid.
Burt Reynolds:
= DATEDIF (B2, C2, "Y") Wijzig 'Y' in de overeenkomstige kolom die u probeert te berekenen.
Johnny Cash:
= DATEDIF (B3, C3, "Y") Wijzig 'Y' in de overeenkomstige kolom die u probeert te berekenen.
Luke Perry:
= DATEDIF (B4, C4, "Y") Wijzig 'Y' in de overeenkomstige kolom die u probeert te berekenen.
Om de JOINED-formule te krijgen, moet u een ArrayFormula gebruiken, net zoals we eerder in het artikel deden. U kunt woorden als Jaren toevoegen om de resultaten van het jaar aan te geven door deze achter de formule en tussen haakjes te plaatsen.
= Matrixformule (gedateerd (B2, C2, "Y") & "Jaren" & gedateerd (B2, C2, "YM") & "maanden &" & gedateerd (B2, C2, "MD") & "dagen")
De bovenstaande formule is per beroemdheid. Als je ze echter allemaal in één klap gewoon wilt uitschakelen, kopieer en plak je de volgende formule in cel G2:
= Matrixformule (gedateerd (B2: B4, C2: C4, "Y") & "Jaren" & gedateerd (B2: B4, C2: C4, "YM") & "maanden &" & gedateerd (B2: B4, C2: C4, "MD") & "dagen")
Uw Google-spreadsheet zou er ongeveer zo uit moeten zien:
Best netjes, he? Zo eenvoudig is het bij het gebruik van de DATEDIF-functie. Nu kunnen we verder gaan met de functie YEARFRAC.
De YEARFRAC-functie
De functie YEARFRAC is eenvoudig voor eenvoudige resultaten. Het is meteen een punt dat een eindresultaat oplevert zonder alle extra toegevoegde output voor jaren, maanden en dagen.
Hier is een basisformule die op slechts één cel van toepassing is:
= int (YEARFRAC (A1, nu ()))
Je voegt de geboortedatum toe aan cel A1 en plakt de formule in B1 voor het resultaat. We gebruiken de geboortedatum 11/04/1983 :
Het resultaat is 35 jaar oud. Eenvoudig, net als bij het gebruik van de DATEDIF-functie voor een enkele cel. Vanaf hier kunnen we doorgaan met het gebruik van YEARFRAC binnen een ArrayFormula. Deze formule is nuttiger voor u wanneer u de leeftijd van grote groepen zoals studenten, docenten, teamleden, enz. Moet berekenen.
We moeten een kolom met verschillende geboortedata toevoegen. Ik heb kolom B geselecteerd omdat A zal worden gebruikt voor de namen van de personen. Kolom C wordt gebruikt voor de eindresultaten.
Om de leeftijd in de aangrenzende kolom te vullen, moeten we de volgende formule gebruiken:
= ArrayFormula (int (YEARFRAC (B2: B8, vandaag (), 1)))
Plaats de bovenstaande formule in cel C2 om de resultaten te krijgen.
Als u liever gewoon doorgaat met een hele kolom en liever niet uitzoekt waar het eindigt, kunt u een kleine variatie aan de formule toevoegen. Volg IF en LEN in de richting van het begin van de ArrayFormula als volgt:
= ArrayFormula (if (len (B2: B), (int (YEARFRAC (B2: B, vandaag (), 1)))))
Hiermee worden alle resultaten in die kolom berekend vanaf B2.
