ISO weeknummers in Excel

[keesvalkenswaard]

Er is al heel wat geschreven over ISO weeknummers in Excel.
De klassieke functie WEEKNUMMER [of weeknum in de Engelse versies] geeft in bepaalde jaren een fout resultaat.
Microsoft zegt:
WEEKNUMMER(serieel_getal;type_resultaat)
serieel_getal is een datum in de betreffende week. Datums moeten worden opgegeven met de functie DATUM of als resultaten van andere formules of functies. Gebruik bijvoorbeeld DATUM(2008;5;23) voor de datum 23 mei 2008. Er kunnen problemen optreden als u datums opgeeft als tekst.
type_resultaat is een getal dat bepaalt op welke dag de week begint. De standaardwaarde is één.
type_getal Begint de week
1 Op zondag en zijn de weekdagen genummerd van 1 tot 7.
2 Op maandag en zijn de weekdagen genummerd van 1 tot 7.
Hierbij mag men het tweede argument weglaten en wordt dus 1 als standaardwaarde genomen.
Je zou denken dat zoiets als =WEEKNUMMER(A1;2), aannemende dat er een datum staat in A1 altijd goed werkt. Nee dus.
Hier in het Officeforum kun je vinden van Jan Karel Pieterse:

Code: Selecteer alles

{=INTEGER((A1-SOM(REST(DATUM(JAAR(A1-REST(A1-2;7)+3);1;2);{1E+99\7})*{1\-1})+5)/7)}

Je moet de formule typen zonder de buitenste krulhaken en dan afsluiten met {Ctrl Shift Enter} waardoor het een macrofunctie wordt.
Dit werkt maar is voor veel mensen lastig en nauwelijks te doorgronden.
Ron de Bruin heeft een andere formule op een Microsoft site:

Code: Selecteer alles

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

Ook die werkt weer goed voor de ISO weeknummers maar is zeker niet de meest efficiënte.
Mijn standaardfunctie was:

Code: Selecteer alles

=GEHEEL((A1-WEEKDAG(A1;2)+4-DATUM(JAAR(A1-WEEKDAG(A1;2)+4);1;1))/7)+1

Natuurlijk kun je GEHEEL door INTEGER vervangen en mag je die 1 achteraan weghalen en de losse 4 middenin door 11 vervangen.
Er is echter hoop. In Excel 10 is er de mogelijkheid het tweede argument de waarde 21 te geven. Daarmee werkt de functie kennelijk zoals verwacht voor ISO (en Europa).
Nu een uitleg.
De klassieke weeknummer functie van Excel is dus niet goed voor Europa.
We proberen de donderdag in de opgegeven week te vinden, via dag - weekdag + 4.
Hierbij bedenken we dat dagnummers [volgens ISO] beginnen met maandag.
Omdat Amerikanen nog iets achter lopen denken die dat zondag dag 1 is.
Daarom in de functie weekdag als tweede argument een 2 om de week met maandag te laten beginnen. [dat is dus bij ons dag 1]
Waarom zoeken we donderdag?
Wel, bij ons is week 1 de week waarin vier dagen van begin januari liggen. Dus in ieder geval donderdag, vrijdag, zaterdag en zondag [die de laatste dag van de week is].
Als we van donderdag uitgaan kunnen we in hetzelfde jaar 1 januari nemen.
Dat kan met de functie DATUM(jaar;maand;dag) of met DATUMWAARDE("datumtekst"). Die laatste functie kan problemen geven bij andere versies omdat Amerikanen eerst de maand en dan pas de dag opgeven. Rare lui! Je loopt ook nog risico met verschillende datum scheidingstekens [/ of -]. Dus beter niet doen! Diverse Amerikaanse formules maken wel die fout, daarmee de functie voor Internationaal gebruik weinig zinvol makend.
Dit verschil [tussen donderdag in de betreffende week en 1 januari van het jaar waarin die donderdag valt] gedeeld door 7 geeft het aantal weken. We moeten even afronden op een geheel getal met de functie GEHEEL [of INTEGER] en er nog 1 bijtellen om niet in week 0 te komen.
De truc om eerst naar de donderdag te gaan is in ieder geval nodig voor jaren waarin er een week 53 is. Dan horen de eerste dagen van januari immers nog bij het vorig jaar. De donderdag ligt dan in het jaar ervoor. Jaren met begin januari in week 52 vragen ook om die aanpassing. Maar op deze manier komt het altijd goed! Dit doordat de deling door 7 en 1 erbij het juiste weeknummer levert.
Wie het allemaal niet gelooft pakt maar eens een aantal agenda's en probeert het door datums in te vullen.

[snb]

Een VBA-aanvulling op Kees' bijdrage:

Code: Selecteer alles

Function ISOweek(d1 as Date)
  ISOweek = DatePart("ww", d1, 2, 2)
End Function

"ww" : het weeknummer
d1 : de als argument doorgegeven datum
2 : maandag is de eerste dag van de week
2 : week 1 is de eerste week van het jaar waarin tenminste 4 dagen van het jaar vallen.

Kanttekening:

Onze MS-vrienden die de datepart-funktie geprogrammeerd hebben, hebben vergeten dat er iedere 4 jaar een schrikkeljaar is.
Daardoor wordt per eeuw aan 11 of 12 data een verkeerd weeknummer toegekend (bijv. 31-12-2007 en 30-12-2019). Dat gebeurt in een reeks van 12 jaar, 12 jaar, 4 jaar.
Meestal gaat het om een maandag, sporadisch een zondag. Iedere andere dag van diezelfde week is dan geschikt om het weeknummer te bepalen.
De donderdag bijvoorbeeld.

Code: Selecteer alles

Function isoweek(d1 as date)
   isoweek =DatePart("ww", d1 + 4 - Weekday(d1, 2), 2, 2)
End function