Upper case month names in custom formatting

[gak67]

Hi

I am using a custom formatting for a date in a cell that is "ABC COMPANY REPORTING TIMETABLE FOR "MMM YY

When I enter 1/7/14 in the cell (we use D/M/YY in NZ) I would like it to appear in the cell as ABC COMPANY REPORTING TIMETABLE FOR JUL 14

Unfortunately it is showing as ABC COMPANY REPORTING TIMETABLE FOR Jul 14

As it is a heading I was wanting it all in upper case. Does anybody out there know how to get a custom format to show upper case characters?

Thanks in advance.

Glenn

[:) Sixthsense :)]

What about using Upper() function with Text() function like this?

In A1 Cell

01/07/2014


In B1 Cell

=UPPER(TEXT(A1,"MMM YY"))

Or

="COMPANY REPORTING TIMETABLE FOR "&UPPER(TEXT(A1,"MMM YY"))

[FDibbins]

try this...
="aaa "&UPPER(TEXT(A1,"MMM YY"))

[sktneer]

This can be achieved with the help of a VBA code. If you are open to a VBA solution to this, try this......

Right Click on the Sheet Tab --> View Code --> Paste the code given below in the opened code window --> Close VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook.

Please Login or Register  to view this content.

The code work for any change in the cell A1 (red range reference in the code). Change it as per your requirement.
In the attached sheet, input a date in A1 to see if you get the desired output.

[gak67]

What about using Upper() function with Text() function like this?

Thanks, but I was wanting to avoid having a separate cell for the date and the heading.

[gak67]

try this...
="aaa "&UPPER(TEXT(A1,"MMM YY"))

Thanks Ford, but I tried it and got the following error message:
Mircosoft Excel cannot use the number format you typed
Try using one of the built-in number formats.
For more information about custom number formats, click Help.

[gak67]

This can be achieved with the help of a VBA code.

Thanks sktneer, but I'm not the only one using this sheet and others can be wary of enabling content.

[FDibbins]

Thanks Ford, but I tried it and got the following error message:
Mircosoft Excel cannot use the number format you typed
Try using one of the built-in number formats.
For more information about custom number formats, click Help.

Worked fine for me, and is pretty much identical to shat sktmeer suggested as well

[gak67]

Worked fine for me, and is pretty much identical to shat sktmeer suggested as well

Can you show it working on the attached sheet and re-upload it?02 Reporting Timetable with dates.xlsx I can't get it to work. As soon as I type an E (as in UPPER) in the custom format it tries to treat it as an exponential.

[FDibbins]

aahh OK now I undersdand. I did not read (*blames his dirty specs*) that you said you was custom formatting

Im not sure you can do what you want with regular formatting/fomrmulas An alternative would be to to enter your date in A1 (like now) but make it white text on a while background (so it doesnt show), then in B2, use...
="ABC COMPANY FINANCE REPORTING TIMETABLE FOR "&UPPER(TEXT(A1,"mmm yy"))

Sorry for the mis-understanding

[daddylonglegs]

Another way is to keep your current custom format but change the font for that cell to a font which only allows UPPER case letters, e.g. I have one called "Stencil" that does that.....

[gak67]

I did not read (*blames his dirty specs*) that you said you was custom formatting

Must be very dirty considering it was in both the title and the first line (excluding the "Hi") of the OP

The white text option won't work as if somebody else starts this file for the new month they would need to know which cell to change.

And the font idea of daddylonglegs is one I hadn't thought of, but there is a consistency to the fonts and layouts here that means it's not likely to be widely accepted.

I think my best bet is to simply flag the uppercase heading idea and just capitalise each word, eg ABC Company Finance Reporting Timetable for Jul 14.

Thanks for your suggestions everybody.

[:) Sixthsense :)]

As I remember I use the > symbol for Upper Case and Less than Symbol for lower case in access Field Format.

I checked the excel VBA Format() function which also works like access.

In Immediate Window

? Format("message",">")

But Text() function is not working like VBA Format() function as intended

=TEXT("message",">")