Hi Marie,

I have a Woorkbook where I want VBA to change the name of a worksheet.
First, I have a french excel, but I want the date to appear in english...

So I can't find the format I must give him so that he puts the right date.

In excel, the Customized date would be "[$-1009]mmm jj, aaaa;@"
But that doesn't work if I put it in VBA. It gives me a full length month
(with all it's letters, and in french)
What I want is,for exemple: AUG 30, 2004
(Something like: Format(DateR, "mmm dd,yyyy"))
Month written with 3 letters.
Also, I'd like if It could me in capitals... can it be done?

Thank you for your help.

Assuming the date value is in a variable dTheDate, you could use the
following:

sDate = UCase$(Application.Evaluate("TEXT(" & _
Str(CDbl(dTheDate)) & ",""[$-1009]mmm dd, yyyy"")")

Note that the number format uses US symbols for day, month and year.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie