Excel formatting DD-MMM-YYYY, must be in this format

2
votes

I have some data below. Please see below. I need excel to put these dates in this format exactly DD-MMM-YYYY. MMM must be in all caps as well. Like JUN not Jun. Also, for DD, it cannot be 3, it must be 03. Excel date formats that are in excel do not give me this option. It seems to me like it might be a custom formatting option. How can I format in this format precisely? I will look around at the custom formats and try to figure this out. Thank you.

Have

EFFECTIVE_START_DATE
3-Jun-2019
3-Jun-2019

Want

EFFECTIVE_START_DATE
03-JUN-2019
03-JUN-2019
2
excelexcel-formula
There's no custom format that inherently displays dates like this, automatically, I don't believe. Can you reference this cell, and adjust for this later, or is it a requirement to utilize the existing cells only? – gravity
Yes, I can reference this cell and adjust it later. – devcoder112
Right now, I am just doing =UPPER() to get it to upper case. Now I am working on adding the missing leading zeros. – devcoder112
support.office.com/de-de/article/… Here you find a list of possible options for formatting dates. As you can see there is no way of formatting months to be all caps. – jBuchholz
How can I add the missing leading zeros in the dates? – devcoder112

2 Answers

5
votes

There is no custom format for months in all-caps.

If you can reference the existing cell, then use TEXT to get the date formatted as you want and then UPPER to convert to upper case.

=UPPER(TEXT(A2,"dd-mmm-yyyy"))

enter image description here

0
votes

This worked

UPPER(TEXT(C2, "DD-MMM-YYYY"))