Change format date, dd-mmm-yyyy, but month i need to be in Uppercase 3

[Guest_imported]

I'm doimg a macro , in a range of cells i need to convert the format of the cell, the format taht i want is Date:
dd-mmm-yyyy but my problem is i can't convert the month in font Uppercase (CapsLock).
Please if someone can help me, I wil thank you

Bye.
If you want, you can write to my email:
cesar_soto@

http://www.com

Thank You

 

[xlhelp]

You can only do it using a macro.

Here is the code, courtsey of Microsoft(article Q213503):

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 <> &quot;&quot; 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, &quot;mmm dd yyyy&quot)
End If
Next
End Sub

 

[xlbo]

Nope - can do it in formulae:
=UPPER(TEXT(A1,&quot;dd-mmm-yyyy&quot)
HTH
Geoff

 

[xlhelp]

Thank you. Shoud have thought of that.

Indu

 

[xlhelp]

xlbo

Just wondering!

Doesn't that lose integrity of the number?

Thanks.

Indu

 

[DaleWatson123321]

Cesar,

xlhelp is partially correct - in terms of NOT being able to &quot;format&quot; a cell without using VBA.

However, there is still the &quot;formula&quot; option, so if it happens that a &quot;formula&quot; option is preferable in your situation, you might find the following formula useful.

These formulas are ones I created way back in the days when there were WAY fewer formatting options, and they can easily be modified to suit one's particular preference.

1) This one is modified to match the formatting you described, and references an adjacent cell...

=RIGHT(&quot;00&quot;&FIXED(DAY(A24),0,TRUE),2)&&quot;-&quot;&CHOOSE(MONTH(A24),&quot;JAN&quot;,&quot;FEB&quot;,&quot;MAR&quot;,&quot;APR&quot;,&quot;MAY&quot;,&quot;JUN&quot;,&quot;JUL&quot;,&quot;AUG&quot;,&quot;SEP&quot;,&quot;OCT&quot;,&quot;NOV&quot;,&quot;DEC&quot&&quot;-&quot;&FIXED(1900+(YEAR(A24)-1900),0,TRUE)

2) This one provides the CURRENT DATE...

=RIGHT(&quot;00&quot;&FIXED(DAY(NOW()),0,TRUE),2)&&quot;-&quot;&CHOOSE(MONTH(NOW()),&quot;JAN&quot;,&quot;FEB&quot;,&quot;MAR&quot;,&quot;APR&quot;,&quot;MAY&quot;,&quot;JUN&quot;,&quot;JUL&quot;,&quot;AUG&quot;,&quot;SEP&quot;,&quot;OCT&quot;,&quot;NOV&quot;,&quot;DEC&quot&&quot;-&quot;&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)

3) This one provides the CURRENT DATE and TIME...

=CHOOSE(MONTH(NOW()),&quot;JAN&quot;,&quot;FEB&quot;,&quot;MAR&quot;,&quot;APR&quot;,&quot;MAY&quot;,&quot;JUN&quot;,&quot;JUL&quot;,&quot;AUG&quot;,&quot;SEP&quot;,&quot;OCT&quot;,&quot;NOV&quot;,&quot;DEC&quot&&quot; &quot;&RIGHT(&quot;00&quot;&FIXED(DAY(NOW()),0,TRUE),2)&&quot;, &quot;&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)&&quot; &quot;&RIGHT(&quot; &quot;&FIXED(IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW())),0,TRUE),2)&&quot;:&quot;&RIGHT(&quot;00&quot;&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12,&quot; a.m.&quot;,&quot; p.m.&quot

You can of course copy these from Tek-Tips directly into your worksheet.

If you opt to use any of these and encounter any difficuty, please let me know. I can always email you a copy of the file.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca


P.S. a =>STAR<= to xlhelp for his contribution.

 

[DaleWatson123321]

Ditto, Geoff. and another <<<STAR>>>.

How soon we forget. I too have used the TEXT formatting option in this situation. Thanks for the reminder.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

w/r the TEXT formatting option - bear in mind that excel can still convert back to date if any calculations need doing by using the VALUE function
initial value 01/01/2001
Formatted by using
=UPPER(TEXT(A1,&quot;dd-mmm-yyyy&quot) in A2 gives
01-JAN-2001

Turned back to a date for calc purposes by using
=VALUE(A2) in A3 gives
36892 which, formatted as dd/mm/yyyy gives
01/01/2001

HTH
Geoff