Excel - Date Format - simple question

Hi all,
Simple enough question but I cannot find the answer. I have date values
in a spreadsheet that can be in mmm-yy or dd-mmm-yy format e.g.
sometimes Jan-11 other times 01-Jan-11. There is no problem when it is in
dd-mmm-yy format, the problem is the ones that are just mmm-yy format.

When I try to convert the field to date format (format(myDate,

Jan-11 CONVERTS TO 11-Jan-2012
Feb-10 CONVERTS TO 10-Feb-2012

So what I want to do is when a value is Jan-11, I want to convert it to
01-Jan-2011. If the value is 01-Jan-11 then convert it to 01-Jan-2011.

Thanking you in advance.




--
meljunk

When you enter a date with just two values, where they can be interpreted as
a day-month pair (e.g. Jan-11) Excel assumes you mean "January 11, [current
year]", not "January 2011". To avoid this in the future, you can enter the
date with the 4-digit year (since there is no 2011th day of January, Excel
picks the first day of that month).

My best suggestion would be to check the cell's .Text property, somewhat like
this:

t = ActiveCell.Text
y = InStr(InStr(ActiveCell.Text, "-") + 1, ActiveCell.Text, "-")
'if y then dd-mmm-yyyy else mmm-yy
If Not y Then t = "1-" & t
'format here using t

(But note that this code will get things wrong if it gets "11-Jan" instead of

--
Artificial by necessity.