SOLVED

Need capital letters in DDMMMYY date formats.

Copper Contributor

In all aeronautical matters as well as in all planning travel, dates are crucial.

Information regarding flights use a DDMMMYY format, with capital letters.

Air Reservation systems only handle capital letters for all related information.

Unfortunately in Excel I can't find such date format: 23FEB24, 25AUG24.

Every time I tried, it gets changed to 23feb24, 25aug24.

In addition these information cannot be converted to TEXT format, as we need to input dates of travel, dates of birth, dates of passport expiration as DATE data.

Is there anyone who can help me?

4 Replies

@AntonioPonce 

 

This could be made more elegant, but here's a formula that will convert a standard Excel date to text.

=TEXT(DAY(A4),"0")&CHOOSE(MONTH(A4),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")&TEXT(YEAR(A4),"0")

As it's done here

mathetes_0-1706124705938.png

See attached

 

best response confirmed by mathetes (Silver Contributor)
Solution

@AntonioPonce 

Excel does not provide a way to display month names in upper case in a number format.

So you'll have to use one column to enter the "real" dates, to be used in calculations, and another column to display the dates the way you want, as text. Just like in the reply by @mathetes 

A slightly shorter formula:

=UPPER(TEXT(A4,"ddmmmyy"))

@AntonioPonce AFAIK you would need to use a TEXT() function to enforce UPPER() which has already been suggested however you could try in addition to your present custom formatting change the font for that cell/range to a font that allows only UPPER case letters. Watch below:

 

Mayukh_Bhattacharya_0-1706137885028.gif

 

 

  • Fonts that does the trick ensuring the dates are not formatted as text and retains the dates as numbers are StencilPerpetua Titling MTEngravers MT and Felix Titling!!

@HansVogelaar 

 

A slightly shorter formula:

=UPPER(TEXT(A4,"ddmmmyy"))

 

Slightly shorter and a lot more elegant (AKA efficient). Well done. I tried something like that, but obviously not exactly...which is why I ended with what I did.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@AntonioPonce 

Excel does not provide a way to display month names in upper case in a number format.

So you'll have to use one column to enter the "real" dates, to be used in calculations, and another column to display the dates the way you want, as text. Just like in the reply by @mathetes 

A slightly shorter formula:

=UPPER(TEXT(A4,"ddmmmyy"))

View solution in original post