How to change DATE format without using =UPPER(TEXT("22-04-2010","ddmmmyy")

[RapchikM]

Hello

Does anyone know how to change the date format without using =UPPER(TEXT("22-04-2010","ddmmmyyyy")
This is quite irritating to incorporate the formula for every date written in column

Cell O6 = 22-04-2010
Also with formula written in O6 =UPPER(TEXT(O6,"ddmmmyy") it generated Error

Even I've used in the cell O6 Ctrl+Shift+# but with pressing Ctrl+Shift+# Date Format is 22-Apr-10

So just type the Date in cell O6 as 22-04-2010 and the format is changed to 22APR2010

Any method shown by you shall be highly appreciated

Thanks

RapchikM

 

[6StringJazzer]

If you data is actually a date value you can:

Select the cells (O6 or whatever cells contain dates)
Right-click and go to Format Cells....
Go to the Number tab
Under Category: select Custom
Under Type, type in
ddmmmyyyy

But you can't get the month in all caps.

 

[Dave Patton]

With Excel 365, You can build a new function to do what you require (see below)
• Name the function something appropriate
• N.B. the function prompts for the input namely day, month, year
With your version, try building a UDF for your Upper case information.

T202401a.xlsm
	A	B	C	D
11			22-Apr-20	22APR2020
12			22APR2010
13
14			22APR2010
15
1g

Cell Formulas
Range	Formula
D11	D11	=UPPER(TEXT(C11,"ddmmmyyy"))
C12	C12	=UPPER(TEXT(DATE(2010,4,22),"ddmmmyyy"))
C14	C14	=DateTextU(22,4,2010)

Lambda Functions
Name	Formula
DateTextU	=LAMBDA(Day,Month,year,UPPER(TEXT(DATE(year,Month,Day),"ddmmmyyy")))

​

 

[Alex Blakenburg]

That question has been asked a few times on the Forum and has been around a long time.
The other suggested option that I am sure I remember @MARK858 suggesting was to use an all Cap font but they are pretty ugly. Excel number format help

I think turning a date into Text except in the final presentation layer is a terrible idea (and you seem to be applying it to a column.)

Video is around 2.5 mins and only has the same 2 options in it.

 

[RapchikM]

Dear Dave Patton, Alex Sir and 6StringJazzer

Thank you so much for your wonderful inputs

It seems to change the Date with Month in Capitals I've no alternative but to use =UPPER(TEXT(.......)
and using =UPPER(TEXT....) means having to add 1 more Column for the Formula to derive the result Exactly
By hiding the added Column and also changing the Fonts as per link provided by Alex Sir will really spoil my representation

I think being quite practical will go with 6StringJazzer solution for Customizing the format of date.

But you can't get the month in all caps.

Any possibilities that MS-Excel can provide us the format of DDMMMYYY in Format Cells>Number>Custom
Not asking more but to add a single customize format

Thanks
Rapchik

 

[Peter_SSs]

Out of interest:
Can you explain what the importance of having the month in capitals is while maintaining the value as an actual date is?

 

[RapchikM]

Out of interest:
Can you explain what the importance of having the month in capitals is while maintaining the value as an actual date is?

Peter_SSs Sir

Your Question Indeed is not out of interest
Although Date is Date for different countries but standard way of representation and There is nothing so important having month in Capitals. But for this case Since month Displays its First 3 letters Highlighting Month basically.
I thought of having in Capitals.
No Doubt MS-Excel has definitely provided beautiful number and Date Formats. Hats Off to the Date and Number Formatting provision.

That question has been asked a few times on the Forum and has been around a long time.

Alex Sir and Peter_SSs Sir,

As per your experience if the similar questions has been asked few times on the forum may be in different form and manner or asked on different forums, and unfortunately if this question has been around a long time
Is it not MS-Excel duty to provide another format ie DDMMMYYYY which i asked for in post #5.
From MS-Excel 2007 to MS-Excel Office 2021 and in between different versions which have already come up without the above provision
and new versions to come up may be loaded with above provision DDMMMYYY Which I may have the opportunity to use or not in future

If i am not mistaken Different Countries and its Corporate Companies, Mfg. Industries, Small Business Firms, Individual users, home makers etc use different methods to display Dates in their own style. But at least they will follow some standards to represent date.

Again Sir An American Way to read the Date for eg 10-08-2022 which is Read as 8th October, 2022 or 8-Oct-2022 and if an English has to read the same then We understand as 10th August, 2022 or 10-Aug-2022

Users are many time helpless because of the limitations.

Hope this clarifies and MS-Excel development team does something about it

Sir you have always been kind

Thanks
RapchikM

 

[Dave Patton]

Did you try the suggestion provided by Alex?

Enter the date(s) as you normally do and use custom format with an Upper Case font.
Microsoft provides a variety of Upper Case fonts.

T202401a.xlsm
	C
17	22Oct2010
18	22Oct2010
19	22Oct2010
20	22Oct2010
21	22Oct2010
1g

 

[Dave Patton]

Use Control + 1 for Format Cells

Numbers
custom ddmmmyyyy

Font
Copperplate Gothic Bold or one of the other Upper Case Fonts


You can format the entire column or paste the format.

The dates will still be real dates and not text.

 

[RapchikM]

Dave Patton

Did you try the suggestion provided by Alex?

Font
Copperplate Gothic Bold or one of the other Upper Case Fonts

I am not at all satisfied.

I've clearly mentioned that i will not add a column and not use =UPPER(TEXT........ nor change to Font with Capitals. This disturbs my representation.

Guess What , if you possess US Visa the Issue and Expiry date is having format as 22APR2010 for eg. and Seeing this representation of date I really got impressed. So i thought of incorporating the same.

Regards
RapchikM