Excel Date in uppercase

[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?

 

[Frank Kabel]

Hi
AFAIK not possible with formtas to change this

 

[Peo Sjoblom]

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)

 

[Niek Otten]

=UPPER(TEXT(TODAY(),"ddmmmyy"))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

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.

 

[Bernie Deitrick]

Steve,

=UPPER(TEXT(A1,"ddmmmyy"))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Niek

That works great

=UPPER(TEXT(TODAY(),"ddmmmyy"))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Don Guillett]

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

 

[Guest]

Thanks Bernie

Working great now

Steve,

=UPPER(TEXT(A1,"ddmmmyy"))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Frank

See below

Hi
AFAIK not possible with formtas to change this