Formating - Date as Month In All Caps

[Guest]

Can Format/Cells/Number/Custom be used to make a date show the month name in
all caps?

I am using an HLOOKUP to drive several report fields which are based on the
first day of a month.

Rather than show the date in the mm/dd/yyyy format, I have formated the cell
for mmmm which shows the date with the full month name formated as 'Proper'.
I would like to show the month name in all caps.

Current: A1 = 1/1/2006; Format displays as January - (Proper)
Would like: A1 = 1/1/2006, Format to display JANUARY - (All Caps)

Can not use a formula as users need to type the date in the cell in order to
create different scenarios.

Thanks in advance!

- - TomB - -

 

[Bernard Liengme]

You have a cell (A1, say) with a date and you format it to show the name of
the month in proper case. But you want all capitals?
What has this to do with HLOOKUP?
If A1 displays Nov and F1:Q1 have: Jan, Feb,....Nov, Dec then
=VLOOKUP(MONTH(A1),E1:Q1,2,FALSE) is NOT going to result in Nov. You are
still passing a date with the reference to A1 no matter what it displays.
How about =INDEX(F1:Q1,MONTH(A1)) ? This will return Nov. The method can be
expanded to tables with more than 1 dimension.
Hope this makes sense to you.
best wishes

 

[Guest]

Try this:

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

 

[Guest]

Thanks, but I can not use a formula in the cell. The users will be typing the
date directly into the cell.

I need to know whether (From MENU bar) Format/Cells/Number/Custom be used to
make a date show the month name in all caps?

 

[Guest]

Thanks Bernard, and the HLookup really did not have much to do with the
quetion except to point out that the cell was being used to drive other
functions, and that the cell needed to be free of formulas. The end users
would be typing directly in the cell.

Design issues are limiting my ability to use a formula linked to another
cell to be used for input. That and I have a boss who really wanted it in
caps - - He will have to learn to live with disapointment I guess...

I was hoping to find out whether Format/Cells/Number/Custom offered a
solution simular to how one can change number formating with [RED].

 

[Guest]

Hi Tom,

I don't think there's a custom format way to do this.

One possibility is to use a font in your date cell that only shows CAPS.

Another alternative is to use just a text month in A1, e.g. just type in
NOVEMBER. Then for your HLOOKUPS use "1 "&A1 to give 1st November in the
current year

Thanks Bernard, and the HLookup really did not have much to do with the
quetion except to point out that the cell was being used to drive other
functions, and that the cell needed to be free of formulas. The end users
would be typing directly in the cell.

Design issues are limiting my ability to use a formula linked to another
cell to be used for input. That and I have a boss who really wanted it in
caps - - He will have to learn to live with disapointment I guess...

I was hoping to find out whether Format/Cells/Number/Custom offered a
solution simular to how one can change number formating with [RED].

You have a cell (A1, say) with a date and you format it to show the name of
the month in proper case. But you want all capitals?
What has this to do with HLOOKUP?
If A1 displays Nov and F1:Q1 have: Jan, Feb,....Nov, Dec then
=VLOOKUP(MONTH(A1),E1:Q1,2,FALSE) is NOT going to result in Nov. You are
still passing a date with the reference to A1 no matter what it displays.
How about =INDEX(F1:Q1,MONTH(A1)) ? This will return Nov. The method can be
expanded to tables with more than 1 dimension.
Hope this makes sense to you.
best wishes