Datum uit bronbestand in verkeerde notatie

[sprezzatura]

All,

Op wekelijkse basis krijg ik een Excel-bestand aangeleverd, met in één van de kolommen een datum opgenomen. Het probleem is echter dat de data in deze kolom niet in het juiste format zijn opgeslagen. De datum wordt namelijk in een Amerikaans-format weergegeven, maar niet als zodanig herkend door Excel. Hierdoor haalt Excel de dagen en maanden door elkaar.

Voorbeeld

Datum	Wordt gezien door Excel als	Zou moeten zijn
05/06/2013	5 juni 2013	6 mei 2013
05/08/2013	5 augustus 2013	8 mei 2013

Hoe kan ik dit corrigeren? Welke Excel-wizzard kan mij hiermee uit de brand helpen?

(Een voorbeeldbestand is bijgevoegd als bijlage bij deze post)

 

[OctaFish]

Als ik je document open, zie ik de datums 5-6-2013 en 5-8-2013. Want zo is mijn datumnotatie ingesteld. Als je ervan overtuigd bent dat het eigenlijk altijd 6-5-2013 en 8-5-2013 zou moeten zijn (wat doet-ie overigens met 11/14/2013?) dan zou je een formule moeten maken die een nieuwe datum genereert op basis van de ingevoerde getallen. Heb je de datumnotatie al aangepast? (mm/dd/jjjj)

 

[sprezzatura]

Hi Michel,

Bedankt voor je reactie.

Iedere week krijg ik een dergelijk bestand doorgestuurd, waarin data wordt gerapporteerd over de voorgaande week. Hierdoor ben ik ervan overtuigd dat er daadwerkelijk 6 en 8 mei zou moeten staan en niet 5 juni en augustus - deze data liggen immers veel verder uit elkaar dan één week.

Ik kan me voorstellen dat het initiële door mij bijgevoegde bestand niet helemaal duidelijk is door het kleine aantal records. Ik heb daarom een kopie gemaakt van het oorspronkelijke bestand en de datum-kolom onbewerkt gelaten. Dit bestand is in week 20 naar mij gestuurd en zou betrekking moeten hebben op week 19. Alle data zouden dus ook in week 19 moeten liggen.

Ik was al in de veronderstelling dat ik dit alleen m.b.v. een formule kon oplossen, inderdaad. Ik loop echter vast bij het opstellen van de formule. Zou jij, of iemand anders, mij hiermee op weg kunnen helpen?

Alvast bedankt.

 

[sylvester-ponte]

=als(dag(e2)<=12;datum(jaar(e2);dag(e2);maand(e2));e2)

 

[popipipo]

Krijg je dit als excel bestand door gestuurd dan moet excel er zelf het juiste formaat van maken.
Dit is kennelijk niet zo, daarom denk ik dat je het als csv bestand krijgt.
Bij het importeren van csv bestand kun je er het juiste datum formaat van maken.

 

[sprezzatura]

@Sylvester Ponten: je bent een held. Het werkt. Bedankt!

@Willem: helaas, het bestand wordt bij mij niet als csv, maar als een xls-bestand aangeleverd. Er gaat duidelijk dus iets fout aan de kant van de leverancier bij het opslaan, maar met de door Sylvester aangedragen oplossing, is dit bij dezen prima opgelost.

 

[OctaFish]

Ik snap niet helemaal dat je met de formule van sylvester uit de voeten kan, want die gaat (in ieder geval in je voorbeeldbestandje) de teil in zodra je één van de datums verandert in (wat dus een Amerikaans correcte datum is) 05/18/2013. Dan krijg je accuut een foutmelding, en dat is ook logisch natuurlijk. Het probleem met Amerikaanse notatie is nu juist dat datums als 05/18/2013 niet worden herkend (18 kan nooit een maand zijn) en 05/06/2013 juist wel, maar dan verkeerd om: dag en maand worden verwisseld.
Volgens mij moet je dus altijd de datumelementen omwisselen, en niet alleen als Dag(E2) <12. Probeer deze formule eens, die de datum als tekst beoordeelt, en op basis daarvan maand en dag omwisselt.
Wellicht dat je het - teken moet vervangen door de slash (/).

=DATUM(DEEL(TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)+1)+1;LENGTE(TEKST(E2;"dd-mm-jjjj"))
-VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)+1));LINKS(TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)-1);
DEEL(TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)+1;VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)+1)
-VIND.SPEC("-";TEKST(E2;"dd-mm-jjjj");1)-1))

 

[OctaFish]

Ik heb het bestandje er ook maar even bijgedaan...

 

[sylvester-ponte]

Sub test()
    a1 = "5/6/2013"
    a2 = "6/13/2013"
    b1 = CDate(a1)
    b2 = CDate(a2)
End Sub

Michel, vaak wordt data via tekst aan excel gevoerd.
als je bovenstaand bestandje test , dan zie je dat tekst a1 en tekst a2 verschillend door excel worden geïnterpreteerd.

kijk maar naar het resultaat: in b1 en b2 is de maand 6 geworden
van daar die voorwaarde <= 12