voorbeeldbestand
ISO weeknummer


1 ISO weeknummer

2.1 ISO-week: VBA
2.2 ISO-week: Excelformule

3.1 dag in ISO-week: VBA
3.2 dag in ISO-week: Excelformule
3.3 dag in ISO-week: UDF

1 Het ISO weeknummer

De ISO-criteria voor de eerste week van het jaar zijn:
- een week begint op maandag; maandag is dus de 1e dag van de week
- de eerste week van het jaar moet minstens 4 dagen van het nieuwe jaar bevatten

ISO-weeknr1111
maandag1
dinsdag21
woensdag321
donderdag4321
vrijdag54321
zaterdag654321
zondag7654321

Uit dit overzicht blijkt:
- dat 1 januari alleen in week 1 valt als 1 januari op een maandag, dinsdag, woensdag of donderdag valt.
- dat 4 januari altijd in week 1 van een jaar valt.
- dat de eerste donderdag van een jaar altijd in week 1 valt

2.1 VBA en ISO-weeknummer

VBA bevat 2 funkties die het weeknummer van een datum kunnen bepalen: DatePart en Format.
Datepart geeft een getal als resultaat, Format een tekst.
Voor de eenvoud bespreken we hier Datepart.

Het weeknummer krijg je met:
weeknummer=DatePart("ww",Datum)
In deze vorm krijgen we het Amerikaanse resultaat (1e dag van de week: zondag; 1e week van het jaar: de week waarin 1 januari valt)

Aangepast op de ISO-norm
Aan de funktie Datepart kun je een argument meegeven, welke dag de eerste dag van de week is. In het ISO-geval vbMonday.
Aan de funktie Datepart kun je een argument meegeven als criterium voor de eerste week van een jaar. In het ISO-geval vbFirstFourdays.
Deze argumenten kennen een lange schrijfwijze en een korte (met cijfers).
ISOweeknummer=Datepart("ww",Datum,vbMonday,vbFirstFourDays)
ISOweeknummer=Datepart("ww",Datum,2,2)
Er zit echter een bug in deze funktie:
1 keer per 400 jaar gaat het mis met een zondag of een maandag, omdat onvoldoende rekening is gehouden met schrikkeljaren per 400 jaar.
De funktie wordt robuust als je niet het weeknummer van de dag laat berekenen maar het weeknummer van bijv. de donderdag in diezelfde week

Berekening:
De funktie weekday berekent het nummer van de dag: ma=1, di=2,wo=3,.. etc.
De berekening is afhankelijk van de instelling van de eerste dag van de week; vergelijkbaaar met Datepart, doen we dat met het argument vbMonday.
dagnummer=weekday(datum,vbmonday)
dagnummer=weekday(datum,2)
Trek van een datum het dagnummer af; resultaat: de zondag van de voorafgaande week.
zondag=datum-weekday(datum,2)
Tel bij zondag 4 op; resultaat: de donderdag van die week.
donderdag=datum-weekday(datum,2)+4
Het weeknummer van de donderdag in dezelfde week als een datum:
ISOweeknummer=Datepart("ww",Datum-weekday(datum,2)+4,2,2)

2.2 ISO-weeknummer: een werkbladformule

De eerste donderdag van het jaar valt altijd in week 1 van het jaar volgens de ISO-criteria.
Het weeknummer van een datum kun je bepalen met:
hoeveel weken zijn er verstreken vanaf de eerste donderdag van een jaar tot de donderdag in de week waarin die datum valt.

- bereken de donderdag in dezelfde week als A1
A1-WEEKDAG(A1;2)+4
- bepaal in welk jaar die donderdag valt
JAAR(A1-WEEKDAG(A1;2)+4)
- bepaal 1 januari van het jaar waarin die donderdag valt
DATUM(JAAR(A1-WEEKDAG(A1;2)+4);1;1)
- bereken het verschil tussen de donderdag en 1 januari
A1-WEEKDAG(A1;2)+4-DATUM(JAAR(A1-WEEKDAG(A1;2)+4);1;1)
- bereken het aantal weken door dit verschil te delen door 7 en op een geheel getal af te ronden
=GEHEEL(....)/7)
- tel daar 1 bij op, omdat anders de eerste week het nummer 0 krijgt.
=(....) +1

Resultaat:
=GEHEEL((A1-WEEKDAG(A1;2)+4-DATUM(JAAR(A1-WEEKDAG(A1;2)+4);1;1))/7)+1
Eenzelfde resultaat geeft de formule waarin de laatste correctie in de formule zelf zit verwerkt.
Deze formule berekent het verschil tussen de eerste donderdag van het jaar en de donderdag in de week volgend op de datum
=GEHEEL((A1-WEEKDAG(A1;2)+11-DATUM(JAAR(A1-WEEKDAG(A1;2)+4);1;1))/7)

3.1 VBA: bereken een bepaalde dag in een bepaalde (ISO) week

De berekening:
Tel bij de zondag voorafgaand aan week 1 van het jaar, 7 keer het aantal weken -1, + het weekdagnummer op.
Zet het jaar in de variabele jaar: 4 cijfers
Zet het weeknummer in de variable week: 1...53
Zet de gewenste dag in de variabele dag (maandag=1, dinsdag=2, woensdag=3 etc. )

- In het ISO-systeem valt 4 januari altijd in week 1
Dateserial(jaar;1;4)
- in het ISO-systeem is de maandag de eerste dag van de week - trek van 4 januari het weekdagnummer af; resultaat: de zondag voor 4 januari
Dateserial(jaar;1;4)- Weekday(DateSerial(jaar, 1, 4), 2)
- voeg er een dagnummer aan toe: de gewenste dag in week 1 van het jaar
Dateserial(jaar;1;4)- Weekday(DateSerial(jaar, 1, 4), 2)+dag
- tel daarbij op het aantal weken * 7; corrigeer met -1 week
7*(week-1)+Dateserial(vjaar;1;4)- Weekday(DateSerial(jaar, 1, 4), 2)+dag

3.2 Bereken een bepaalde dag in een bepaalde (ISO) week: werkbladformule

In A1 het jaar:4 cijfers
In A2 het weeknummer: 1...52
In A3 de te brekenen weekdag: 1...7

- In het ISO-systeem valt 4 januari altijd in week 1
DATUM(A1;1;4)
- trek van 4 januari het weekdagnummer af: de zondag voor 4 januari
DATUM(A1;1;4)-WEEKDAG(DATUM(A1;1;4);2) - voeg een weekdagnummer toe (ma=1, di=2, wo=3, etc.)
DATUM(A1;1;4)-WEEKDAG(DATUM(A1;1;4);2)+A3
- tel daarbij op het aantal weken in cel A2 * 7; en corrigeer met -1 week
=7*(A2-1)+DATUM(A1;1;4)-WEEKDAG(DATUM(A1;1;4);2)+A3

3.3 VBA-formule als User Defined Function (UDF)

In plaats van een formule in het werkblad kun je gebruik maken van de VBA-formule in de vorm van een UDF
Zet deze UDF in een macro-module van het werkboek.

Het ISO-weeknummer bepaal je met deze UDF:
Public Function ISOweeknum(ByVal Datum As Date) As Integer
ISOweeknum = DatePart("ww", Datum - Weekday(Datum, 2)+4, 2, 2)
End Function
Als in cel A4 de datum staat, voer de formule in als:
=ISOweeknum(A4)

Een bepaalde dag in een bepaalde ISO-week bepaal je met deze UDF:
Function ISOdag(jaar As Integer, week As Integer, dag As Integer) As Long
ISOdag = 7 * (week - 1) + DateSerial(jaar, 1, 4) - Weekday(DateSerial(jaar, 1, 4), 2) + dag
End Function
Als in A1, A2 en A3 resp. jaar, weeknummer en weekdagnummer staan, voer de formule in als :
= ISOdag(A1;A2;A3)