Kroonjaren filteren

[wimbre]

Hi,

Wij willen onze klanten vanaf hun 65e jaar ieder kroonjaar (65e, 70e, 75e enz.) een kaartje of een bosje bloemen te sturen.

Daarom heb ik vanuit een pakket een klantenbestand in Excel geïmporteerd met de bedoeling om vanuit die lijst iedere maand een selectie te maken van de klanten die volgende maand een kroonjaar bereiken.

Referentienummer Naam Geboortedatum Overlijdensdatum 65 Jaar 70 jaar enz.
2273131 Voorbeeld, A 12-09-85 12-09-2050 12-09-2055
1700400 Example, B 24-07-46 24-07-2011 24-07-2016
170040001 Client, C 02-03-50 2-03-2022

Als er iemand is overleden dan is de kolom van de kroonjaren leeg (is een voorwaarde in de cel).

Door iedere kolom te filteren op het lopende jaar + volgende maand zou het ook moeten lukken maar dat is erg omslachtig.

Is er een manier om vanuit die hele lijst met kroonjaren van 65 t/m 100 jaar iedere maand een overzicht te genereren die alleen laat zien wie er een kroonjaar bereikt en welk kroonjaar dat dan is?

Tips & trics zijn zeer welkom!

[Exterazzo]

Je kunt dan toch de geboortedatum pakken en dan in die kolommen "65 jaar", "70 jaar" etc, gewoon de geboortedatum + 65 jaar doen?

Deze functie kun je daarvoor gebruiken: EDATE function
In het Nederlands heet die functie overigens "ZELFDE.DAG"

[ Voor 9% gewijzigd door Exterazzo op 15-07-2022 08:03 ]

[Patrick_6369]

Ik heb zoiets gebouwd voor onze ledenlijst. Een kolom toevoegen waarin je de leeftijd berekent. Vervolgens in een apart lijstje de kroonjaren neerzetten. En dan met voorwaardelijke opmaak de cellen laten markeren waarbij de leeftijd overeenkomt met een kroonjaar (voor ieder kroonjaar heb ik een aparte opmaak-regel aangemaakt).

[wimbre]

Exterazzo schreef op vrijdag 15 juli 2022 @ 08:00:
Je kunt dan toch de geboortedatum pakken en dan in die kolommen "65 jaar", "70 jaar" etc, gewoon de geboortedatum + 65 jaar doen?

Deze functie kun je daarvoor gebruiken: EDATE function
In het Nederlands heet die functie overigens "ZELFDE.DAG"

De formule in de kolom "65 jaar: =ALS($D2="";DATUM(JAAR($C2)+65;MAAND($C2);DAG($C2));"")

Dit resulteert in de kolommen (65 jr., 70 jr. etc.) de datum waarop ze die leeftijd bereiken. Als er dan 1.000 klanten in staan is het een brei aan datums in Excel.

Dat zal bij gebruik van jouw formule toch niet anders zijn?

[Djordjo]

Ik zou een formule maken die controleert of iemand, gebaseerd op zijn geboortedatum, volgende maand 65/70/75 is, maar deze maand nog 64/69/74 is.

[TheFes]

Een beetje wat @Djordjo voorstelt heb ik in deze Google Sheet gezet
https://docs.google.com/s...SkIqOkm0/edit?usp=sharing

Je kunt die hulpkolommen met volgend kroonjaar en huidige leeftijd ook nog in de laatste formule opnemen, maar voor nu leek het me wel handig om het op te splitsen zodat je duidelijk ziet wat er gedaan wordt.

[DukeBox]

wimbre schreef op vrijdag 15 juli 2022 @ 08:57:
De formule in de kolom "65 jaar: =ALS($D2="";DATUM(JAAR($C2)+65;MAAND($C2);DAG($C2));"")

Als je dan alleen de minimale waarde neemt heb je de 'eerstvolgende' per klant ?
Vraag mij wel af of dit AVG technisch gedekt is

[The Eagle]

Ik weet niet of dit B2B danwel B2C is? Indien B2C:
AVG / GDPR was ook de eerste vlag die bij mij af ging. Een geboortedatum is in principe PII data en mag je alleen gebruiken voor het doeleinde waar je hem voor gekregen hebt vziw. Zou ik even nakijken voor je begint, anders is al je moeite tevergeefs

Having said that: dit soort dingen wil je op je klantendatabase doen en dan automatisch genereren. Klantdata centraal houden en niet rond laten slingeren. Ook is dan het selectiecriterium slechts 1 commando

[Djordjo]

TheFes schreef op vrijdag 15 juli 2022 @ 09:30:
Een beetje wat @Djordjo voorstelt heb ik in deze Google Sheet gezet
https://docs.google.com/s...SkIqOkm0/edit?usp=sharing

Gaat alleen in december niet werken. De huidige maand moet je mod 12 doen.

[wimbre]

Djordjo schreef op vrijdag 15 juli 2022 @ 10:20:
[...]

Gaat alleen in december niet werken. De huidige maand moet je mod 12 doen.

Kun je dit toelichten?

[TheFes]

Djordjo schreef op vrijdag 15 juli 2022 @ 10:20:
[...]

Gaat alleen in december niet werken. De huidige maand moet je mod 12 doen.

Ah, je hebt gelijk ja. Heb het aangepast (met een IF die 1 geeft als de huidige maand 12 is, en anders de huidige maand +1)

[ Voor 14% gewijzigd door TheFes op 15-07-2022 10:24 ]

[timberleek]

wimbre schreef op vrijdag 15 juli 2022 @ 10:22:
[...]
Kun je dit toelichten?

Die formule gebruikt <huidige maand> + 1.
Dat gaat het hele jaar goed, maar in december kijk je dan naar maand 13 (en vanaf dat moment is elke maand fout).

(<huidige maand> + 1)%12 zorgt dat hij terugslaat naar maand 1 aan het eind van elk jaar
(<huidige maand>%12) + 1 zorgt dat hij terugslaat naar maand 1 aan het eind van elk jaar

[ Voor 11% gewijzigd door timberleek op 15-07-2022 14:09 ]

[wimbre]

Top, bedankt voor jullie reacties.!

Ik wens jullie allen een fijn weekend!

[TheFes]

Heb nog een aanpassing gedaan en de MOD functie gebruikt ipv de IF variant die ik daarop zelf geknutseld had. Dat is een betere manier om het probleem wat @timberleek aandrroeg af te vangen.

[Djordjo]

timberleek schreef op vrijdag 15 juli 2022 @ 10:41:
(<huidige maand> + 1)%12 zorgt dat hij terugslaat naar maand 1 aan het eind van elk jaar

Dan gaat het in november mis
Je moet de huidige maand mod 12 doen, en er dan één bij optellen.

[TheFes]

Volgens mij is het correct zoals ik het nu heb (en zoals @timberleek ook aangaf)
(huidige maand)%12 (of in excel MOD(MONTH(TODAY());12) geeft gewoon de huidige maand weer, pas als je er eerst 1 bij optelt kun je eventueel 13 krijgen (in december dus) en zal hij terugvallen naar 1.

[ Voor 3% gewijzigd door TheFes op 15-07-2022 13:30 ]

[Djordjo]

Eh nee, dat is niet hoe modulo werkt; 12 (mod 12) ≡ 0.

[TheFes]

Djordjo schreef op vrijdag 15 juli 2022 @ 13:39:
Eh nee, dat is niet hoe modulo werkt; 12 (mod 12) ≡ 0.

Ah right, my bad.. dan heb je inderdaad wel gelijk ja Heb het aangepast

[timberleek]

Djordjo schreef op vrijdag 15 juli 2022 @ 13:21:
[...]

Dan gaat het in november mis
Je moet de huidige maand mod 12 doen, en er dan één bij optellen.

The devil is in the details.
Je hebt gelijk