I am currently working on a project report in Microsoft Excel where all my dates are imported in the MM/DD/YYYY format. However, my stakeholders prefer the DD-MMM-YYYY format (e.g., 15-May-2024) to avoid confusion between months and days. I tried using the standard dropdown menu, but the specific "MMM" style isn't showing up as an option. Is there a way to create a custom number format for this, and will it automatically update if I change the underlying date value?
3 answers
To get that specific format, you need to use the "Format Cells" dialog box. Select your date range, press Ctrl + 1, and go to the Number tab. Select Custom from the list on the left. In the "Type" field, manually type dd-mmm-yyyy and click OK. The "mmm" tells Excel to display the three-letter abbreviation of the month, which is perfect for international reports because it eliminates any ambiguity. This change is purely visual; the underlying value remains a date, so your formulas and pivot tables will continue to work perfectly without any need for the TEXT function or manual re-typing.
Does your data actually contain real Excel dates, or are they currently stored as text strings? If they are stored as text, the Custom Number Format won't work until you convert them back into a date format using the "Text to Columns" tool—have you checked the cell alignment to see if they are left-aligned?
You can also use the =TEXT(A1, "dd-mmm-yyyy") formula if you want to display the date in a specific format within a sentence or a concatenated string.
I agree with Margaret. The TEXT function is a lifesaver when you are building dynamic headers for dashboards where you need the date to look a certain way alongside words.
Thomas, you hit the nail on the head! Many people struggle with formatting because their imported data is "Text." If it is text, you can use a formula like =DATEVALUE(A1) to convert it first. Once it's a serial number (which is how Excel sees dates), the dd-mmm-yyyy custom format will apply instantly. I always suggest users check this first by trying to sum the dates—if the sum is zero, your dates are definitely text and need conversion before formatting!