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
