Ik ben bezig een bestaande transcriptie van een doopboek te bewerken zodat ik gezinsreconstructies kan maken. Ik gebruik hiervoor Microsoft Excel. Het vervelende hierbij is echter, dat Excel data van vóór 1904 niet herkent als datumveld. Sorteren op datum werkt dan niet. Een oplossing kan zijn om het datumveld te splitsen in drie getalvelden: jjjj - mm - dd of daar één getalveld van te maken: jjjjmmdd. Dit zal ik dan vast handmatig moeten doen, want de data staan nu vermeld als tekst: 3 april 1687 - 14 april 1689 - [?] april 1689, etc. Het spreadsheet telt zo'n 1400 regels (dopelingen) en ik heb nog meer transcripties die ik wil bewerken tot gezinsreconstructies.

Heeft iemand een suggestie hoe dit beter kan?

Ik begrijp niet precies wat u bedoelt maar mijn eerste ingeving is de velden te selecteren en dan via rechtermuisknop > celeigenschappen de gewenste eigenschap in te voeren (datum, standaard, getal, tekst etc. )

mvg,

Hans

@Hans,

Excel datumvelden werken niet met datums van vóór 1904. Probeer het maar eens. Het wordt gewoon een tekstveld. Daar kan je dus niet mee sorteren.

inderdaad, een beperking van Excel (en andere spreadsheets)

workaround:

alle datum opnemen als:

jjjjmmdd

dus eigenlijk als een oplopend getal

 1/1/1700 = 17000101

 31/12/1900 = 19001231

 16/2/2011 = 20110216

even wennen, daarna "weet je niet beter"

grtn

Jan

@Jan

Ik moet dan 1400 "tekstvelden" handmatig converteren naar een getal jjjjmmdd?

Ik zag op een aantal Excel-fora op het internet een verwijzing naar de Excel invoegtoepassing XDATE van J-Walk ( zie http://www.j-walk.com/ss/excel/usertips/tip028.htm

Heeft iemand daar ervaring mee? Met name hoe ik die 1400 velden kan converteren naar soorteerbare velden?

Frank

deze extends heb ik geen ervaring mee

stuur me even een prive berichtje

ik kan je een voorbeeld-excelbestand sturen, om te laten zien hoe ik het zou oplossen

grtn

Jan

Frank

als truc om alles handmatig te veranderen te omzeilen, zou je een hulpkolom kunnen aanmaken waarin je de werkelijke datum ophoogt met 300 jaar (of wat dan ook, als het maar later dan 1904 wordt). Dan kun je vervolgens op die kolom sorteren. Maar als je meeste ingevoerde data vóór 1900 liggen, is ook dat handmatig nogal veel. Dus zou je het misschien kunnen doen met enkele ingebouwde Excel-functies.

Ik heb wat zitten proberen en het is denk ik wel gelukt (alleen natuurlijk niet met een datum waar [?] instaat), maar er zitten wat stappen in die het een beetje omslachtig maken om het hier uit te leggen.

Mocht het met Jan nog niet opgelost zijn, kun je altijd nog een privéberichtje sturen.

groeten, Frans

voordat we de indruk wekken dathet allemaal heel geheimzinnig is

;-)

ik voeg een kolom in naast de datum-kolom

in die kolom zet ik een formule die de waarde in de oorspronkelijke datum kolom vertaald naar een sorteerbare waarde

de formule in dit voorbeeld (waarbij de oorspronkelijk datum in kolom A staat):

=ALS(ISGEENTEKST(A3);TEKST.SAMENVOEGEN((JAAR(A3));RECHTS((TEKST.SAMENVOEGEN("0";MAAND(A3)));2);RECHTS((TEKST.SAMENVOEGEN("0";MAAND(A3)));2));TEKST.SAMENVOEGEN(RECHTS(A3;4);DEEL(A3;4;2);LINKS(A3;2)))

dit werkt voor de formaten:

01-01-1750 (historisch, dus "tekst")

1-1-2011 (hedendaags, dus "datum")

1-apr-11 (hedendaags, dus "datum")

dit werkt NIET voor de formaten:

1-4-1789 (dus historisch zonder 0)

1 april 1789 (dus historisch met letters)

deze kan ik ook wel oplossen, maar dat is ietsje ingewikkelder dan de formule hierboven

grtn

Jan 

afbeelding: http://keevel.50webs.com/HistDatum.jpg

excel-sheet: http://keevel.50webs.com/HistDatum.xls

Interessant! Ik had precies hetzelfde probleem. Nu weet ik dus waarom ik zo zat te hannessen en te prutsen met dat bestand. 't Was Excel! Ik was er nog niet eerder achter dat Excel niets met een datum van voor 1904 iets kan doen.

Als het werkt met 17500101, werkt het dan ook niet met 1750-01-01? En dan hou je dat aan als sorteerbare datum. Je vertelt Excel gewoon niet dat het een datum is...

Grappig genoeg sla ik standaard mijn bestanden op op als 2010-01-01 nogwat en 2011-02-16 watanders, juist omdat het anders nogal moeilijk zoeken is. Zeker met foto's!

Beste Jan en anderen,

Hartelijk dank voor jullie inbreng. Ik zal de formules gebruiken en waar mogelijk aanvullen, ook al ben ik geen doorgewinterde Excel programmeur. Het resultaat koppel ik graag aan jullie terug, al zal ik er wel een poosje mee bezig zijn.

1-4-1789 (dus historisch zonder 0) kan worden opgelost door door gebruik te maken van de VIND-functie, zoek 1e koppelteken en zoek 2e koppelteken. Het jaar is altijd de laatste 4 karakters. De tekens die tussen de 2 koppeltekens staan, is de maand in 1 cijfer of 2 cijfers. De dag bestaat uit 1 of 2 cijfers en staan altijd voor het 1e koppelteken. Door de VIND-functie te laten starten op de 2e positie en van dat karakter te vragen of het een koppelteken is krijg je de waarde TRUE of FALSE. Als het TRUE is, dan bestaat de dag uit 1 karakter en als het FALSE is, uit 2 karakters.

1 april 1789 (dus historisch met letters) kan worden behandeld met een soortgelijke zoekfunctie als hierboven en de gevonden maand te vervangen door een 2-cijferige notatie. In plaats van de hierbovengenoemde koppelteken, moet je hier zoeken op het karakter SPATIE.

De XDATE-toepassing is overigens ook een aardige toevoeging hierop om het resultaat van de hiervovengenoemde conversie in XDATE-format op te slaan. Dan blijft de datum als datum zichtbaar en je kunt er ook mee sorteren en eventueel rekenen. 

Het wordt nog spannender als je de overgang van de Juliaanse kalender naar de Gregoriaanse kalender vanaf 4 oktober 1582 wilt meenemen (zie http://nl.wikipedia.org/wiki/Gregoriaanse_kalender). In veel protestantse gebieden werd de nieuwe kalender pas rond 1700 aanvaard. In Nederland aanvaardden Holland en Zeeland en de zuidelijke gewesten vrijwel onmiddellijk de nieuwe kalender maar de overige gewesten deden dit pas in 1700 of in 1701. Gevolg was bijvoorbeeld dat de tornado die de Utrechtse Domkerk op 1 augustus 1674 in twee stukken splitste, in geschriften die de oude kalenderstijl hanteerden, gedateerd is op 22 juli!

Groeten van Frank

@ frans, mijn excuus. Nu ik de verdere reacties lees begrijp ik hoe mijn reactie overkomt

 

Geeft niet, Hank.

Wil je meer weten over het ontstaan van dit vraagstuk, kijk dan eens op http://www.exceluser.com/explore/earlydates.htm. Voor genealogen die met Excel werken een artikel om rekening mee te houden. Over het ontstaan van het spreadsheet kan je alles lezen op de site van Dan Bricklin, de geestelijk vader van het eerste spreadsheetprogramma VisiCalc op http://www.bricklin.com/visicalc.htm

Groeten van Frank

op een bepaald moment wordt het in excel lastig om de formule steeds complexer te maken

handiger is het dan om een of meer hulpkolommen te introduceren (evt. te verbergen) 

Ik heb net even een mini-trial gedaan en de data invoeren als 1731-05-29 en dan numeriek sorteren gaat prima!

Als je het zo gebruikt zijn de gegeven data voor iedereen te begrijpen zonder er al te grote gedachtesprongen voor hoeven te maken, lijkt me.

idd

ik zet al jaren al mijn gegevens in dat formaat weg  (beroepsdeformatie)

igv Frank's gegevens gaat om een groot aantal records dat al in een ander formaat is vastgelegd

en dus enige conversie behoeft

grtn

Jan

Met wat knip- en plakwerk van het internet, gevolgd door enige correcties, is de volgende formule tot stand gekomen.

=ALS(ISLEEG(B2);"";ALS(ISGETAL(B2);JAAR(B2)*10000+MAAND(B2)*100+DAG(B2);WAARDE(RECHTS(B2;4)&TEKST(DEEL(B2;VIND.SPEC("-";B2)+1; VIND.SPEC("-";B2;VIND.SPEC("-";B2)+1)-VIND.SPEC("-";B2)-1);"00")&TEKST(DEEL(B2;1;VIND.SPEC("-";B2)-1);"00"))))

Deze formule WERKT voor de volgende formaten:

01-01-1750 (historisch, dus "tekst")

1-4-1789 (historisch, zonder 0)

1-1-2011 (hedendaags, dus "datum")

1-apr-11 (hedendaags, dus "datum")

dit werkt NIET voor de formaten:

1 april 1789 (historisch, met letters).

Deze laatste notatie zul je in een 1e hulpkolom naast de oorspronkelijke geboorte- of doopdatum kolom moeten vertalen met twaalf keer de ZOEK/VERVANG-functie. Voer geen vervangfuncties in de oorspronkelijke geboorte- of doopdatum kolom. Die moet je houden zoals die is. Je moet er sowieso handmatig doorheen om datumomschrijvingen, zoals ?In festo Inventionis Ste Crucis 1683? te vertalen naar de cijfermatige datum 06-03-1683 of doopinschrijvingen zonder datumvermelding. Je weet dan vaak wel het jaar en met geluk de maand, maar de dag niet. In de hulpkolom vertaal je die met 00-mm-jjjj.

In een 2e hulpkolom plaats je de formule, in dit geval in veld B3. Vervolgens kopieer dit veld en plak het in de velden eronder, zo vaak als nodig is. Op kolom B3 kan je nu goed sorteren.

Er worden 3 dingen gedaan in kolom 3:

ISLEEG(B2);"": als B2 leeg is geen waarde.

ISGETAL(B2);JAAR(B2)*10000+MAAND(B2)*100+DAG(B2): als B2 een getal is (een datum na 1-1-1900) dan wordt de datum omgezet naar een getal, jaar voorop dan maand dan dag
En de moeilijkste:

WAARDE(RECHTS(B2;4)&TEKST(DEEL(B2;VIND.SPEC("-";B2)+1; VIND.SPEC("-";B2;VIND.SPEC("-";B2)+1)-VIND.SPEC("-";B2)-1);"00")&TEKST(DEEL(B2;1;VIND.SPEC("-";B2)-1);"00"))

De tekst met daarin de geboortedatum moet omgezet worden naar een getal zoals bij de "echte" geboortedata (jaar-maand-dag).

Het jaar is vrij gemakkelijk dat zijn de laatse 4 cijfers uit de tekst (RECHTS(B2;4)).

De dag is ook eenvoudig. Dat is namelijk het gedeelte van de tekst vóór het eerste "-". (TEKST(DEEL(B2;1;VIND.SPEC("-";B2)-1);"00")) TEKST(....;"00") wordt toegevoegd omdat ander 4-1-1750 getransformeerd wordt tot 175014 een getal dat veel kleiner is dan 15-12-1623.

De maand is het lastigst:
TEKST(DEEL(B2;VIND.SPEC("-";B2)+1; VIND.SPEC("-";B2;VIND.SPEC("-";B2)+1)-VIND.SPEC("-";B2)-1);"00")
Met 4 x VIND.SPEC wordt het onoverzichtelijk. We zoeken de tekst tussen de 2 "-". Hiervoor gebruiken we de functie DEEL die een gedeelte van de tekst zoekt.

De eerste "-" vinden is geen probleem: VIND.SPEC(B2;"-");

De tweede "-" vinden we door te zoeken vanaf (1 karakter na) de eerste "-":

(VIND.SPEC("-";B2;VIND.SPEC("-";B2)+1)).

We hebben nu de positie uit de tekst en daar moet de positie van de eerste "-" nog van af. Ook hier maken we van de maanden een getal van 2 cijfers.

Zo, en nu ga ik gezinsreconstructies maken. Was er in dit topic niet iemand die schreef dat je dat in Excel mooi met filters en zo kan doen? Ik hou mij van harte aanbevolen, maar dan wel in een ander topic.

Groeten van een nawinterse spreadsheet dummy, Frank Bakkum

:-)

er zijn idd meerdere wegen die naar Rome leiden

overigens is "vertalen met twaalf keer de ZOEK/VERVANG-functie" niet nodig

je kunt ergens op het vel, of op een vervolgtabblad een tabel defini:eren met de 12 maanden en hun maandnummer, 

en vervolgens de in-het-bronveld-gevonden-waarde met 1 "vind" (VERT.ZOEKEN) in de tabel zoeken om het maandnummer te vinden

voorbeeldje heb ik nog wel ergens, zal ik opzoeken voor de liefhebbers

filteren is net zo heel moeilijk

kijk even onder "auto filter" - dit kin je voor een kolom doen of voor alle

 grtn

Jan