[Excel 2007] Datumveld of geen datumveld

[smeerbartje]

Ik heb in Excel 2007 een veld dat niet wordt gezien als een correcte datum. Het betreft een dump uit de database die ikzelf niet aan kan passen en het ziet er als volgt uit:

Wed Feb 27 20:45:32 UTC+0100 2008

Graag zou ik deze willen veranderen naar "YYYYMMDDHHMMSS", maar zoals gezegd kan ik niet bij de query die de dump genereert. Wel heb ik het Excel sheetje, maar Excel ziet bovenstaande niet als datum. Wat doe ik verkeerd en weet iemand hoe ik dit op kan lossen? Het veld kopieren naar een ander datum-veld werkt niet.

[Witte]

Onderstaand VBA-functietje does the trick:

code:

Function myDate(myText As String) As Date
  Dim tempstr
  tempstr = Split(myText, " ")
  myDate = CDate(tempstr(2) & " " & tempstr(1) & " " & tempstr(5) & " " & tempstr(3))
End Function

Als je dit in een module plaats, kan je in je werkmap de functie myDate gebruiken, zoals: =myDate(A1)

vervolgens pas je in celeigenschappen de datumnotatie aan naar jouw wensen.

[ Voor 11% gewijzigd door Witte op 15-07-2008 16:55 ]

[smeerbartje]

Wow, thanks man! Maar uuh...een klein probleem. De query levert niet altijd een dag aan in twee digits. M.a.w. bij:

Sat Mar 1 17:26:22 UTC+0100 2008

gaat het fout, aangezien de functie verwacht dat de dag bestaat uit twee digits. Enig idee hoe op te lossen op een generieke manier?

[Witte]

Niet waar. De functie 'breekt' de tekst in stukken op basis van de spaties, en niet op basis van digits.
(uiteraard heb ik daar aan gedacht )

[Verwijderd]

Het minder handige hieraan (VBA) lijkt me dat je expliciet een commando moet geven om te vertalen.

Je kan het toch gewoon met MIN en & doen?

=mid(..,..,..)&mid(..,..,..)&..

Het lijkt er namelijk op dat je data op een vaste regel staat.

[Witte]

Tja, in de oplossing met mid etc. heb je ipv 1 functie er een stuk of 4 nodig. kies maar uit welke handiger is. Bovendien, met mid heb je wel degelijk last van dagen met 1 of 2 digits.
de VBA-funcitie split lijkt hiervoor geknipt. Standaard biedt Excel dit niet.

doe er je ding mee:
- prutsen met mid, left en right en hopen dat het goed komt
- een elegante functie in VBA

[smeerbartje]

Ikzelf prefereer ook de VBA oplossing. Echter deze werkt niet goed in de volgende gevallen.

code:

Sat May 24 14:11:06 UTC+0200 2008
Sun May 25 09:20:12 UTC+0200 2008
Sun May 25 09:20:30 UTC+0200 2008
Sun May 25 09:21:15 UTC+0200 2008
Thu May 29 17:01:52 UTC+0200 2008

In de volgende gevallen weer wel; zien jullie verschil?

code:

Tue Jun 3 12:18:15 UTC+0200 2008
Wed Jun 4 08:16:27 UTC+0200 2008
Fri Jun 13 13:55:31 UTC+0200 2008
Wed Jun 18 13:17:11 UTC+0200 2008
Fri Jun 20 12:25:13 UTC+0200 2008

Eerst dacht ik dat het te maken heeft met 2 of 1 digit als dag, maar dit blijkt niet het geval te zijn. Zie ik iets over het hoofd? Witte, help!

[Witte]

Ja, hij weet niet dat may engels is voor mei.
Je zal dat eerst moeten vervangen (in de VBA-functie het liefst), zelfde voor okt / oct en maa / mar

code:

NL   ENG
jan  jan
feb  feb
maa  mar  <= verschil
apr  apr
mei  may  <= verschil
jun  jun
jul  jul
aug  aug
sep  sep
okt  oct  <= verschil
nov  nov
dec  dec

Je zou het kunnen doen door Replace te gebruiken:

code:

Function myDate(myText As String) As Date
  Dim tempstr
  myText = LCase(myText)
  myText = Replace(myText, "may", "mei")
  myText = Replace(myText, "mar", "maa")
  myText = Replace(myText, "oct", "okt")
  tempstr = Split(myText, " ")
  myDate = CDate(tempstr(2) & " " & tempstr(1) & " " & tempstr(5) & " " & tempstr(3))
End Function

Je zou hem nog wat kunnen optimaliseren door niet te Replace'n op de complete text (myText), maar eerst splitten, en dan de Replace uitvoeren op tempstr(1).

[ Voor 47% gewijzigd door Witte op 16-07-2008 12:34 ]

[smeerbartje]

He Witte, thanks man! Het is allemaal gelukt. Replace'n gebeurt nu op temstr(1). Zonder jou was het niet gelukt!