Excel Date in uppercase

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help

I have a date if a cell in Excel, which when formatted the month is in lower
case. 15Dec04
how do I change it so that the date appears in upper case 15DEC04?
 
You would need either a help column and a formula

=UPPER(A1)

or a macro


Sub UpCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=UPPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = UCase(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub


press Alt + F11, click insert module and paste in the above,
press At + Q to close the VBE, select the text and press Alt + F8 to run the
macro
--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Thanks for your quick reply however both methods result in the same format of
the date in a numerical form.
'38331.654837963'

I thought it would be a long shot, thanks again for trying.
 
PRE-format column 1 as text. right click sheet tab>view code>copy/paste
this. SAVE. Now when you input a date such as 4/1 you will get 1APRIL2004.
Modify to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target = Day(Target) & UCase(Format(Target, "mmm")) & Year(Target)
Application.EnableEvents = True
End Sub
 
Back
Top