Weeknummers berekenen in Excel

Door: Ron de Bruin (Engelstalig), Microsoft Excel MVP

Een veel gehoorde opmerking van Excel-gebruikers is de volgende: De berekening van het weeknummer geeft onverwachte resultaten als ik de WEEKNUMMER-functie in Excel gebruik.

In dit artikel leggen we uit wat het verschil is tussen de WEEKNUMMER-functie van Excel en het ISO8601: 2000 weeknummersysteem dat wij in Europa gebruiken, en geven aan hoe u het juiste weeknummer kunt berekenen.

De Excel WEEKNUMMER werkbladfunctie

In Excel is een werkbladfunctie genaamd =WEEKNUMMER(Serieel_getal;Type_getal)

Type_getal bepaalt de eerste dag van de week (optioneel)

1 = Eerste dag van de week is zondag

2 = Eerste dag van de week is maandag

Als je Type_getal niet gebruikt dan zal zondag als eerste dag van de week gebruikt worden.

In Europa en dus ook in Nederland beginnen we de week op Maandag dus we gebruiken hier het nummer 2.

Serieel_getal : De datum of het cel adres van een cel met een datum.

Kijk hier voor meer informatie over de Weeknummer-functie.

In Excel worden datums opgeslagen als seriële getallen, zodat deze datums in berekeningen kunnen worden gebruikt en Windows gebruikt het 1900-datumsysteem. Dit houd in dat het nummer 1 de datum 1-Jan-1900 is en het in het voorbeeld gebruikte getal 39492 het seriële nummer voor de datum 14 februari 2008 is.

Voorbeeld voor 14 februari 2008:

=WEEKNUMMER("2008-02-14";2)

Gebruik altijd jjjj-mm-dd, want dit werkt in elke taalversie van Excel.

Omdat 14 februari 2008 39492 dagen na 1 januari 1900 is, werkt dit ook:

=WEEKNUMMER(39492;2)

Maar de datum gebruiken via een celverwijzing (bv: A1) zal in de praktijk het meeste voorkomen:

=WEEKNUMMER(A1;2)

Deze functie kan je gebruiken om het weeknummer te berekenen van een datum volgens de Amerikaanse standaard. Daarbij begint week 1 op 1 Januari en week 2 op de volgende zondag of maandag. Dit betekent dat er niet in alle weken van het jaar 7 dagen zijn in dit weeknummersysteem en dat het mogelijk is dat er een week 54 voorkomt in een jaar.

Tot en met Excel 2003 is deze functie een deel van de Analysis Toolpak-invoegtoepassing van Microsoft. Deze invoegtoepassing wordt bij een normale Office-installatie wel geïnstalleerd, maar niet aangevinkt in Extra:Invoegtoepassingen in de menubalk. Als Analysis Toolpak niet aangevinkt is zal de bovenstaande formule deze fout weergeven: #Naam?. In Excel 2007 is het een standaard werkbladfunctie dus werkt deze formule altijd.

Zie ook Invoegtoepassingen in- of uitschakelen in Office-programma's.

Nadeel van deze functie

In Europa werken we haast allemaal met ISO-weeknummers dus de berekeningen van deze functie komen niet overeen met ons ISO-weeknummer systeem.

Analysis Toolpak functies worden niet automatisch vertaald als je een Excel bestand die gebruik maakt van een Analysis Toolpak functie opent in een andere taalversie van Excel. Omdat het in Excel 2007 een standaardfunctie is hebben we geen vertaalprobleem meer maar wel een ander probleem, zie dit artikel (Engelstalig).

Het ISO-weeknummer

Alle ISO-weken hebben 7 dagen en beginnen op een maandag. Week 1 begint op de maandag van de week waarin de eerste donderdag van het kalenderjaar valt. Dit betekent dat week 1 in het jaar er voor kan beginnen en week 52 of 53 kan eindigen in het jaar erna.

Er is helaas geen standaardfunctie in Excel om het ISO-weeknummer te berekenen.

We hebben twee opties:

1: We gebruiken een werkbladfunctie om het weeknummer te berekenen van de datum in cel A1. Dat ziet er als volgt uit:

=INTEGER((A1-DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3)+WEEKDAG(DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3))+5)/7)

Het voordeel is dat er geen VBA-code nodig is en dat het werkt in elke Excel taalversie waarin het bestand word geopend.

2: We gebruiken een VBA-functie (door gebruiker gedefineerd) in een standaardmodule in het bestand zodat we een korte werkbladfunctie kunnen gebruiken in het werkblad. Deze methode werkt ook in elke taalversie van Excel.

Plak de onderstaande VBA-functie in een standaardmodule van je bestand.

  • Open je Excel-bestand
  • Druk op Alt+F11 om de VBA-editor te openen
  • Kies uit menu Invoegen de optie Module
  • Plak de onderstaande functie in de zojuist ingevoegde module (het rechtervenster)
  • Druk op Alt+Q om de VBA-editor weer te sluiten
  • Kies uit het menu Bestand de optie Oplaan
Public Function IsoWeekNumber(d1 As Date) As Integer

   'Attributed to Daniel Maher
   Dim d2 As Long
   d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
   IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) / 7)

End Function

Voor meer informatie zie ook deze pagina (Engelstalig)

Je kan nu de onderstaande werkbladfunctie gebruiken om het weeknummer van A1 te berekenen.

= IsoWeekNumber(A1)

Nadelen

Je moet macro’s activeren als je het bestand opent . Als de beveiliging op hoog staat werkt het helemaal niet. Dit kan dus een problem zijn als ook anderen het bestand moeten gaan gebruiken. Zie ook het artikel Macro's in Office-documenten in-of uitschakelen.

In Excel 2007 ben je verplicht het bestands formaat .xlsm te gebruiken omdat het niet mogelijk is om in een normaal .xlsx-bestand VBA-code op te slaan.

Een andere mogelijkheid is om de VBA-functie in een invoegtoepassing of in je persnlk.xls werkmap op te slaan. Als deze invoegtoepassing is geïnstalleerd kan elk bestand de werkbladfunctie gebruiken zonder dat de VBA-functie in elk bestand aanwezig moet zijn. In dit artikel gaan we hier verder niet op in, maar een apart artikel in de toekomst hierover is niet uitgesloten.

Voorbeeld

In cel A1 tik de datum van maandag 4 januari 2010 in 4 -1-2010 en gebruik de bovenstaande formules om het weeknummer te berekenen.

De WEEKNUMMER werkbladfunctie geeft als antwoord week 2, maar de ISO-functie geeft als antwoord week 1

Bij de WEEKNUMMER werkbladfunctie is week 1 van 1 tot 3 januari 2010.

Reden : Week 1 begint op 1 Januari en week 2 op de volgende zondag of Maandag. Dus 4 januari 2010 is week 2 volgens dit weeknummersysteem.

Bij de ISO-functie is week 1 van 4 januari 2010 tot 10 januari 2010.

Reden : Week 1 begint op de maandag van de week waarin de eerste donderdag van het kalenderjaar valt. Donderdag 7 januari 2010 is de eerste donderdag van het kalenderjaar dus de maandag er voor is de start van week 1.

Je ziet dat er het hele jaar er een verschil is van 1 week in 2010 (dit jaar is maar een voorbeeld). Ook zie je dat de Excel-werkbladfunctie dat jaar 53 weken heeft en ISO maar 52.

Dit kan vervelende misverstanden veroorzaken als er geen goede afspraken gemaakt worden over welk weeknummersysteem gebruikt gaat worden. Download het weeknummer kalender bestand van mijn site (Engelstalig) om de weeknummers in de verschillende weeknummersystemen te bekijken in elk jaar dat je wil.

Conclusie

De WEEKNUMMER werkbladfunctie van Excel is niet altijd geschikt om het juiste weeknummer weer te geven voor Nederland en andere landen in Europa. De oplossingen in dit artikel stellen je in staat toch het weeknummer te berekenen volgens de ISO8601:2000-norm.

Meer informatie

Excel en macro’s

Datum en tijdfuncties in Excel

De functie WEEKNUMMER

Implementing Week-Numbering Systems and Date/Time Representations in Excel 2007 (Engelstalig)

Week numbers in Excel (Engelstalig)

ISO Date Representatation and Week Numbering (Engelstalig)

 
 
Van toepassing op:
Excel 2007