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

Question

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.

What is the date and time setting on your computer control panel? You seem to be dealing with a mixture of US and other date formats. Your date strings are not unambiguous, so this will lead to trouble. — Fionnuala
Gosh...I wish the documentation would specify that it relies on the System date/time to do the conversions! My date/time is set to m/dd/yyyy and standard hh:mm AM/PM time. What would be the best way of converting the string? I cannot change the system time, or the way the string comes in. — FizzBuzz
Your input string says EST, so this suggests to me that it is a US date, as is your system date, so this should make things easier, however, for complete confidence it is often best to use DateSerial : msdn.microsoft.com/en-us/library/gg264202.aspx — Fionnuala

Patrick Honorez Patrick Honorez · Accepted Answer · 2011-09-20T09:54:52

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.

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

1 Answers