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
Quote:
Originally Posted by meljunk
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.
|