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

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


"Alex J" wrote in message
...
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

Thanks for the feedback.
Alex J

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Alex J" wrote in message
...
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