[Excel/Access 2007] Datumnotatie niet aan te passen

[Bastiaan]

In onze productieomgeving hebben we een (weeg-)machine staan welke registraties wegschrijft in een txt-file. Hieraan is ook een datum en tijd gekoppeld. Deze info moet in Excel worden geïmporteerd, verwerkt en eventueel doorgesluisd naar Access.

Het probleem is alleen dat metingen als platte waarden wordt weggeschreven; de datum van vandaag is 40509, de tijd 1300. De tijd-waarde heb ik inmiddels kunnen verwerken tot 'echte' tijd, middels een Excelsheet icm verticaal zoeken. De datum is helaas een ander verhaal. Ik kan de nummers wel splitsen (ik krijg 4, 05 en 09 gesplitst), maar ik kan er geen soort waarde aan koppelen. Ik kan aangeven dat Excel het veld met '05' moet zien als een maand ('mm'), maar het wordt niet als dusdanig verwerkt. Beetje vervelend dus, aangezien dit het rekenen met tijdspannen niet mogelijk maakt.

Deze notatie of manier van wegschrijven is overigens niet in de betreffende weegmachine aan te passen. Is er iemand die voor Excel een workaround/oplossing weet? Ik ga ervan uit dat als dat opgelost is, het ook probleemloos te importeren in Access is.

[Witte]

=datum(jaar;maand;dag)

[Bastiaan]

Helaas niet. Even los van dat de formule uitgebreider zou moeten ("LINKS" er bij in verwerken, zodat je een bepaald aantal cijfers in de waarde kunt toewijzen aan jaar, maand en dag) heb ik dit geprobeerd. Toch bedankt

[pedorus]

Bastiaan schreef op maandag 04 mei 2009 @ 13:20:
Helaas niet. Even los van dat de formule uitgebreider zou moeten ("LINKS" er bij in verwerken, zodat je een bepaald aantal cijfers in de waarde kunt toewijzen aan jaar, maand en dag) heb ik dit geprobeerd. Toch bedankt

Nou ja, mij lijkt het toch een werkende oplossing
Dus iets als:

code:

=DATUM(RECHTS(A1;2)+2000;DEEL(A1;LENGTE(A1)-3;2);LINKS(A1;LENGTE(A1)-4))

[Anoniem: 147180]

Er even vanuitgaande dat je datumopmaak altijd de syntaxis DMMJJ heeft (en waarbij de dag dus uit één of twee cijfers kan bestaan, maar maand- en jaargetal altijd uit twee cijfers bestaan), zou je de volgende formule kunnen gebruiken:

code:

=WAARDE(LINKS(A1;LENGTE(A1)-4)&"-"&DEEL(A1;LENGTE(A1)-3;2)&"-"&DEEL(A1;LENGTE(A1)-1;2))

Ongetwijfeld valt er nog iets korters te bedenken, maar dit werkt in ieder geval wel.

[ Voor 11% gewijzigd door Anoniem: 147180 op 04-05-2009 21:22 . Reden: Kortere formule ;-) ]

[Bastiaan]

Anoniem: 147180 schreef op maandag 04 mei 2009 @ 21:17:
Er even vanuitgaande dat je datumopmaak altijd de syntaxis DMMJJ heeft (en waarbij de dag dus uit één of twee cijfers kan bestaan, maar maand- en jaargetal altijd uit twee cijfers bestaan)...

Yup, dat is inderdaad zo.

Anoniem: 147180 schreef op maandag 04 mei 2009 @ 21:17:

code:

=WAARDE(LINKS(A1;LENGTE(A1)-4)&"-"&DEEL(A1;LENGTE(A1)-3;2)&"-"&DEEL(A1;LENGTE(A1)-1;2))

Dit is inderdaad de formule welke werkt.
Ik ben - net als Pedorus - ook rechts begonnen, juist om de 1- of 2-cijferige dag te 'omzeilen' (laatste 2 zijn jaar, volgende 2 zijn maand, de rest is dag). Links ben ik ook mee bezig geweest maar kwam ik ook niet uit, dus.

Bedankt voor de hulp! Ook Access slikt het nu.

[pedorus]

Bastiaan schreef op dinsdag 05 mei 2009 @ 10:08:
Dit is inderdaad de formule welke werkt.

Op een platform met Nederlandse datuminstellingen... Ik zou daarom toch liever DATUM() gebruiken dan WAARDE()

Ik ben - net als Pedorus - ook rechts begonnen, juist om de 1- of 2-cijferige dag te 'omzeilen' (laatste 2 zijn jaar, volgende 2 zijn maand, de rest is dag). Links ben ik ook mee bezig geweest maar kwam ik ook niet uit, dus

Of je nou DEEL(A1;LENGTE(A1)-1;2) of RECHTS(A1;2) gebruikt zal echt niks uitmaken, het laatste is korter. Ok, afgezien van de foutmelding die je bij de eerste krijgt als de lengte van A1 minder dan 2 is. Voor de rest zie ik geen verschil.

[Anoniem: 147180]

^^ Dat van de platformafhankelijkheid klopt. Nadeel van jouw formule is, dat deze alleen werkt voor 21e-eeuwse datums, en we weten niet of de TS nog met gegevens uit de 20e eeuw moet werken. In dat geval is het vrij lastig als "99" wordt vertaald naar 2099 ipv 1999.

Je kunt natuurlijk ook het beste van twee werelden combineren, maar ik moet er wel bijzeggen dat ik de eruit voortkomende formule wel heel erg vind getuigen van 'het probleem aan de achterkant dichttimmeren'. Maar goed, als het aan de voorkant niet lukt...

code:

=DATUM(JAAR(WAARDE("1-1-"&DEEL(A1;LENGTE(A1)-1;2)));DEEL(A1;LENGTE(A1)-3;2);LINKS(A1;LENGTE(A1)-4))

Het moge voor zich spreken dat deze formule ook niet gaat werken op platforms met een exotische notatie waarbij het jaartal vooraan staat. Dan zul je toch echt met VBA moeten gaan achterhalen wat de systeemnotatie is, en daar je formule op aanpassen.

[pedorus]

Nou ja, in dat geval kun je denk ik beter zelf een grens gaan trekken. Bijvoorbeeld voor 1970-2069:

code:

=DATUM(RECHTS(A1;2)+ALS(RECHTS(A1;2)*1<70;100);
       DEEL(A1;LENGTE(A1)-3;2);LINKS(A1;LENGTE(A1)-4))

[Anoniem: 147180]

Er leiden vele wegen naar Rome een voor Excel leesbare datum

[Anoniem: 113297]

pedorus schreef op dinsdag 05 mei 2009 @ 14:21:
Nou ja, in dat geval kun je denk ik beter zelf een grens gaan trekken. Bijvoorbeeld voor 1970-2069:

code:

=DATUM(RECHTS(A1;2)+ALS(RECHTS(A1;2)*1<70;100);
       DEEL(A1;LENGTE(A1)-3;2);LINKS(A1;LENGTE(A1)-4))

hoeft niet, dit kan je beter laten afhandelen door excel, zodat de datumschijf overeenstemt met de instellingen van windows zelf :

code:

=TEKST("05/05/99";"DDMMJJJJ")

(de vaste datum in dit voorbeeld dient natuurlijk vervangen door een van de omzettingsformules uit deze draad).

[pedorus]

Anoniem: 113297 schreef op dinsdag 05 mei 2009 @ 16:02:
[...]
hoeft niet, dit kan je beter laten afhandelen door excel, zodat de datumschijf overeenstemt met de instellingen van windows zelf :

code:

=TEKST("05/05/99";"DDMMJJJJ")

(de vaste datum in dit voorbeeld dient natuurlijk vervangen door een van de omzettingsformules uit deze draad).

Ik snap deze post niet. Het lijkt me dat je toch beter de interpretatie van de brondata onafhankelijk van de systeeminstellingen kan doen? En de formattering kun je gewoon bij de celinstellingen doen als het daar over gaat.

Daarnaast werk ik hier met een Engelstalige Excel, en vertaalt de TEKST functie erg slecht naar internationale versies. In het Engels had het "ddmmyyyy" moeten zijn, en dit wordt niet automatisch vertaald zoals de functienamen. Ik zou het gebruik van die functie dan ook niet aanraden, behalve als je heel zeker weet dat het maar met 1 taal gebruikt gaat worden.

[Bastiaan]

Het betreft alleen data welke vanaf 1 april verzameld word/is. De 21ste eeuw dus

Overigens is de formule nu geworden (houd je vast):

code:

=ALS('RUWE_DATA'!$H$2="";"";LINKS('RUWE_DATA'!$H$2;LENGTE('RUWE_DATA'!$H$2)-4)&"-"&DEEL('RUWE_DATA'!$H$2;LENGTE('RUWE_DATA'!$H$2)-3;2)&"-"&DEEL('RUWE_DATA'!$H$2;LENGTE('RUWE_DATA'!$H$2)-1;2))

pedorus schreef op dinsdag 05 mei 2009 @ 16:32:
[...]
Het lijkt me dat je toch beter de interpretatie van de brondata onafhankelijk van de systeeminstellingen kan doen?

Wat bedoel je met onafhankelijk van de systeeminstellingen? Is er dan een andere manier om te zeggen (los van de formattering van je cel) hoe welke cijfers geïnterpreteerd dienen te worden?
Om het afhankelijk te laten van je systeeminstellingen ben ik sowieso geen voorstander van. Op het moment dat de sheets op andere systemen ook gebruikt worden loop je mijn inziens een groot risico op foutjes.

[pedorus]

Bastiaan schreef op woensdag 06 mei 2009 @ 08:58:
Wat bedoel je met onafhankelijk van de systeeminstellingen? Is er dan een andere manier om te zeggen (los van de formattering van je cel) hoe welke cijfers geïnterpreteerd dienen te worden?

Nou, stel dat je je spreadsheet nu naar mij zou opsturen, en ook nog wat rekent met de datums, dan is het resultaat bij mij anders. Je maakt nu van "40509" "4-05-09". Dat is bij mij op dit moment (VS datums) 5 april ipv 4 mei... Als je DATUM() zou gebruiken, dan werkt het wel goed als je verder rekent. Afhankelijk van de formattering van de cellen zie ik het dan ook goed op het scherm. TEKST() is een functie die bij mij ook andere resultaten zal geven, omdat de formattering niet goed vertaald wordt.

[Anoniem: 147180]

Nu is het natuurlijk wel zo dat als de TS de enige is die van het betreffende Excelbestand gebruik maakt, formules die ook nog eens rekening houden met internationale instellingen overbodig zijn. Een leuke exercitie voor de Excellers - dat wel

[Bastiaan]

Ik ben niet zozeer de enige, maar binnen het bedrijf zijn geen verschillende versies van Excel in omloop.

Pedorus; ah, I see, I see!