Excel - formule wijzigen voor schrikkeljaar

[McCees]

Beste iedereen,

Ik heb voor mijn werk een Excel-bestand waar ik in cel B1 een weekgetal neerzet, waardoor er op verschillende andere cellen automatisch datums komen.

Op maandag staat deze formule:
=DATUM(JAAR(2024);1;-3)-WEEKDAG(DATUM(JAAR(2023);1;3))+B1*7

Op dinsdag:
=DATUM(JAAR(2024);1;-3)-WEEKDAG(DATUM(JAAR(2023);1;3))+B1*7+1

Enzovoort.

Nu wil ik dit document volgend jaar weer gebruiken, maar volgend jaar is een schrikkeljaar en op 1 of andere manier komt dit nu niet goed. Uiteraard heb ik de jaargetallen wel veranderd naar 2025 en 2024.

Nu heb ik via ChatGPT al het een en ander gevraagd, maar op één of andere manier kom ik er niet uit...

Dus ik zoek hulp van de community!

Graag hoor ik van jullie.

[dix-neuf]

Als in A1 de eerste dag van het jaar staat (01-01-2024), dan kun je de eerste iso-weekdag (=maandag) van het weeknummer van 2024 dat in B1 staat, in een willkeurige cel weergeven aldus: =$A$1+(B1-1)*7 . Voor de volgende dagen tel je bij die uitkomst telkens 1 dag op.

[ Voor 29% gewijzigd door dix-neuf op 19-12-2023 15:07 ]

[Dido]

Even los van weeknummers:

Als je in een cel een juiste datum voor maandag hebt, dan kun je voor dinsdag gewoon +1 doen.
Dus als je maandag-formule werkt, werkt alles.

Als de datum voor maandag in X12 staat, en je wilt dinsdag in X13, dan zet je in X13 gewoon =X12+1

---

Dan, weeknummers. Het meest verwarrende en ingewikkelde stukje kalenderneuken dat ooit is verzonnen.

Je kunt ingebouwde formules in Excel gebruiken om met weeknummers te werken - MAAR omdat Microsoft uit het land komt waar ISO een vies woord is, begint de week voor MS op zondag. En dat betekent dat week 1 in de VS een volle week kan verschillen van week 1 in de beschaafde wereld. Daar is wel iets mee te doen door Excel t evertellen dat je toch echt wiul dat de week op maandag begint, ipv zondag, maar het blijft lastig.

Je kunt ook, zoals je dat zo te zien probeert, zelf gaan rekenen met datums, maar dan gelden twee belangrike regels:
* rekenen met datums is een van de meest foutgevoelige zaken die er zijn
* vanwege nummer 1: Keep It Simple!

De simpelste oplossing is om zelf ergens de datum van de maandag in week 1 in te vullen (voor 2024 is dat maandag 1 januari

Zet je die datum in A1, en vul je in B1 je weeknummer in, dan kun je in C1 de datum voor de maandaf van die week zetten met:
=A1+7*(B1-1)

In C2 krijg je dan de dinsdag met =C1+1.

Enzovoort

Voordeel is niet alleen dat je formules simpeler worden, maar je hebt nergens last van schrikkeljaren

[ Voor 3% gewijzigd door Dido op 19-12-2023 14:58 ]

[HaNS1443]

Als je voor weeknummer '1' '01-01-2024' wil hebben kan je die '-3' vervangen voor '-2'. Het is mij niet helemaal duidelijk welke datum je wil hebben voor '1'.

Daarnaast geeft 'DATUM(JAAR(2024);1;-3)' '28-12-1904', ik geloof niet dat dat de bedoeling is, ik verwacht dat dit DATUM(2024;1;-3) zou moeten zijn.

[Lustucru]

HaNS1443 schreef op dinsdag 19 december 2023 @ 14:59:
[..]Daarnaast geeft 'DATUM(JAAR(2024);1;-3)' '28-12-1904',

Exact. Hoe die formule voor welk jaar dan ook kan werken is mij een raadsel. Anyway, als je een weeknummer wilt omrekenen naar een datum (en dat lijkt de bedoeling te zijn) dan is het algoritme als volgt:

Pak een datum waarvan je zeker weet dat die in het nieuwe jaar valt, conform de weeknummers die je wilt gebruiken. Voor het iso-systeem is dat 4 januari: dat valt altijd in week 1. Neem de dag van de week van die datum (iso, maandag = 1) -1 en trek dat van 4 januari af. Alle maandagen van het jaar vallen dan op '4 januari' - correctie + (weeknr-1) * 7. Lijkt op jouw formule maar werkt wél.

code:

=DATUM(2024;1;4)-WEEKDAG(DATUM(2024;1;4);3)+(B1-1)*7

En als je het mooi wilt doen zet je het jaar in A1 en dan hoef je nooit meer een formule te wijzigen:

code:

=DATUM(A1;1;4)-WEEKDAG(DATUM(A1;1;4);3)+(B1-1)*7

[ Voor 8% gewijzigd door Lustucru op 20-12-2023 13:38 ]

[PolarBear]

Dido schreef op dinsdag 19 december 2023 @ 14:57:
Je kunt ingebouwde formules in Excel gebruiken om met weeknummers te werken - MAAR omdat Microsoft uit het land komt waar ISO een vies woord is, begint de week voor MS op zondag.lastig.

Welkom in 2023, Microsoft is daar al en heeft inmiddels al zo'n 5 jaar ondersteuning voor ISO weeknummers in Excel.

[PolarBear]

Lustucru schreef op woensdag 20 december 2023 @ 13:34:
[...]
Voor het iso-systeem is dat 4 januari: dat valt altijd in week 1.

De exacte definitie is de eerste volledige week waarin een donderdag valt. En een week begint in ISO termen altijd op maandag. De regel 4 januari valt in week 1 klopt daarmee ook, maar dit als achtergrond waarom dat zo is.

Komend jaar 2024 hebben we een heel net begin 1 januari is een maandag, is dus ook week 1.

[JJ Le Funk]

=ISO.WEEKNUMMER(VELD MET DATUM) houdt rekening met schrikkeljaren neem ik aan?

[hamsteg]

ISO weeknummers hebben in essentie niets te maken met de exacte datum. Ze kijken wanneer de eerste van januari valt en rekenen daarna door. Als de datum berekening volgens ISO is geïmplementeerd, zal het weeknummer ook goed zijn (datum berekening is bij de millenniumbug als grondig rereviewed). Natuurlijk houden dus ISO Weeknummer rekening met een schrikkeljaar.

Het weeknummer is het volgnummer dat een week in een kalenderjaar krijgt. Volgens de internationale standaard ISO 8601 is de eerste week van een jaar de week die vier of meer dagen van dat kalenderjaar bevat. Omdat maandag als eerste dag van de week wordt beschouwd, komt het erop neer dat week 1 de week is, waarin de eerste donderdag van dat jaar zit en de week waar 4 januari in valt. 1 februari valt altijd in week 5. Ter illustratie: met deze methode valt zaterdag 16 december 2023 in week 50.

Bron: Wiki

[ Voor 18% gewijzigd door hamsteg op 20-12-2023 14:05 ]

[Dido]

Lustucru schreef op woensdag 20 december 2023 @ 13:34:
En als je het mooi wilt doen zet je het jaar in A1 en dan hoef je nooit meer een formule te wijzigen:

code:

=DATUM(A1;1;4)-WEEKDAG(DATUM(A1;1;4);3)+(B1-1)*7

Dit is inderdaad nog een stuk eleganter dan wat ik voorstelde (bij mijn voorstel was handwerk nodig, jakkiebah!)

offtopic:
En ik was lui genoeg om ervan uit te gaan dat ij toch wel langs zou komen in dit topic om mijn snelle fix te verbeteren

[ Voor 2% gewijzigd door Dido op 20-12-2023 17:06 . Reden: Ik had per ongeluk het hele ding ]

[Lustucru]

Dido schreef op woensdag 20 december 2023 @ 15:40:
[...]

offtopic:
En ik was lui genoeg om ervan uit te gaan dat ij toch wel langs zou komen in dit topic om mijn snelle fix te verbeteren

offtopic:
Lui is een goede eigenschap, volgens Pascal. Maar hoe krijg je het voor elkaar om Polarbear te quoten met mijn tekst

[ Voor 6% gewijzigd door Lustucru op 20-12-2023 16:32 ]

[Dido]

Lustucru schreef op woensdag 20 december 2023 @ 16:32:

offtopic:
Lui is een goede eigenschap, volgens Pascal.

offtopic:
Tsja, Pascal was een gokker (met een gemankeerd gevoel voor logica) en volgens hem had de paus gelijk, dus dat zegt ook niet alles

offtopic:
Maar hoe krijg je het voor elkaar om Polarbear te quoten met mijn tekst

offtopic:
Dat lukt alleen met heel veel moeite, twee mensen willen quoten waar ik op reageer, en heel slordig knippen... wat vindt Pascal daarvan?

[Lustucru]

Dido schreef op woensdag 20 december 2023 @ 17:05:
[...]

offtopic:
Dat lukt alleen met heel veel moeite, twee mensen willen quoten waar ik op reageer, en heel slordig knippen... wat vindt Pascal daarvan?

offtopic:
Que Tout le malheur des hommes vient d'une seule chose, qui est de ne savoir pas demeurer en repos dans une chambre.