Voor de week waar u zich momenteel in bevindt is online snel achterhaald om welke datums dat gaat, maar voor een uitgebreide tabel met enkel weeknummers is het handig om te weten welke datum daarachter schuilt. Cm: legt uit hoe u datums door Excel laat invullen.
Er is een functie in Excel om van een datum een weeknummer te maken, namelijk WEEKNUM. Helaas is er geen functie om het omgekeerde te doen en het weeknummer om te toveren in datums. Hiervoor kunt u zelf een formule in elkaar zetten. Wilt u meteen aan de slag: de formule om de maandag van het huidige weeknummer te vinden is:
Of Engels:
=DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+ISOWEEKNUM(TODAY()) *7
Alternatief verwijst u uiteraard naar een cel in plaats van ISO.WEEKNUMMER(), bijvoorbeeld B2 met daarin het weeknummer. Logischerwijs wordt de formule dan =DATUM(JAAR(VANDAAG());1;-2)-WEEKDAG(DATUM(JAAR(VANDAAG());1;3))+B2*7
Opbouw van de formule
Om de weekstartdatum te berekenen gebruikt u de functies DATUM() minus WEEKDAG(), DATE() minus WEEKDAY(). Wat we dienen te weten is de eerste maandag van het jaar. Waar dit enigszins verwarrend wordt, is het eerste weeknummer. De eerste week is volgens de internationale standaard de week die begint met de eerste donderdag van het jaar. Voor dit doeleinde zijn we op zoek naar de eerste maandag van het jaar en dat zal door deze scheidslijn op donderdag altijd de eerste maandag vóór 5 januari zijn.
>> Datums in de headers gebruiken in een Excel-tabel? Tony De Jonker legt uit hoe dat moet.
Ten eerste gebruikt u het element DATUM. Deze heeft drie parameters: jaar, maand en dag. Voor het jaar gebruikt u de functie VANDAAG() die het jaar van de huidige datum hanteert. Voor de parameters erna hebben we januari nodig, dus dat wordt 1. Vervolgens bent u op zoek naar de datum van 7 dagen vóór 5 januari, wat de eerste dag van de eerste week zal zijn. 5 januari – 7 dagen = -2. Dit stukje van de formule wordt daarom =DATUM(JAAR(VANDAAG());1;-2).
Deze trekt u af van de hierboven beschreven DATUM-functie om de eerste maandag vóór het wisselen van de weken te vinden. De formule wordt dan =DATUM(JAAR(VANDAAG()), 1, -2)- WEEKDAG(DATUM(JAAR(VANDAAG());1;3)). Dat levert de eerste maandag van de laatste week van het voorgaande jaar op. Dat is het getal waar Excel verder mee rekent.
Het laatste stukje van de formule is het getal achter de laatste maandag van het voorafgaande jaar, plus het weeknummer maal zeven dagen. In dit voorbeeld houden we het op het weeknummer van vandaag, 18 juni 2021. Dat achterhaalt u met ISO.WEEKNUMMER() met daarin wederom VANDAAG. Dus ISO.WEEKNUMMER(VANDAAG()). Maar hier kunt u natuurlijk ook verwijzen naar de cel in uw tabel met daarin het weeknummer. Dat levert week 24 op.
Dan wordt de uiteindelijke formule om de eerste dag van de week te bepalen: =DATUM(JAAR(VANDAAG());1;-2)-WEEKDAG(DATUM(JAAR(VANDAAG());1;3))+ISO.WEEKNUMMER(VANDAAG())*7.
>> Lees ook: Excel-tutorial: fouten verbergen of verwijderen
Weektabel vullen met datum
Stel dat u een tabel met weeknummers wilt aanvullen met de begin en einddatum. Als u deze formule toepast op een dergelijke tabel, en het stuk ISO.WEEKNUMMER(VANDAAG()) vervangt door een cel met een weeknummer, is de output het seriële getal waar Excel mee rekent. Selecteer deze kolom, kies met de rechtermuisknop voor Celeigenschappen en kies een datumnotatie:
Als u ook de opvolgende vrijdag wilt toevoegen, pakt u simpelweg dezelfde formule maar telt u daar vijf bij op. Dat wordt dan =DATUM(JAAR(VANDAAG());1;-2)-WEEKDAG(DATUM(JAAR(VANDAAG());1;3))+ISO.WEEKNUMMER(VANDAAG())*7+4. Dat levert opnieuw seriële getallen op (het getal van de maandag, plus vier, levert het getal van vrijdag op), zet deze velden op dezelfde manier om in datumvelden en maak er eventueel een tabel van:
