Solved: Upper case for months and days

[brunces]

Hi, friends.

Please, I'd like to know if the following is possible, without using tricks such as VLOOKUP or something.

1) Upper case for month

I have a date in cell A1: 01/01/2006
A1 format is defined this way: DD MMM YY
A1 value now is shown this way: 01 jan 06
But I want it to be shown this way: 01 JAN 06 (upper case for the month)

2) Upper case for weekday

I have a date in cell A1: 01/01/2006
I have a formula in cell B1: =A1
B1 format is defined this way: DDDD
B1 value now is shown this way: sunday
But I want it to be shown this way: SUNDAY (upper case for the weekday)

I have already solved these problems above using two tables and the MONTH, WEEKDAY and VLOOKUP functions.

Table MONTHS (1 = JAN, 2 = FEB, 3 = MAR, etc.)
Table WEEKDAYS (1 = SUN, 2 = MON, 3 = TUE, etc.)

But I'd like to know if there's another smoother way to get the same result, without using those tables and functions. Maybe there's a way to apply formats to the cells or maybe there are other functions I don't know which do what I want... whatever!

I have installed "Parry's Format Function Add-in". His functions work very well, but it seems they don't do what I need. Even if they did, there would be a problem: if I send my file to someone who doesn't use Parry's add-in, it won't work. :(

So, please, do you guys know if it's possible to do such a thing? If there's no way to do it, that's OK, just let me know it, please. :)

Thank you very much for your attention, friends.

Hugs,

Bruce

[JKwan]

have you try using "UCASE" function?

[brunces]

JKwan,

I've tried this code...



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo DoorQuit
If Intersect(Target, Range("A1:B10")) Is Nothing Then
Exit Sub
ElseIf Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)
End If

DoorQuit:
End Sub


... but I can't use it because it turns everything into "text". :(

Any idea? :)

Thanks for answering.

Hugs,

Bruce

[mvidas]

Bruce,

Unfortunately you can't use number formatting to change the case of the display. MMM is treated the same as mmm, etc. If you need to use the date's value in a formula, but want it displayed in upper case, you could always insert a new column and hide the date column, using a formula like

=UPPER(TEXT(A1,"dd-mmm-yyyy"))

Reference A1 in your formulas to get the date value and B1 would be the one displayed for printing/etc. Or use DATEVALUE(B1) to get that text date's value for your lookup/etc formulas.

Matt

[brunces]

mvidas,

Well, if there's no other way, I'll try to use the example you've shown me. Or maybe, I'll keep on using the tables I've already created with the VLOOKUP function. :) Thank you, buddy!

Thank you all for your answers, guys. :)

Hugs for all,

Bruce