String to date conversion with Excel VBA

have a string datetime that contains a pending 'AEST' that needs to be removed so can do date calculations

to do this manually using Find and Replace 'AEST' with blank works. However want to do this in VBA which does not work. - for example when there are 4 sample dates there is inconsistency and the 2 of the dates get converted to US time instead of AUS time. - dont know the root cause of this inconsistency

Sub KPIM_RptMth()

Range("H:H").Select
Selection.Replace What:="AEST", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

e.g. sample dates:

[REQ] WOC Date and Time
12-04-2019 01:03:32 PM AEST
22-06-2019 08:33:04 AM AEST
11-06-2019 03:30:00 PM AEST
17-06-2019 02:50:00 PM AEST

the correct output is:

[REQ] WOC Date and Time
12/04/2019 13:03
22/06/2019 8:33
11/06/2019 15:30
17/06/2019 14:50

the actual output after the VBA script above runs is:
[REQ] WOC Date and Time
4/12/2019 13:03
22-06-2019 08:33:04 AM 
6/11/2019 15:30
17-06-2019 02:50:00 PM 

The issue is that the computer cannot know which format this string date has

12-04-2019 01:03:32 PM AEST

It could be both of the following

dd-mm-yyyy
mm-dd-yyyy

So what you did is let the computer guess and it fails. Even a human cannot say which one of the both formats is the correct one. Therefore dates that are strings and no real dates are pretty useless (unless you have additional information about the format, which is not part of the date-string itself).

So the only working solution to convert a string into a date is that if you know which of the both format is the correct one, to use this information to split the date into pieces and use the DateSerial function to create a real date out of it.

You will need to do something like below for every cell as a string-date conversion.

An example

Public Sub ConvertTimeStampToRealDateTime()
    Dim TimeStampString As String
    TimeStampString = "12-04-2019 01:03:32 PM AEST"

    'split by spaces
    Dim SplitTimeStamp As Variant
    SplitTimeStamp = Split(TimeStampString, " ")
        'SplitTimeStamp(0) = "12-04-2019"
        'SplitTimeStamp(1) = "01:03:32"
        'SplitTimeStamp(2) = "PM"
        'SplitTimeStamp(3) = "AEST"

    'split date by dash
    Dim SplitDate As Variant
    SplitDate = Split(SplitTimeStamp(0), "-")
        'SplitDate(0) = "12"
        'SplitDate(1) = "04"
        'SplitDate(2) = "2019"

    'now we add the information which of the 3 split parts is day, month and year
    'and put it together to a real date
    Dim RealDate As Date
    RealDate = DateSerial(SplitDate(2), SplitDate(1), SplitDate(0))

    'cast time string into a date
    Dim RealTime As Date
    RealTime = SplitTimeStamp(1) & " " & SplitTimeStamp(2)
        'casting from string to date works good for times but not for dates
        'so we can do that with the time

    'Add date and time to make it a datetime
    Dim RealDateTime As Date
    RealDateTime = RealDate + RealTime 'note that we need to ADD them mathematically (+) this is not a string concatenation (&)!!!

    'AEST: This information about how many hours you need to add or subtract
    '      from this datetime to convert it into your desired time zone needs
    '      needs to be done manually

    Dim RealDateTimeInMyZone As Date
    RealDateTimeInMyZone = DateAdd("h", -3, RealDateTime) 'for example subtract 3 hours
End Sub

In the end you can write your real datetime into a cell and format it with Range("A1").NumberFormat into which format you like.

^{Image 1: Variable values during string to date conversion.}

Please note that the code above is just an example of the concept. If you need to make it solid you probably will need some checkings and error handling because it will fail on unexpected input strings.

Also it is probably a good idea to make a function out of it that you can re-use like:

Public Function ConvertDDMMYYYYTimeStampToRealDateTime(ByVal TimeStampString As String) As Date

End Function