Mixing date formats in VBA

[Alex J]

All,

I am stuck trying to solve a problem with mixing date formats.
I am trying to sort an array of strings which represent dates ( "31 Mar 04")
on a system whose short date format is yyyy/mm/dd. Since I want to sort the
date strings in order by date, I used the CDate function in VBA.

The result of CDate("31 Mar 04") is (unfortunately) 2031/04/04, which is not
very useful, considerinbg I would prefer either 2004/03/04, or even better
the date code.

Could someone suggest a technique to convert the date correctly in order to
use in a sorting routine.

Thanks,
Alex J

 

[Bernie Deitrick]

Alex,

Try something like

myStr = "31 Mar 04"
CDate(Replace(myStr, "04", "2004"))
or
CDate(Left(myStr, InStrRev(myStr, " ") - 1) & " 2004")

HTH,
Bernie
MS Excel MVP