I am attempting to format a date within Excel as DD-MMM-YYYY where the
MMM would be all caps. The only way that I have seen to do this so far
is to write a VB snippet that would convert it to text. MS actually has
sample code in KB Q213503 which works fine, it's listed below:

Sub UpperMonth()
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Cell.Value < "" And Val(Cell.Value) 0 Then
'Format the cell as text in a 'mmm' number format,
'and change it to uppercase.
Cell.Value = UCase(Format(Cell.Value, "mmm dd yyyy"))
End If
Next
End Sub

The only catch is if I try and change the date mask to "dd-mmm-yyyy",
the code does nothing. If I throw in a variable in the code and step
thru it I can see it will convert the date to MyStr as say 15 0CT 2003
as a string, but never changes the cell value..i.e.

Sub UpperMonth()
Dim MyStr
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Cell.Value < "" And Val(Cell.Value) 0 Then
'Format the cell as text in a 'mmm' number format,
'and change it to uppercase.
MyStr = UCase(Format(Cell.Value, "dd mmm yyyy"))
Cell.Value = UCase(Format(Cell.Value, "dd mmm yyyy"))
End If
Next
End Sub

Any ideas?
Thanks,
Mike



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/