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/

Format the cell as "@" (Text) prior to inserting the datestring
Note that the cell will now be stored as TEXT, so you cannot
use it in calculations.


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.NumberFormat = "@"
Cell.Value = UCase(Format(Cell.Value, "dd mmm yyyy"))
End If
Next
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


mriley wrote:

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/

Worked like a champ...thanks!



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