Excel: weken berekenen - zo werkt het met een formule

Vind de juiste functie

In Microsoft Excel is de functie KALENDERWEEK beschikbaar waarmee u de kalenderweek voor een bepaalde datum kunt weergeven. U kunt bijvoorbeeld automatisch laten zien in welke week een afspraak plaatsvindt. De tabelfunctie KALENDERWEEK in Excel heeft ook zijn valkuilen. Dit komt omdat een kalenderweek in Duitsland, Oostenrijk en Zwitserland anders wordt gedefinieerd dan in aangrenzende Europese landen of de VS. Dit artikel laat zien hoe je alsnog de juiste kalenderweek kunt berekenen en wat de verschillen zijn tussen de kalenderweek in de DACH-regio en andere landen.

De functie KALENDER WEEK in Excel

De functie KALENDERWEEK berekent ook de kalenderweken in de Europese versies van Excel volgens de Amerikaanse standaard. De eerste dag van het jaar wordt over het algemeen toegewezen aan kalenderweek 1.

De syntaxis voor het berekenen van de kalenderweek is:

= KALENDERWEEK (DATUM)

Een kalenderweek wordt in Duitsland, Oostenrijk en Zwitserland anders gedefinieerd. Het is eigenlijk de week die als eerste week van het jaar uit minimaal vier dagen bestaat. Als 1 januari op een vrijdag, zaterdag of zondag valt, retourneert de tabelfunctie KALENDERWEEK een onjuist resultaat voor Europa.

Bepaal de kalenderweken correct in Excel - zo werkt het

Om de juiste kalenderweek voor Duitsland, Oostenrijk of Zwitserland weer te geven, heeft u een formule nodig. Dit moet bijvoorbeeld als volgt zijn voor cel C4:

= TRIM ((A4-DATUM (JAAR (A4-REMAIN (A4-2,7) +3); 1; REST (A4-2,7) -9)) / 7)

De formule is complex en uitgebreid. Om uit te leggen hoe het werkt, beschouwen we de afzonderlijke componenten van de formule afzonderlijk:

• De functie RUST (A4-2; 7) komt meerdere keren voor in de formule. Deze functie deelt de datum door 7. en retourneert de resulterende rest. Deze functie retourneert de waarde 0 voor maandag, de waarde 1 voor dinsdag tot een waarde van 6 voor zondag.
• In de formule wordt het getal 2 afgetrokken van de datumwaarde. De reden hiervoor is dat de basisdatum 1/1/1900 een zondag was. Gedeeld door 7 resulteert in een rest van 1. Om deze reden zou maandag worden opgelost naar het nummer 2. Om de dagtelling te corrigeren naar 0 voor zondag, 1 voor maandag, enz., wordt het getal 2 van de datum afgetrokken.
• Op basis van deze berekening van de weekdag geeft het onderdeel (JAAR (A4-RUST (A4-2; 7) +3) een jaarlijkse waarde verminderd met 1 voor de eerste drie dagen van het jaar, op voorwaarde dat deze dagen vrijdag, zaterdag of zondag zijn handelingen.
• Deze jaarwaarde wordt opgenomen in een datum binnen de DATE-functie. Deze datum wordt meestal gevormd met het tweede argument 1 voor de maand januari.
• Breng als dagwaarde de hierboven beschreven REST-functie over naar de DATE-functie minus de waarde 9. Dit resulteert in het algemeen in een negatieve dagwaarde. De formule laat zien dat de datum berekend door DATE over het algemeen in december is.
• Voor de eerste drie dagen van het jaar is het echter december van het jaar vóór de voorlaatste, op voorwaarde dat deze drie dagen vrijdag, zaterdag of zondag zijn.
• U trekt deze datum berekend door de functie DATUM af van de datum in kolom A, deelt het verschil door 7 (voor de 7 dagen van een week) U gebruikt deze hele berekening binnen een KORTE functie om alleen gehele getallen te verkrijgen.

Alternatief: gebruik het functieargument WOCHENSTART

Een andere mogelijkheid om met de functie KALENDER WEEK in Microsoft Excel te werken en correcte resultaten te krijgen, is door het functieargument weekstart aan de formule toe te voegen. Net als bij de hierboven getoonde formule, kost het moeite om de formule te bouwen.

Het functieargument WOCHENSTART kan onderscheid maken tussen de twee verschillende systemen voor het berekenen van de kalenderweek:

1. Systeem 1 (Amerikaans systeem) - rapporteert week 1 als de week waarin 1 januari valt.
2. Systeem 2 (Europese norm volgens ISO 8601) - KW 1 is de week waarin de eerste donderdag van het jaar valt.

Als u het functieargument WOCHENSTART en de kalenderweekfunctie gebruikt, moet u altijd de volgende getallen als argument invoeren:

maandag	2 of 11
Dinsdag	12
woensdag	13
donderdag	14
vrijdag	15
zaterdag	16
zondag	1 of 17

Koppel de functie KALENDER WEEK met het functieargument WOCHENSTART in 6 stappen

Ga als volgt te werk om de kalenderweekfunctie te koppelen aan het functieargument weekstart:

1. Voer een datum in uw Excel-spreadsheet in.

2. Klik met de linkermuisknop in de cel waarin u de kalenderweek wilt opnemen.

3. Schakel over naar het tabblad FORMULES en klik met de linkermuisknop op "Functie invoegen".

   

4. Typ KALENDER WEEK in het zoekveld.

   

5. Nadat u de functie KALENDER WEEK hebt gevonden, bevestigt u uw keuze met OK.

6. Zodra u in het invoerveld van de functie bent, voert u de formulecomponenten als volgt in:

   • Serienummer = cel met de datum, bijvoorbeeld A1.
   • Nummertype = numerieke waarde voor de dag van de week volgens de bovenstaande tabel.
   

De kalenderweek wordt correct uitgevoerd volgens ISO 8601.

Hoe het begin en het einde van een kalenderweek in Excel te berekenen

Kalenderweken spelen een cruciale rol in professionele planning. Afspraken worden vaak gepland of afgesproken voor een bepaalde kalenderweek. In dat geval moet u

1. Weet ook in welke kalenderweek een bepaalde datum valt
2. Ontdek op welke datum een specifieke kalenderweek begint of eindigt.

Deze informatie helpt u bijvoorbeeld bij het plannen van productiedata in een industriële omgeving of bij het regelen van leverings- of facturatiedata.

Met een speciale formule bepaalt u eenvoudig het begin en einde van een kalenderweek. De formule heeft de volgende opbouw:

= TEKST (DATUM (jaarnummer; 1; 7 * calweek-3-
WEEKDAG (DATUM (jaarnummer;;); 3)); "DD.MM.YY")
& "-" & TEKST (DATUM (jaarnummer; 1; 7 * kalenderweek + 3-
WEEKDAG (DATUM (jaarnummer ;;); 3)); "DD.MM.YY")

Je geeft twee argumenten door aan de formule: Met het argument Jaarnummer definieer het jaar waarin de kalenderweek zich bevindt. Je geeft het nummer van de kalenderweek door met het argument Kalweek.

Het resultaat van de formule is de datum van de maandag van deze kalenderweek, gevolgd door een koppelteken en de datum van de zondag van de kalenderweek.

In de getoonde werkmap levert de volgende formule in cel D4 de periode "30/09/13 - 10/6/13" als resultaat:

= TEKST (DATUM (C4; 1; 7 * B4-3-WEEKDAG)
(DATUM (C4 ;;); 3)); "DD.MM.YY") & "-" & TEKST
(DATUM (C4; 1; 7 * B4 + 3-WEEKDAG (DATUM (C4 ;;); 3));
"DD.MM.JJ")

Deze periode is kalenderweek 40 uit cel B4 in 2013 uit cel C4.

Het resultaat van de formule levert tekst op die u niet kunt gebruiken om verder te rekenen. Om de tijdsperiode die wordt uitgevoerd te verduidelijken, kunt u het resultaat zo weergeven dat ook de dagen van de week worden uitgevoerd. Wijzig hiervoor de getalnotatie in de twee TEKST-functies van de formule als volgt:

= TEKST (DATUM (C4; 1; 7 * B4-3-WEEKDAG)
(DATUM (C4 ;;); 3)); “DDD DD.MM.
JJ “) &“ - “& TEKST (DATUM (C4; 1; 7 * B4 + 3-
WEEKDAG (DATUM (C4 ;;); 3)); "DDDD.MM.JJ")

Na het kopiëren van de formule naar alle rijen van de tabel, worden de datums altijd uitgevoerd met de dagen van de week.

Hoe weken in Excel te berekenen en de juiste datum te vinden

In veel plannen spelen weken als eenheid een rol, bijvoorbeeld kalenderweken of productieweken. Weken kunnen ook worden berekend en weergegeven in Excel met behulp van gemakkelijk te begrijpen formulieren.

De volgende afbeelding toont een lijst met datumwaarden in kolom A. In kolom B staat een waarde die aangeeft hoeveel weken deze datumwaarde moet worden uitgesteld:

In kolom C wil je een formule gebruiken die de nieuwe datum berekent op basis van de datum in kolom A en het aantal weken in kolom B. Als het aantal weken een waarde groter dan nul is, wordt de datum naar de toekomst verschoven, als de waarde negatief is, wordt deze naar het verleden verschoven.

U kunt dit doel bereiken door het aantal weken te vermenigvuldigen met de waarde 7 en op te tellen bij de oorspronkelijke datum. Met de hierboven beschreven tabelstructuur ziet de formule voor cel C2 er als volgt uit:

= A1 + 7 * B1

De formule is toepasbaar omdat Microsoft Excel datumwaarden intern als getallen opslaat. De afstand van de ene dag naar de andere is 1. De volgende afbeelding toont het gebruik van de formule in een voorbeeldtabel:

Conclusie: Kalenderweken zijn cruciaal voor professioneel afsprakenbeheer

Afspraken bepalen ons leven, zowel professioneel als privé. Bij het plannen van afspraken worden de kalenderweken bij toenemende afspraakintensiteit belangrijker, omdat ze kunnen worden gebruikt om meer duidelijkheid te scheppen in de afsprakenplanning.

Microsoft Excel kan voor elke datum de juiste kalenderweek specificeren. Doorslaggevend hierbij is dat gebruikers in Europa het Europese datumsysteem volgens ISO 8601 in acht nemen bij het bouwen van hun Excel-formules. Standaard wordt de kalenderweek berekend volgens het Amerikaanse systeem, wat leidt tot afwijkingen. In de VS wordt de eerste dag van het jaar over het algemeen toegewezen aan kalenderweek 1. In Duitsland, Oostenrijk en Zwitserland daarentegen wordt de eerste kalenderweek doorgaans gedefinieerd als de week die als eerste week van het jaar uit ten minste vier dagen bestaat. In de praktijk betekent dit dat week 1 in het Europese systeem de week is waarin de eerste donderdag van het jaar valt.

Voor de juiste berekening van de kalenderweek kunt u zowel de functie KALENDERWEEK als het functieargument WEEKSTART in Excel gebruiken. Het is ook mogelijk om individuele formules op te zetten en specifiek aan uw spreadsheet aan te passen.