Date Format Problem

[grady121]

I have a code that inserts a UserForm TextBox2.Value as a date, into Range("J8") - e.g. 01/08/2011.

What I'm trying to do is then copy the date into Range("J9") and covert its into a Date format to the value of "Aug 11"
Or, if I was to enter 01/09/2011 then I would expect to see "Sep 11"

i.e. Just the first 3 letters of the Month & two numbers of the Year.

I'm trying to use the coverted value in Range("J9") as part of a 'Sheet Name', 'Save As' & 'e-Mail' code.

I've tried

    Range("J9").Value = DateValue(TextBox2.Value)
    Range("J9").Value = Format$(Date, "MMM_YY") ' Change Date format

But it just returns the same month value, regardless of the original date entered.


Any help appreciated.

 

[Norie]

That code will always return the current date, that's what the Date function/statement does.

Which cell do you want the formatted data to end up in?

If it's J9 you could try this.

With Range("J9")
    .NumberFormat = "@"
    .Value = Format(DateValue(TextBox2.Value), "mmm-yy")
End With

Or this

With Range("J9")
       .NumberFormat = "mmm-yy"
       .Value = DateValue(Textbox2.Value)
End With

 

[grady121]

Thanks for the quick reply Norie.

Also thanks for pointing it out the difference.

The code works great now.

Thanks for your time to reply.