Split date/time not working when 12:00:00 AM

Question

I have vba function to split date/time on my worksheet but when it find 0:00AM it will stop and I don't know how to fix this

code

Function extractDateTime(strTime As Date) As Variant
  Dim arrD, d As String, t As Date
  arrD = Split(strTime, " ")
  d = arrD(0)
  t = CDate(arrD(1) & " " & arrD(2))
  extractDateTime = Array(d, t)
End Function

pic when it find date/time at 12:00:00 AM function not return value arrD(1) and arrD(2)

cell value

pic when function normally working

Answer 1

Always handle date/time as Date, not text, not numbers, no exceptions. So:

Public Function ExtractDateTime(Value As Date) As Variant

    Dim d   As Date
    Dim t   As Date

    d = DateValue(Value)
    t = TimeValue(Value)

    ExtractDateTime = Array(d, t)

End Function

Answer 2

Parsing the Date for spaces is not a great way to go about it.

Instead, you can use Format to just get the pieces you want.

Function extractDateTime(dt As Date) As Variant
    Dim d As String, t As String
    d = Format(dt, "dd/mm/yyyy")
    t = Format(dt, "hh:mm:ss AMPM")
    extractDateTime = Array(d, t)
   
    Debug.Print d
    Debug.Print t

    Debug.Print Format(dt, "mmm dd, yyyy")
    Debug.Print Format(dt, "mmmm")

    Debug.Print WeekdayName(Weekday(dt))

End Function

Kinda seems like a waste of a function tho when you can just do this:

Result = Array(Format(dt, "dd/mm/yyyy"), Format(dt, "hh:mm:ss AMPM"))