[Excel] tekst naar datum conversie

[Shadow_Zero]

Ik zit met de volgende situatie. Ik heb een .csv bestand met, oa., data velden die in twee verschillende formaten zijn:
- 3/5/2006 9:24:38 AM
- 15/10/2003 15:15:54

De eerste ziet Excel als een datum veld (Engelse Office op kantoor), de tweede niet. Dus ik wil de tweede converteren naar een datum veld zodat ik de kolom op datum kan sorteren, en dat blijkt vooralsnog een uitdaging, vooral vanwege de tijd er bij (die ik ook wil behouden).
Ik heb wat mooie bronnen gevonden:
http://chandoo.org/wp/2010/03/23/text-to-date-convertion/
http://fiveminutelessons....ert-text-value-date-excel
http://superuser.com/ques...time-format/274524#274524

en ik heb het idee dat ik dichtbij ben, via de DATEVALUE functie, maar ik krijg het nog niet aan de praat.

Als 'workaround' kan ik mijn region settings op Dutch zetten. Als ik dan de cel open en ENTER klik maakt hij er automatisch een datum veld van. Maar dan moet ik nog uitvinden hoe ik dit voor een hele kolom tegelijk kan doen.

Alternatief is ook om via Text to Columns de tijd uit het veld scheiden en daarna in het derde scherm de Column data format te configeren naar wens en daarna de kolommen weer mergen naar een nieuwe kolom via =A1 & “ “ & B1 bijv. Ik krijg dan momenteel alleen een numerieke waarde waar ik niet weet waar die vandaan komt, 42026 0,419016203703704, terwijl de cel wel op dd/mm/jjjj uu:mm:ss staat geconfigureerd.

Wie kan helpen deze noot te kraken?! (bij voorkeur met 1 functie)

[LFA]

Kun je niet die 2e soort datums (15/10/2003 15:15:54) kopieren naar een word/notepad bestandje en vervolgens terugkopieren naar je Excel bestand? Meestal maakt Excel er dan het goede type van.

[analogworm]

Zit het 'm niet in de format van de cell? dat je er via de custom optie iets van dd/mm/jjjj uu:mm:ss van maakt?

ooh, nevermnind de format van de cell heb je al geprobeerd. Via tijdsnotaties weet ik dat als je van tijd naar number gaat of andersom je er een factor 24 aan moet toevoegen.
Cell A1 = 17:15 (time format)
Cell A2 = A1*24 (number format) = 17,25
Misschien dat je er zo komt als je ook de factor voor data weet te vinden?

[ Voor 60% gewijzigd door analogworm op 30-01-2015 11:51 ]

[RaZ]

Elke Cell kan je in Excell anders opmaken. Ik zou gewoon quick'dirty aan de slag gaan. Die kolom even kopieren, tekst naar kolommen gebruiken, en je hebt een gevuld of een leeg veld. Op die kolom sorteren, zodat de lege cellen bovenaan staan. Dan heb je nog een goede kolom met de juiste informatie er nog in. Die kan je vervolgens selecteren, en de opmaak van die cellen goed zetten.

Volgensmij moet dat op die manier redelijk eenvoudig te doen zijn..

[F_J_K]

(Engelse Office op kantoor)

Dag en maand staan verkeerd om. De eerste wordt ws als 5 maart herkend. Misschien ben je er dan al als je dat aanpast in de Windows settings / in de Excel-celeigenschappen (dus dat je dd/mm/jjjj gebruikt).

Met links(), deel() en rechts() gebruiken icm met datum() en tijd() kan je herschikken in 1 formule/cel.

Maar gebruik geen & maar +.

Edit:
420260,419016203703704 is een factor 10 te hoog.

42026,419016203 = 22-01-15 10:03
420260,419 = 18-08-3050 10:03

[ Voor 14% gewijzigd door F_J_K op 30-01-2015 11:54 ]

[Shadow_Zero]

F_J_K schreef op vrijdag 30 januari 2015 @ 11:51:
[...]

Dag en maand staan verkeerd om. De eerste wordt ws als 5 maart herkend. Misschien ben je er dan al als je dat aanpast in de Windows settings / in de Excel-celeigenschappen (dus dat je dd/mm/jjjj gebruikt).

Je hebt gelijk, het viel me ineens op dat de (US) data velden geen dag groter dan 12 had, dus het zijn verkeerde geinterpreteerde datum velden. Ik ga eens kijken of ik daar wat mee kan.

[Verwijderd]

Het helpt als je de kolom waar datum staat de eigenschap tekst ipv datumveld dd/mm/jjjj toekent.

Sub DatumUSAnaarEU()
'Deze module zet de US schrijfwijze om naar EU 19710824 naar 24/08/1971

'De nodige variabelen definiëren
Dim intTeller, eindwaarde As Integer
Dim VarJaar, VarMaand, VarDag, VarJaarVandaag, VarGebdd As Variant
Dim teken As String
Dim myrange As Range

'Ga naar werkblad 'NAAM WERKBLAD'
Sheets("NAAM WERKBLAD").Select

'Labels invullen
'Begin gebied bepalen
Set myrange = Range("A1") 'kolom waar Geboortedatum staat
'Gebied selecteren
myrange.Select
'label invullen in kolom A1 (Geboortedatum)
ActiveCell.Value = "Geboortedatum"


'de kolom A als tekstveld instellen
Columns("A:A").Select
Selection.NumberFormat = "@"

'Naar kolom A
Set myrange = Range("A1") 'kolom waar Geboortedatum staat
'Gebied selecteren
myrange.Select

'Einde gebied bepalen die aangepast moet worden
'eindwaarde = myRange.CurrentRegion.Rows.Count - 1

'Teller
For intTeller = 1 To eindwaarde

'variabelen recetten
VarJaar = "" 'recet
VarDag = "" 'recet
VarMaand = "" 'recet


ActiveCell.Offset(1, 0).Select '1 rij omlaag

If ActiveCell = "19000101" Or ActiveCell = "" Then ' dan rij naar beneden
ActiveCell.Offset(1, 0).Select '1 rij omlaag
ActiveCell.Value = ""

Else

'b haal dag uit actieve cel en onthoudt het in variabele VarDag
VarDag = Right(ActiveCell.Value, 2)
VarGebdd = VarDag + "/"

' c gebmaand aanmaken in variabele teken via for next lus
'en onthoudt het in variabele VarMaand
VarMaand = Right(ActiveCell.Value, 4)
teken = VarMaand
VarMaand = Left(teken, 2)
VarGebdd = VarGebdd + VarMaand & "/"

'a vormen van jaar
'haal jaar uit actieve cel en onthoudt het in variabele VarJaar
VarJaar = Left(ActiveCell.Value, 4)
VarGebdd = VarGebdd + VarJaar

'Eu schrijfwijze geb dd

ActiveCell.Value = VarGebdd ' VarDag + "/" + VarMaand + "/" + VarJaar

End If


Next intTeller

End sub

[Lustucru]

Welkom op GoT!

Had je toen je dit postte gezien dat het topic ruim 3,5 jaar oud is? Laat oude topics maar genieten van hun welverdiende rust.