vbaoutlookformatdatetime

VBA Issues converting string to date using format() or CDate()

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

I have the following code, which reads in a string like this; Outage StartDate: 05/10/11 23:59 EST
And pulls out the date information, 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 output I usually get: 

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

But sometimes it swaps the day and year even:

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

Then CDate completely stuffs things around, converting dates to such things as 1931...any help converting a date string to a date object would be greatly appreciated.

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

Edit: Should probably have mentioned this in the initial post. The idea behind reading the string, is to convert to a Date object so that I can create a calendar appointment. Current I use this;

dStartTime = CDate(sStartTime)

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

So obviously there's some conversion issues going on there, but I have no idea what format I'm meant to be feeding to the CDate function in order to turn 29/09/11 23:00 into the equivalent date object.

Solution

  • Format(sStartTime, "dd/mm/yy hh:mm") cannot correctly work since sStartTime is a string, NOT a date.
    You have to do some extra work to get a correctly typed Date, like
    dStartTime= DateSerial(Mid(sStartTime,10,2),Mid(sStartTime,7,2),Mid(sStartTime,4,2)) + TimeSerial(...) etc...
    THEN, you will be able to apply your Format function correctly.