Convert RFC Dates to VBA Date

[Oralloy]

Folks,

I'm using VBA to access an XML data source that's giving me RFC style dates. Is there a simple way to convert the dates to VBA's internal Date format?

For example, I extract this date string from my XML: Tue, 23 Nov 2010 09:42:07 -0800

What I'd like is a function that returns the VBA Date value #11/23/2010 09:42:07#

Is there something out there, or do I get to write yet another date parser?

Thanks,
Oralloy

[Guido Geurs]

Split the string and recombine in the format you like:

Dim SPLITARRAY() As String
'§ split on spaces
   SPLITARRAY = Split(Text1.Text, " ")
   Select Case SPLITARRAY(2)
'§ convert month to number
   Case "Jan": SPLITARRAY(2) = "01"
   Case "Feb": SPLITARRAY(2) = "02"
   Case "Mar": SPLITARRAY(2) = "03"
   Case "Apr": SPLITARRAY(2) = "04"
   Case "May": SPLITARRAY(2) = "05"
   Case "Jun": SPLITARRAY(2) = "06"
   Case "Jul": SPLITARRAY(2) = "07"
   Case "Aug": SPLITARRAY(2) = "08"
   Case "Sep": SPLITARRAY(2) = "09"
   Case "Okt": SPLITARRAY(2) = "10"
   Case "Nov": SPLITARRAY(2) = "11"
   Case "Dec": SPLITARRAY(2) = "12"
   End Select
'§ combine new string
   Text2.Text = "#" & SPLITARRAY(2) & "/" & SPLITARRAY(1) & "/" & SPLITARRAY(3) & " " & SPLITARRAY(4) & "#"

[Oralloy]

Guido,

Your singleton solution may well be what I need to do. Thank you.

I was hoping that there was a common library routine that I'd failed to recognize, which actually handled most of the major date formats. I've written such beasts before, but they take time to implement and debug properly.

Cheers!
Oralloy

[Rodney Roe]

Couldn't you just do something like this,

Sub test()
Dim mystring As String
Dim myDate As Date
 
mystring = "Tue, 23 Nov 2010 09:42:07 -0800"
myDate = Mid(mystring, 5, 20)
mystring = "#" & Left(CStr(myDate), 18) & "#"
 
End Sub
 

That should get you the string you need.

[Guido Geurs]

Sorry, not quite the same.

He wants: #11/23/2010 09:42:07#

Your code ("#" & Left(CStr(myDate), 18) & "#") gives:
#23/11/2010 9:42:00#
- day and month not on the same place.
- hours is not "09"
- second = 00 ? => it must be: Mid(mystring, 6, 20)
- why Left(...?? => you can just write:
("#" & CStr(myDate) & "#") because Mydate= 18 long.

But I think this is better:

"#" & CStr(Format(myDate, "mm/dd/yyyy hh:mm:ss")) & "#"

giving:
#11/23/2010 09:42:07#

[Oralloy]

Yep folks,

Good answers. Thank you.

Actually, the CDate(Mid$(...)) solution is the quick answer I settled on earlier, right after Guido tossed out his quick date parser.

My next question would be if we have a general date handling function anywhere in the community? Part of my problem being that people can enter:

• Tue, 23 Nov 2010 09:42:07 -0800
• 11/23/2010
• 23 Nov 2010
• 2010-11-23 15:23

I did such a beast for a commercial web-site using perl a number of years ago; and it worked great. I guess I'm just being lazy, though.

[Guido Geurs]

A question: is it possible to direct the user to enter a date in a well defined format like you find in some web pages: with drop down lists to select the values of day, month and year and the result is a date in your format in a non editable textbox?

Otherwise: you can recognize the formats on distinguished differences like:
- the 1st has a comma
- the 2nd has slashes
- the 3rd has only spaces
- the 4th has "-" signs
so you can write a code like:

If instr(datestring,",") then
   myDate = Mid(mystring, 5, 20)
   mystring="#" & CStr(Format(myDate, "mm/dd/yyyy hh:mm:ss")) & "#" 
else if instr(datestring,"/") then
...
else if instr(datestring,"-") then
...
else
...
end if

[Oralloy]

Not really.

My problem is that I have built a Word report that extracts its data from a vendor product, and the admin may change the date format from underneath me. I'd like the report to continue working correctly in said case, rather than have my report fail.

Yes, I do expect that my customer will be changing date format. Call it "man's intuition". It's a new system, and they just haven't figured out that they want dates to read a little "better".

As I said, I'm being lazy and hoping the function exists, because the approach you're suggesting is exactly the correct one.

The bottom line is that it doesn't, so I'll deal with it.

Thank you.