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?

Hi
AFAIK not possible with formtas to change this

--
Regards
Frank Kabel
Frankfurt, Germany

Steve J. Vaughan wrote:
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)



"Steve J. Vaughan" wrote in
message ...
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?

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Steve J. Vaughan" wrote in
message ...
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?

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.

"Peo Sjoblom" wrote:

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)



"Steve J. Vaughan" wrote in
message ...
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?

Steve,

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

HTH,
Bernie
MS Excel MVP

"Steve J. Vaughan" wrote in
message ...
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.

"Peo Sjoblom" wrote:

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)



"Steve J. Vaughan" wrote in
message ...
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?

Thanks Niek

That works great

"Niek Otten" wrote:

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Steve J. Vaughan" wrote in
message ...
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?

PRE-format column 1 as text. right click sheet tabview codecopy/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

--
Don Guillett
SalesAid Software

"Steve J. Vaughan" wrote in
message ...
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?

Thanks Bernie

Working great now


"Bernie Deitrick" wrote:

Steve,

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

HTH,
Bernie
MS Excel MVP

"Steve J. Vaughan" wrote in
message ...
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.

"Peo Sjoblom" wrote:

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)



"Steve J. Vaughan" wrote in
message ...
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?

Thanks Frank

See below

"Frank Kabel" wrote:

Hi
AFAIK not possible with formtas to change this

--
Regards
Frank Kabel
Frankfurt, Germany

Steve J. Vaughan wrote:
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?