Formatting Issues?

[en_passant]

Hello all,

Hoping for some help with something I can't seem to get my head around. I've taken a screenshot which will hopefully illustrate what I'm up against well, but the issue I'm having is with raw data which is formatted in a crazy way which I'm trying to manipulate/ change.

Please see the screenshot attached, the cell B2 is the raw data. There is normally information in columns C-F, which I've omitted for ease.

I want to create a macro which will open thefile then change the format of column B from the crazy date format it’s in to anormal date of d/m/yy hh:mm:ss – I can change the format manually by going intothe cell and changing it from to yy/mm/dd hh:mm however, this is stillincorrect and not what I want.

Any and allhelp greatly appreciated!

e_p

 

[Yongle]

Try this
- call as in example or use like an Excel formula in any cell
=StringToDateTime(B2)

In example below, C2 is formatted under Number Format \ Custom as dd/mm/yyyy hh:mm

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Raw data [/td][td]Converted[/td][td]Formula in C2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]19/04/2002 06:23[/td][td]

02/04/2019 06:23​

[/td][td] =StringToDateTime(B2)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Sub test()
    Dim aValue As String
    aValue = "19/04/2002 06:23"
    MsgBox Format(StringToDateTime(aValue), "dd/mm/yyyy hh:mm")
End Sub

Function StringToDateTime(ByVal aString As String) As Date
    Dim aTime As String, aDate As String
    Dim D As Integer, M As Integer, Y As Integer
    Dim hr As Integer, min As Integer
'get the time
    aTime = Split(aString, " ")(1)
    hr = Split(aTime, ":")(0)
    min = Split(aTime, ":")(1)
'get the date and switch day and year
    aDate = Split(aString, " ")(0)
    D = Split(aDate, "/")(2) - 2000
    M = Split(aDate, "/")(1)
    Y = Split(aDate, "/")(0) + 2000
'convert to correct date and time
    StringToDateTime = DateSerial(Y, M, D) + TimeSerial(hr, min, 0)
End Function