Forum Discussion
Need capital letters in DDMMMYY date formats.
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?
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"))
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"))
- mathetesSilver Contributor
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.
- Mayukh_BhattacharyaIron Contributor
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:
- Fonts that does the trick ensuring the dates are not formatted as text and retains the dates as numbers are Stencil, Perpetua Titling MT, Engravers MT and Felix Titling!!
- mathetesSilver Contributor
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
See attached