Split Column to Date and time vba-CodePudding

I am new to VBA (my usual coding language is python) and I am simply trying to split a column that contains a combination of Date and Time into two Columns, where date and time are seperated. Though I manage to split them into two columns the format always ends up being wrong. Let me quickly show you what I mean:

Column C contains a combination of date and time, for example "2022-01-01 09:30:00" This should be split into Date in Column D and Time in Column E, in the format "dd.mm.yyyy" and "hh:mm": Column D with 01.01.2022 Column E with "09:30"

I need it in this format, because I need to compare with a different sheet later, where they are in this format.

I found solutions where it was suggested to use Int() to get the date, and then use subtraction to get the time, however my date seems not to be a date but a string. So I tried to format my column to a Date datatype by using the Cdate function, however this resulted in an error.

As I don't necessarily need the value to have this datatype, I thought I can work with the Left() and Right() function. This first gave a problem but by including a string in between, I am getting closer to what I want. Currently I have:

Dim iAircol As Integer
Dim lastrow As Integer
Dim i As Integer
Dim str1 As String
Dim str2 As String
Dim spacepos as Int

iAircol= Worksheets(ws).Cells.Find(What:="Airdate", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    spacepos = InStr(Cells(i, iAircol), " ")
    str1 = Left(Cells(i, iAircol).Value, spacepos)
    Cells(i, iAircol   1) = str1
    str2 = Left(Right(Cells(i, iAircol).Value, Len(Cells(i, iAircol)) - spacepos), 6)
    Cells(i, iAircol   2) = str2
Next i

This technically works, but the value still is in "hh:mm:ss":

Honestly I am very confused by this, because first of all, I am only giving the cell the first 5 characters of the total time, so no idea why it ends up with all 8 characters again, and technically this should be a string now, but Debug.Print gives me the Type "Date" for the date, and a Double for the Time...

It would be great if you could explain to me what is happening in the background, and how to solve my problem. Thanks in advance, if any information is missing, please let me know, and I'll provide it.

CodePudding user response：

Please, use the next function to split the string as you need:

Function splitDateTime(strTime As String) As Variant
   Dim d As Date, t As Date, arrD
   arrD = Split(Split(strTime, " ")(0), "-")
   
   d = DateSerial(CLng(arrD(0)), CLng(arrD(1)), CLng(arrD(2)))
   t = CDbl(CDate(Format(Split(strTime, " ")(1), "hh:mm")))
   splitDateTime = Array(d, t)
End Function

It can be tested like this:

Sub testSplitDateTime()
   Dim arr, ac As Range
   
   Set ac = ActiveCell 'in the active cell should be the string to be split/converted...
   arr = splitDateTime(ac.value)
   ac.Offset(0, 2).EntireColumn.NumberFormat = "HH:mm"
   Range(ac.Offset(0, 1), ac.Offset(0, 2)).value = arr   
End Sub

CodePudding user response：

Use DateValue and TimeValue, they are exactly for this:

Cells(i, iAircol   1) = DateValue(Cells(i, iAircol))
Cells(i, iAircol   2) = TimeValue(Cells(i, iAircol))

Then apply the Format you prefer to the two date and time columns, as these will hold true DateTime values, not text.