VBA \ VB6 - Convert number to DATE
VBA \ VB6 - I have a long number: 20141202153026
which should be converted to date \ time (just to get back to a properly formatted date string):
2014/12/02 15:30:26
Using CDate()
just doesn't cut it.
Format$("20141202153026", "dd/mm/yy hh:mm:ss")
And it doesn't seem to work. Need to be guided here.
EDIT: I used a jac solution and it worked great. One thing to remember is to revert the result back to the cDate () - to make sure the language settings are set and to order the year and month in their correct DD / MM / YYYY positions
CDate(Format$("20141202153026", "####/##/## ##:##:##"))
source to share
I need to talk to Bond to be more reliable, but if you are looking for the fastest smallest exit code and just because I love the alternatives, you can format your date date string to date format and then convert that.
'number mask depends on the input being correct
CDate(Format$("20141202153026", "####-##-## ##:##:##"))
source to share
There is no easy way to do this other than just parse the string:
Const strText As String = "20141202153026"
Dim y As String, m As String, d As String
Dim h As String, n As String, s As String
y = Left$(strText, 4)
m = Mid$(strText, 5, 2)
d = Mid$(strText, 7, 2)
h = Mid$(strText, 9, 2)
n = Mid$(strText, 11, 2)
s = Right$(strText, 2)
Dim dtm As Date
dtm = DateSerial(y, m, d) + TimeSerial(h, n, s)
Debug.Print Format$(dtm, "dd/mm/yy hh:nn:ss") ' => "02/12/14 15:30:26"
Edit:
Although if you want to add a link to the library Microsoft VBScript Regular Expressions 5.5
, you can make it a little simpler:
Dim re As New RegExp, dtm As Date
re.Pattern = "^(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})$"
dtm = re.Replace(strText, "$2/$3/$1 $4:$5:$6")
Debug.Print Format$(dtm, "dd/mm/yy hh:nn:ss") ' => "02/12/14 15:30:26"
source to share