VBA questions converting a string to date using format () or CDate ()

Question

If this was asked before, please point me in the right direction. I can't seem to find anything useful in my google-ing skills.

I have the following code that reads in such a line; Outage StartDate: 05/10/11 23:59 EST
And pulls out information about the date, i.e. 05/10/11 23:59

sStartTime = Mid(objItem.Body, 18, 15) ' Extract the start time Debug.Print "sStartTime after reading: " & sStartTime sStartTime = Format(sStartTime, "dd/mm/yy hh:mm") ' Format date correctly Debug.Print "sStartTime after formatting: " & sStartTime 

This is what I usually get:

 sStartTime after reading: 05/10/11 23:59 sStartTime after formatting: 10/05/11 23:59 

But sometimes he even changes the day and year:

 sStartTime after reading: 14/07/11 23:59 sStartTime after formatting: 11/07/14 23:59 

CDate then completely fills things up, converting dates to things like 1931 ... any help converting a date string to a date object would be greatly appreciated.

================

Edit: Perhaps this was mentioned in the initial post. The idea of ​​reading a string is to convert the Date object so that I can create a calendar assignment. Current I use it;

 dStartTime = CDate(sStartTime) 

I think this is a problem, sStartTime = "29/09/11 23:00" (dd / mm / yy hh: mm) and dStartTime = "11/9/2029 11:00:00 PM"

Thus, it is obvious that there are some conversion problems, but I do not know what format I intend to submit to the CDate function in order to turn the 29/09/11 23:00 into an equivalent date object.

Answer 1

Format(sStartTime, "dd/mm/yy hh:mm") cannot work correctly, since sStartTime is a string, not a date.
You have to do extra work to get a correctly printed date, for example dStartTime= DateSerial(Mid(sStartTime,10,2),Mid(sStartTime,7,2),Mid(sStartTime,4,2)) + TimeSerial(...) etc ...
Then you can correctly apply the Format function.