Hi all,
Simple enough question but I can't 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's 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, "dd-mmm-yyyy")) it does the following:

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.

Dim r As Range
Dim t As Date

Set r = Selection.Cells(1) ' change as desired
If VarType(r.Value2) = vbDouble Then
t = r.Value
ElseIf IsDate(r.Text) Then
t = CDate("1 " & r.Text)
End If
MsgBox t

meljunk wrote:

Simple enough question but I can't 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's 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,
"dd-mmm-yyyy")) it does the following:

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.

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's 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
"Jan-11": "11-Jan" - "1-11-Jan".)

--
Artificial by necessity.