Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

Auteur Topic: Excel-beperking mbt datum pré 1900 wegwerken met VBA  (gelezen 244780 keer)

0 leden en 6 gasten bekijken dit topic.

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Gepost op: 15 december 2018, 15:35:25 »
Beste Excellers,

Ik ben al jaren bezig met genealogie (stamboomonderzoek) en krijg dus héél veel te maken met datums pré 1900.
Aangezien excel hiermee niet overweg kan los ik dit nu al jaren op met omwegjes, hulpkolommen en formules...
Ik moet ook veel berekeningen maken met deze datums.
Maar omdat dit toch wel een zeer complex werkje is en bovendien zorgt voor zéér brede tabellen hoop ik dit alles
wat efficiënter en overzichtelijker te kunnen oplossen via VBA.
Ik ben een echte leek wat VBA betreft dus heb ik totaal geen idee hoe eraan te beginnen...

Momenteel los ik dit probleem als volgt op :

De datums, waarmee ik een berekening moet maken, splits ik eerst over 3 kolommen (dag/maand/jaar).
Dan zet ik deze om in één datumcel naar een datum nà 1900 via volgende formule:

=ALS(H18<=1200;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+800;G18;F18));ALS(H18<=1300;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+700;G18;F18));ALS(H18<=1400;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+600;G18;F18));ALS(H18<=1500;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+500;G18;F18));ALS(H18<=1600;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+400;G18;F18));ALS(H18<=1700;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+300;G18;F18));ALS(H18<=1800;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+200;G18;F18));ALS(H18<=1900;ALS(EN(H18="";G18="";F18="");"";DATUM(H18+100;G18;F18));ALS(EN(H18="";G18="";F18="");"";DATUM(H18;G18;F18))))))))))
Deze omzetting doe ik omdat ik de leeftijd tussen de 2 datums moet kunnen berekenen.
De berekening doe ik dan als volgt :

=ALS(OF(U18="";V18="");" ";DATUMVERSCHIL(U18;V18;"Y")&" jaar, "&DATUMVERSCHIL(U18;V18;"YM")&" maand en "&DATUMVERSCHIL(U18;V18;"MD")&" dagen")
Per persoon heb ik 5 datums waarmee ik in totaal 5 leeftijden moet berekenen.
Via mijn complexe oplossing heb ik hierdoor in totaal 15 invoerkolommen (3 per datum omwille van de opsplitsing) en dan nog eens 10 hulpkolommen.
Inclusief alle andere gegevens die ik invoer per persoon maakt dat ik werk met een tabel van maar liefst 46 kolommen wat zéér lastig werkt als ik de gegevens moet invoeren.
Ook staat mijn tabel vol met complexe geneste formules die nogal verwarrend worden.
De gegevenskolommen worden vervolgens in een ander werkblad gebruikt voor automatische invoer (adhv een nummeringave)
in een overzichtelijke personenfiche. Hiervoor gebruik ik dan hoofdzakelijk de functie verticaal zoeken.

Dus enerzijds zoek ik een VBA-oplossing om de datums pré 1900 in één cel te kunnen laten erkennen als datum
en anderzijds zoek ik een VBA-oplossing om al mijn formules en berekeningen over te nemen.

Kan iemand mij hierbij helpen?

Ik dank jullie alvast op voorhand met vriendelijke groetjes,
BlackDevil

1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #1 Gepost op: 15 december 2018, 16:01:25 »
Hallo BlackDevil,

Hartelijk welkom op Oplossing.be  :)

Ik vond een mooie formule voor jou in VBA, een die een Functie aanmaakt.

Je kan deze functie in je werkblad oproepen en laten uitvoeren door
=AgeFunc(B1;B2)
in te voeren in je cel waar je het resultaat wilt zien.
Hier wordt door de Functie het aantal jaren berekent van B1 tot B2

Zie voorbeeldbestand: Datums_voor__1900_Oplossing.be.xlsm

Als je de VBA-code voor die Functie die in mijn bijlage gebruikt wordt naar jou werkboek wilt kopiëren en plakken, voeg dan in de VBA van jou werkblad een module in waar je de code in plaatst (en sla je bestand op als Excel-werkmap met macro's  =  (*.xlsm)

Als je vragen hebt, we zijn er om je te helpen  :).

Groeten,

:) SoftAid :)             



Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #2 Gepost op: 15 december 2018, 17:13:26 »
Hallo BlackDevil,

Hartelijk welkom op Oplossing.be  :)

We kunen en willen je heel graag helpen, maar kan je een gestripte versie van je bestand aanhangen.
Verander de namen, maar laat 1 rij met zijn de formules staan.
Dit maakt het ons veel gemakkelijker om snel en accuraat een oplossing aan te bieden, of in een formule, of in VBA.

Hoe post je een bijlage? (KLIK)

Groeten,

:) SoftAid :)             


Hallo SoftAid,

Alvast bedankt om te willen helpen.
Bij deze een gestripte versie van mijn bestand.

Vriendelijke groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #3 Gepost op: 15 december 2018, 17:19:28 »
Hallo BlackDevil,

terwijl jij je werkblad aan het "strippen" was, had ik al een goede oplossing gevonden (zie hierboven in mijn vorige reactie).

Je zegt maar als je graag wilt dat je de "Functie" even in je voorbeeldbestand wilt toegepast zien.

Groeten,

:) SoftAid :)             
 
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline emields

  • Ervaren lid
  • ***
  • Berichten: 257
  • Geslacht: Man
  • Oplossing.be
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #4 Gepost op: 15 december 2018, 17:21:36 »
Is het niet eenvoudiger de jaartallen als tekst op te slaan en met volgende formule te werken?
zie voorbeeldbestand

 =rechts(b1;4)-rechts(a1;4)   in C1

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #5 Gepost op: 15 december 2018, 17:36:52 »
Hallo emields,

dank voor je reactie.

Je voorstel lijkt aanlokkelijk, maar.... men dient ook rekning te houden met de dag en maand om een juiste ouderdom te kunnen maken.
° 24/11/1988   † 15/10/2000  = gestorven op 11-jarige ouderdom
° 24/10/1988   † 15/11/2000  = gestorven op 12-jarige ouderdom

Maar als ik het voorbeeldbestand van BlackDevil bekijk dan wordt en niet enkel de ouderdom in jaren, maar de ouderdom in dagen, maanden en jaren weergegeven.

Dat blijft dus nog een zware dobber (voor de specialisten)...

BTW... in de Functie wordt de methode mm/dd/jjjj gebruikt !! Dat moet dus ook nog aangepast worden.

:) SoftAid :)             

Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #6 Gepost op: 15 december 2018, 18:37:40 »
Hello BlackDevil,

Moet het per se een vba-oplossing zijn of is een formuletje ook goed genoeg ?  ;)
Je hoeft echt niet volgens de eeuw waarmee je wil rekenen telkens zoveel keer 100 jaar bij te tellen. Excel kan goed genoeg overweg met datums tot in een verre toekomst. Om helemaal op zeker te spelen heb ik dus overal 2000 jaar bijgeteld (tot voor het begin van onze jaartelling vind je vermoedelijk toch geen info  0:-) )
Bijgevoegd voorbeeldje zou nog met een kolom minder kunnen, maar omdat je de functie 'datumverschil' op meerdere wijzen toepast heb ik één hulpkolom (B). In kolom C vind je bij wijze van demo telkens het verschil in jaren t.o.v. de bovenliggende rij.
Kan je hier iets mee ?

Groeten,
pitufo

"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #7 Gepost op: 15 december 2018, 19:13:27 »
Hallo allen,

Alvast reeds enorm bedankt voor het meedenken en de reeds gegeven mogelijke oplossingen.
Ik zal misschien toch eerst een beetje meer details geven over wat nu eigenlijk de bedoeling is.

Per persoon dien ik de volgende datums in te geven :
- Geboorte-datum
- Doop-datum
- Huwelijksdatum
- Overlijdensdatum
- Begraafdatum

Vervolgens dien ik als uitvoer in de personenfiche ook de volgende leeftijden te hebben :

- leeftijd bij doop
- leeftijd bij huwelijk
- leeftijd bij overlijden
- leeftijd bij begraven

En voor de nog in leven zijnde personen ook de leeftijd op vandaag

De output moet dus vermelden  leeftijd : ## jaar, ## maand en ## dagen.

Aangezien excel datums pré 1900 opslaat als tekst (dus niet erkent als datum) is het met een simpele formule
niet mogelijk om de juiste leeftijd in jaren, maanden én dagen te berekenen.

Vandaar dat ik datums verdeelde over 3 kolommen en de jaartallen die pré 1900 zijn laat omrekenen
naar een jaartal na 1900.
Dus jaren tussen 1801 en 1900 liet ik 100 jaar bijtellen,
jaartallen tussen 1701 en 1800 liet ik 200 jaar bijtellen,
enz...

Ik liet dit zo doen omdat ik soms dus ook gewoon datums heb die standaard na 1900 vallen
en soms datums moet berekenen waarvan de ene datum pré 1900 is en de andere na 1900..


Om al die extra kolommen én complexe geneste formules te kunnen vermijden hoopte ik dus
dat het mogelijk zou geweest zijn om via VBA een ingevoerde datum pré 1900 ook daadwerkelijk
te laten opslaan als datum. En indien het niet mogelijk zou zijn om dan gewoon al mijn formules
gewoon in VBA te zetten zodat ik in mijn gegevensbestand zo weinig mogelijk formules heb staan.

Naderhand is het ook nog eens mijn bedoeling om de volledige invoer van de gegevens via een
overzichtelijk UserForm te kunnen ingeven, maar dat zou ik dan naderhand in een nieuwe topic
aangepakt hebben, vandaar dat ik hiervan nog geen vermelding had gemaakt.

Hopelijk heb ik nu al iets duidelijker kunnen omschrijven wat de bedoeling is, zo niet, vraag dan maar
gerust verdere uitleg.
Ik ga ondertussen al eens één en ander uitproberen met de reeds gegeven mogelijke oplossingen.

Vriendelijke groetjes,
BlackDevil

1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #8 Gepost op: 15 december 2018, 19:30:49 »
De output moet dus vermelden  leeftijd : ## jaar, ## maand en ## dagen.

Aangezien excel datums pré 1900 opslaat als tekst (dus niet erkent als datum) is het met een simpele formule
niet mogelijk om de juiste leeftijd in jaren, maanden én dagen te berekenen.

Vandaar dat ik datums verdeelde over 3 kolommen en de jaartallen die pré 1900 zijn laat omrekenen
naar een jaartal na 1900.
Dus jaren tussen 1801 en 1900 liet ik 100 jaar bijtellen,
jaartallen tussen 1701 en 1800 liet ik 200 jaar bijtellen,
Hallo BlackDevil,

Als je gewoon verschuivingen gaat maken met de tijden (jaren) om ze te kunnen tellen, dan gaan je resultaten volgens mij toch afwijken van de realiteit.
In het jaar 1830 was er (mogelijk) een schrikkeljaar (en dus 366 dagen), als je daar voor je berekening 100 jaar bij telt, dan is (mogelijk) 1930 GEEN schrikkeljaar (en maar 365 dagen), en is je uiteindelijke optelling gebaseerd op 1930, en heb je dus 1 dag te weinig in je berekening....

Als je het dus echt correct wilt hebben zal je daar toch rekening mee moeten houden.

:) SoftAid :)             

Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #9 Gepost op: 15 december 2018, 20:11:27 »
Hallo BlackDevil,

Aangezien excel datums pré 1900 opslaat als tekst (dus niet erkent als datum) is het met een simpele formule
niet mogelijk om de juiste leeftijd in jaren, maanden én dagen te berekenen.
Dat heb ik mijn voorbeeld nochtans al weerlegd (als we het adjectief 'simpele' wat ruimer nemen  8) )

'K heb ook je laatste bericht aandachtig gelezen, en mocht je in je berekening met een mogelijke afwijking van 1 dag (zoals SoftAid terecht opmerkte, en waar je in je huidige werkwijze overigens ook mee te maken krijgt) kunnen leven, dan kom je héél ver voor alle gewenste resultaten met de werkwijze die ik je daarnet al heb bezorgd.
Trouwens, het feit dat ik altijd 2000 jaar bijtel maakt de kans op zo een afwijking quasi onbestaande !
Wat wel, en altijd, een probleem kan geven is datumverschillen uitrekenen in jaren + maanden + dagen. Maar dat is inherent aan de datumverschil-functie.
Meer zelfs, als je dat zonder Excel probeert uit te rekenen loop je tegen dezelfde problemen aan...

De output zoals je die wou stond toch al in je bijlage, dus da's enkel een kwestie van de formules op de juiste plaats te zetten.
Succes !

Update : mocht het niet duidelijk zijn, hierbij versie 2 van mijn voorbeeld waarin ook maanden en dagen worden bekeken
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #10 Gepost op: 15 december 2018, 23:32:26 »
Hallo pitufo,

Hallo BlackDevil,
Dat heb ik mijn voorbeeld nochtans al weerlegd (als we het adjectief 'simpele' wat ruimer nemen  8) )

'K heb ook je laatste bericht aandachtig gelezen, en mocht je in je berekening met een mogelijke afwijking van 1 dag (zoals SoftAid terecht opmerkte, en waar je in je huidige werkwijze overigens ook mee te maken krijgt) kunnen leven, dan kom je héél ver voor alle gewenste resultaten met de werkwijze die ik je daarnet al heb bezorgd.
Trouwens, het feit dat ik altijd 2000 jaar bijtel maakt de kans op zo een afwijking quasi onbestaande !
Wat wel, en altijd, een probleem kan geven is datumverschillen uitrekenen in jaren + maanden + dagen. Maar dat is inherent aan de datumverschil-functie.
Meer zelfs, als je dat zonder Excel probeert uit te rekenen loop je tegen dezelfde problemen aan...

De output zoals je die wou stond toch al in je bijlage, dus da's enkel een kwestie van de formules op de juiste plaats te zetten.
Succes !

Update : mocht het niet duidelijk zijn, hierbij versie 2 van mijn voorbeeld waarin ook maanden en dagen worden bekeken

Hartelijk dank voor je hulp.
Ik heb eventjes jouw oplossing toegepast op mijn bestand en hiermee heb ik toch al zo'n 10 kolommen kunnen elimineren dus dat is al
een mooie stap in de goede richting ;-).

Ik stuit nu echter op een ander probleempje.
Zoals ik in één van mijn eerdere berichten reeds vermeld had dien ik ook voor de nog in leven zijnde personen de leeftijd
op dag van vandaag (dus met de 'vandaag()' functie) te berekenen.

In mijn personenfiche moet dan uiteindelijk in één specifieke cel het volgende als resultaat komen te staan :

(voor de nog in leven zijnde personen) => leeftijd : ## jaar, ## maand en ## dag(en) op vandaag
oftewel
(voor de overleden personen) => leeftijd : ## jaar, ## maand en ## dag(en) bij overlijden

wat dien ik dan toe te voegen aan jouw formule?

in die specifieke cel waarin het resultaat moet komen gebruik ik volgende formule :

=ALS(D13="--";"";ALS(D17="--";VERT.ZOEKEN(N5;IndexGCB;2)&" op vandaag";VERT.ZOEKEN(N5;IndexGCB;27)&" bij overlijden"))
Alvast bedankt.

Vriendelijke groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #11 Gepost op: 16 december 2018, 09:46:22 »
Hoi BlackDevil,

Probeer me even te volgen in een héél onnozele (maar wel juiste) redenering.
Stel dat je 8 - 5 wil berekenen, maar dat is of te moeilijk, of zelfs onmogelijk.
Dus tel je bij beide getallen eerst een ander getal op, laten we zeggen 10.
En 18 - 15 kunnen we wel, dat wordt dan 3. Daaruit leiden we af dat 8 - 5 ook 3 moet zijn (ik had je verwittigd dat het onnozel ging zijn...)
Stel anderzijds dat je bij die 8 eerst 20 optelt, en bij die 5 slechts 10, dan krijg je gegarandeerd een verkeerde uitkomst.

Welnu, met die datums is dat exact hetzelfde. Wat jij eerst deed door de ene keer 600 jaar bij te tellen, de andere keer 700 jaar, enz., zal in vele gevallen correcte resultaten hebben gegeven, maar alleen als beide te vergelijken datums in dezelfde eeuw vallen !

Een andere conclusie die we hieruit kunnen trekken is dat je altijd ALLE datums met dezelfde waarde moet verhogen, dus OOK 'vandaag()' !

(voor de nog in leven zijnde personen) => leeftijd : ## jaar, ## maand en ## dag(en) op vandaag
(voor de overleden personen) => leeftijd : ## jaar, ## maand en ## dag(en) bij overlijden

Ook dit zou je kunnen bereiken met één enkele formule maar dat wordt dan een uitzonderlijk lange, die ook niet zomaar te kopiëren zou zijn, omdat de datums zich niet altijd op dezelfde positie ten opzichte van elkaar bevinden.

Dus misschien toch iets beters zoeken ? Vba of geen vba, that's the question.
Het 'gewoon' in vba proberen oplossen is in dit geval om problemen vragen. Wat we wel kunnen is zelf een functie schrijven, en dan bevinden we ons ook in vba-gebied.  De redenering die achter mijn formule zat zal in elk geval moeten aangehouden blijven, maar je zal zien : dat valt allemaal best mee.

Ik kom hier dus zeker later op terug (maar moest op dit ogenblik eigenlijk al de deur uit zijn  ??? , dus 't zal misschien niet meer voor vandaag zijn)

By the way, je hebt mogelijk geen idee wat voor een interessante vraag je hier hebt gesteld. Het is namelijk iets wat ik nooit nodig heb gehad, en me bijgevolg nooit afgevraagd of het sowieso kon, m.a.w. je hebt een leuk denkproces op gang gebracht.

Groetjes,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #12 Gepost op: 16 december 2018, 11:42:32 »
Hey pitufo,

Alvast bedank voor je hulp en geduld :-).
Blij dat ik een leuk denkproces op gang heb gebracht ;-).


In mijn oplossing, met de complexe formules, had ik dit zo opgelost :

Ik had (speciaal voor de berekening van de leeftijd op 'vandaag()') nog een extra hulpkolom(AT) gemaakt met volgende formule :

=ALS(H5<=1200;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+800;G5;F5));ALS(H5<=1300;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+700;G5;F5));ALS(H5<=1400;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+600;G5;F5));ALS(H5<=1500;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+500;G5;F5));ALS(H5<=1600;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+400;G5;F5));ALS(H5<=1700;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+300;G5;F5));ALS(H5<=1800;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+200;G5;F5));ALS(H5<=1900;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+100;G5;F5));ALS(H5>=1900;ALS(EN(H5="";G5="";F5="");"";DATUM(H5+100;G5;F5));ALS(EN(H5="";G5="";F5="");"";DATUM(H5;G5;F5)))))))))))
Dus eigenlijk dezelfde formule als bij de andere datums maar ditmaal ook voor datums nà 1900 een bijtelling van 100 jaar.

Vervolgens in de kolom waar het resultaat moest komen van de leeftijd op vandaag had ik dan volgende formule :

=ALS(AT5="";"";DATUMVERSCHIL(AT5;VANDAAG()+36525;"Y")&" jaar, "&DATUMVERSCHIL(AT5;VANDAAG()+36525;"YM")&" maand en "&DATUMVERSCHIL(AT5;VANDAAG()+36525;"MD")&" dagen")
De logische test en waarde als waar in deze formule staat erbij omdat ik soms de betreffende datum niet ken en anders kwam er telkens een foutmelding in de doelcel, dus om deze te vermijden heb ik er die logische test aan toegevoegd.


In de personenfiche komen dan ook de datums te staan waarnaar dan verwezen word in de formule in de cel waar de leeftijd 'op vandaag' of 'bij overlijden' komt te staan. D13 verwijst naar de geboortedatum en D17 verwijst naar de overlijdensdatum. Als er in D13 "--" vermeld staat is deze datum niet gekend en dan moet de cel leeg blijven, als er in D17 "--" vermeld staat is deze datum ongekend of de persoon nog in leven en dan moet er gezocht worden naar de waarde in de kolom waar de leeftijd 'op vandaag' vermeld staat. Is dat niet het geval dan staat er een datum vermeld in D17 en dan moet de waarde gezocht worden in de kolom waar de leeftijd 'bij overlijden' vermeld staat.

Is nogal ingewikkeld als ik het zo probeer uit te leggen ;-) en hoop dan ook dat jullie er een beetje wijs uit geraken :D .

Ik ga sowieso al mijn formules in de personenfiche ook drastisch moeten aanpassen aangezien deze allemaal opgesteld zijn volgens de drie kolommen per datum die ik had en ik dan nu de datum volledig in één kolom heb staan. Dat ga ik nu vandaag allemaal proberen uit te zoeken.
Tenminste, als ik mijn laptop en alle aangesloten apparatuur verhuisd krijg naar mijn zetel want ik zou eigenlijk mijn been verhoogd te rusten moeten leggen aangezien ik afgelopen vrijdag in mijn middenvoet 3 breuken heb opgelopen :-( ... Mijn denkvermogen is dan momenteel ook niet helemaal optimaal door alle pijnmedicatie die ik moet nemen :-).

Ik ga in ieder geval vandaag ook één en ander uitproberen om het probleempje met de berekeningen van de leeftijden op 'vandaag()' proberen op te lossen en hou jullie uiteraard op de hoogte.

Nogmaals mijn oprechte dank aan iedereen voor het meedenken en vooral voor het geduld ;-).

Vriendelijke groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #13 Gepost op: 16 december 2018, 12:03:22 »
Aan alle helpers,

Ik zie dat er lustig gebrainstormd wordt.
Maar ik wil toch nog even de nadruk leggen dat TS werkt aan een stamboek, en dat zo een opzoeking ver in de tijd terug kan gaan (als ze geluk heeft)

Om in zo een geval juiste berekeningsresultaten te verkrijgen moet er wel degelijk rekening worden gehouden met zaken als schrikkeljaren, welke kalender er gebruikt werd in bepaalde tijdsperioden (Gregoriaanse vs Juliaanse kalender) enzovoort.

Als voorbeeld voor de complexiteit: het jaar 2000 was normaal een schrikkeljaar, en zou normaal 1 dag extra krijgen.
Maar om de kalender tot in detail te corrigeren worden op jaren die normaal een schrikkeljaar zijn, maar die deelbaar zijn door 400, GEEN schrikkeljaar toegepast.
Schrikkeljaar?
2020 Ja
2016 Ja
2012 Ja
2008 Ja
2004 Ja
2000 Normaal ja, om de 4 jaar maar omdat 2000 deelbaar is door 400, NEE
1996 Ja
---------
1904 Ja
1900 NEEN
1896 Ja

Omdat TS veel gaat te maken krijgen met datums van vóór 1900 dient hier toch rekening mee gehouden te worden, niet?
Ook al zeker omdat ze de ouderdom (leeftijd) wilt uitdrukken in dd:mm:yyyy.
Ik weet niet of iemand van de helpers iets ziet in de Functie AgeFunc() die ik in een vorig bericht aangehaald heb.
Deze werkt (zonder aanpassingen) enkel om de jaren te tellen tussen 2 datums van voor of na 1900, corrigeert dus enkel het Windows Excel jaar 1900 probleem.
Maar mogelijk zien jullie specialisten hier een kans om deze om te werken tot een dagen/maanden/jaren - functie ???

Willen jullie toch met jaren tellen door er jaren bij te tellen, dan zou ik zeker aanraden om enkel 400 jaar (of een veelvoud ervan) te gebruiken, mogelijk blijven de verschillen (door schrikkeljaren die niet schrikkeljaar worden omdat ze deelbaar zijn door 400) dan beperkt.

Just my 2 cents ...

Succes,

:) SoftAid :)             




Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: Excel-beperking mbt datum pré 1900 wegwerken met VBA
« Reactie #14 Gepost op: 16 december 2018, 12:52:28 »
Hey SoftAid,

Bedankt voor je bemerking, hier dient zeker rekening mee gehouden te worden als ik echt correct tot op de dag de berekeningen wil maken, wat nu toch wel het geval is gezien ik, naast het gewone stamboomonderzoek, dit ook aan verwerken ben in boeken en dan dient het toch wel zo correct mogelijk te zijn.
Er is dan ook nog eens tussen 1792 en 1806 gebruik gemaakt geweest van de Frans Republikeinse kalender waar rekening mee zal moeten gehouden worden.
Ik wil me gerust wel eens bezighouden om voor elk jaar het aantal dagen in kaart te brengen aangezien ik momenteel toch vastzit met mijn gebroken voet en dus wel de tijd ervoor kan vrijmaken.


Update mbt het schrikkeljaar : ik heb eventjes uitgezocht hoe het nu ook alweer zat met het schrikkeljaar en ben in een notendop op het volgende uitgekomen => het schrikkeljaar werd ingevoerd in 46 v.C. door Julius Caesar. Zijn berekening was echter niet geheel correct waardoor er in de 16e eeuw plots een verschil ontstond van maar liefst 10 dagen. Daarom werd door Paus Paul Gregorius XIII in 1582 de Gregoriaanse kalender ingevoerd waarbij deze 10 dagen onmiddellijk werden weggewerkt. Men ging van 4 oktober 1582 plots naar 15 oktober 1582. Om te voorkomen dat er opnieuw zulks groot verschil zou ontstaan bepaalde de Paus dat eeuwjaren voortaan geen schrikkeljaar zouden zijn BEHALVE als ze deelbaar waren door 400. Zo was bijvoorbeeld 1900 GEEN schrikkeljaar maar 2000 WEL.

En het gaat inderdaad ver terug in de tijd, langs moederskant zit ik toch al tot in de 12e-13e eeuw....

Wat jouw Functie AgeFunc() betreft denk ik wel dat deze met de nodige aanpassingen een oplossing zou kunnen bieden voor het volledige probleem maar aangezien ik een complete leek ben op gebied van VBA begrijp ik er dus niks van :-) en kan/durf ik er zelf niet in gaan prutsen...

Dus indien er onder de specialisten hier iemand zit die de uitdaging wil aangaan om de nodige aanpassingen erin te maken zou ik ontzettend dankbaar zijn ;-).

Vriendelijke groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

 


www.combell.com