Excel number format help

[awrosey]

Hi all, first post.

I am trying to format a range of cells so that when a user enters a date as 12/21/23, it appears in the cell as 21-DEC-23. There is a number format that results in 21-Dec-23, but I am wanting the month in CAPS. I have tried to use a custom format of d-MMM-yy, but that doesn't work. All assistance is appreciated.

Alan

 

[Joe4]

Yes, I believe the date format option will always return the date month abbreviation like that. You could use VBA to convert it to text and capitalize everything, but then it will be treated as text and not a date.

 

[MARK858]

Change the font on the cells to an all capitals font like Castellar with your custom format of d-mmm-yy

 

[Joe4]

Clever trick Mark!

Never thought about changing the font.

 

[MARK858]

Clever trick Mark!

Never thought about changing the font.

It's one Bill showed in a video a few years back the all capitals fonts are all a bit weird looking though

 

[Joe4]

It's one Bill showed in a video a few years back the all capitals fonts are all a bit weird looking though

If I am being completely honest, I didn't even know that there were all capital fonts (never really had need of them).
So I learned something new today!

 

[awrosey]

Thanks Mark, it is a clever trick, the only problem is the boss has specified he wants the font to be Arial. I will let him know that it is Arial with lower case or a different font and upper case. He is pretty particular, so I don't know which he will choose. This is for an invoice worksheet that he is having me revise for him. I already suggested leaving it a a text and have the sales person enter the date in the manner he prefers. I bet he will choose the lower case Arial. We'll see. In any case I have learned something for my own benefit.

 

[MARK858]

The only way I can think of to have it all caps in Ariel other than VBA is to use a formula in a separate cell but then the result will be text, not a real date

Book1
	A	B
15	5-Jan-24	5-JAN-24
Sheet1

Cell Formulas
Range	Formula
B15	B15	=UPPER(TEXT(A15,"d-mmm-yy"))

Edit:
Just out of interest the capital fonts I can see at first glance (there is another Copperplate variation as well) are