Listing month names in all caps

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
Hello Excellers! How can I make a cell values appear in all caps?

I have the following formula in several cells: =DATE($U$1,COLUMN()-2,1). The cell is formatted to show just the month abbreviation: "mmm". Basically the cell just shows the month (Jan, Feb, etc.) but other cells reference the cells and pull the month from the cell so I can't have it show as text for some reason or the dependent cells give me a #VALUE ! error. All of the data is in lowercase but I would like the headings to appear in uppercase both to set them off and because people here like all caps. I don't get it, but happy coworkers make a happy Tina.

I've tried:
  • Changing the format from "mmm" to "MMM".
  • =UPPER(DATE($U$1,COLUMN()-2,1)) which changed the value to text and screwed up the formulas downstream.
  • 6 or 7 other formulas which have all blended together in my mind into some formula hash.

I cannot figure out how to make this happen. Any ideas?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
To return the three character month abbreviation in all caps, you can do something like this:
Code:
=UPPER(TEXT([COLOR=#333333]DATE($U$1,COLUMN()-2,1)[/COLOR],"mmm"))
This will return a Text value. If you have to use it elsewhere in a date, you can use the DATEVALUE function to turn it back, i.e.
Code:
=DATEVALUE(A1 & " 1, 1971")
where A1 is the cell holding the three character month abbreviation.
 
Upvote 0
Try changing the font in the cell to Perpetua titling MT, Copperplate Gothic Light, Castellar or Felix Titling font
 
Upvote 0
You are only talking about 12 cells, correct? Why not simply select each cell with your original DATE formula and Custom Format it with the uppercase month in quotes. It should not take that long to do the 12 cells and you will have a real date pegged to the year in cell U1 but displaying the uppercase month name as you desire.
 
Upvote 0
Thanks to all for the great responses. I think I'm going to go with Rick's solution although I know the next person who is called to maintain it will be confused by the non-traditional cell formatting. It will probably be me.

Again, a heartfelt thank you to all.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top