De essentie van de miljoenen datum topics die je kan vinden op het web ligt hem in het feit dat mensen het verschil niet weten tussen een datum waarde en een "tekst-met-datum-achtige-notatie" waarde. Ook helpers missen vaak genoeg de boot in dit verhaal. Simpelweg vanwege het feit dat de altijd lokaal op het systeem van de gebruiker worden bepaald, en dus niet exact te definiëren zijn.
Volg altijd dit 2-stappenplan:
Stap 1. is de waarde tekst of datum?
Is de waarde een datum?
ga naar stap 2
Is de waarde tekst?
Je zult de waarde moeten omzetten naar een datum, via een conversie functie.
Hier ligt uiteraard de "uitdaging"
De moeilijkheid hierin is de lokale systeeminstellingen voor datum. Zowel voor het inlezen van het systeem van jouw input, als het interpreteren van jouw formules van de output van (Excel)functies. Andere talen hanteren andere maandnamen, de volgorde dag-maand-jaar kan verschillen in de notatie, en zelfs het datum scheidingsteken varieert.
Wat je het beste kan doen is voor "datums als tekst" een paar formules maken die uit de geleverde tekst de waarden jaar, maand en dag halen, en deze via de functie DATUM() samenvoegen tot een datum waarde.
Om het gegeven voorbeeld ter hand te nemen:
De datum notatie is dd-mmm-jj, en de datum staat in cel A1.
Als dit consistent wordt gehanteerd zijn de twee linker tekens altijd de dag, en te bepalen met de formule
LINKS(A1;2)
En voila, we hebben de dag.
Nu zijn teken 4 tot en met 6 de maand. maar de maand is in korte tekstnotatie, oftewel "jan". Deze notatie is systeem afhankelijk. We kunnen dit eenvoudig oplossen als jouw systeem in dezelfde taal staat als het bronsysteem dat de informatie levert, maar anders moet je zoektabellen gaan gebruiken, wat het een en ander weer complexer maakt
We gaan nu natuurlijk van het eenvoudige scenario uit, en staan nu voor het probleem om de tekst "mmm" (bijvoorbeeld: "jan") om te zetten in het maandnummer(bijvoorbeeld: 1).
Op mijn systeem (en volgens Excel help "algemene excel datum notatie"(ik geloof het niet,maar het staat er wel)) is dit te verkrijgen door de maand van de tekst "dag maand jaar" te bepalen. MAAND("1 jan 2010") zou als op basis van het voorbeeld resultaat 1 moeten geven
Om die waarde te bepalen nemen we een fictieve dag, 1, en een fictief jaar, 2010 en plakken deze samen met een maand samen tot de "algemene Excel datumnotatie"(kuch)
"jan" isoleren:
MIDDEN(A1;4;3) ___________voorbeeld resultaat: "jan"
Samenvoegen met fictieve dag en jaar om een "dummy" datumtekst te maken:
TEKST.SAMENVOEGEN("1 ";MIDDEN(A1;4;3);" 2010") ___________voorbeeld resultaat: "1 jan 2010"
en vervolgens het maandnummer bepalen:
MAAND(TEKST.SAMENVOEGEN("1 ";MIDDEN(A1;4;3);" 2010"))___________voorbeeld resultaat: 1
Het maandnummer is nu bekend.
Nu rest ons nog het jaar te bepalen, het jaar is volgens het opgegeven tekstformaat de laatste 2 tekens, dus het jaar is eenvoudig te bepalen door de twee rechter tekens van de tekst te nemen(en daar 2000 bij op te tellen.)
RECHTS(A1;2) + 2000 het jaar is (2012)
Nu we het jaar, de maand en de dag hebben bepaald, kunnen we de datum bepalen door
=DATUM(jaar,maand,dag)
ofwel:
=DATUM(RECHTS(A1;2)+2000;MAAND(TEKST.SAMENVOEGEN("1 ";MIDDEN(A1;4;3);" 2010"));LINKS(A1;2))
om af te vangen dat je niet een waarde die al een datum is gaat omzetten, kun je nog een als formule gebruiken om jezelf te verzekeren dat de waarde een tekst is, voordat je de waarde naar datum converteert (de uitkomst van de formule is dan niet juist)
=ALS(ISTEKST(A1);[datumformule];A1)
ofwel
=ALS(ISTEKST(A1);DATUM(RECHTS(A1;2)+2000;MAAND(TEKST.SAMENVOEGEN("1 ";MIDDEN(A1;4;3);" 2010"));LINKS(A1;2));A1)
nu heb je een datum, deze datum kun je vervolgens via stap 2 naar de door jouw gewenste notatie wijzigen.
de hierboven beschreven methode is een voorbeeld en hoeft niet voor elk systeem te gelden, maar hopelijk biedt het een beetje inzicht.
Stap 2. is de waarde een datum?
Pas de datumnotatie aan met de celeigenschappen, gebruik de methode zoals omschreven door EA in post #2
Let op: de datum WAARDE blijft onveranderd, wat je wijzigt is de WEERGAVE
probeer het verschil te zien tussen een tekst en een datum.
succes,
Mark
