On the "New Titles" worksheet, for cells E4:E24, create and apply a custom number format that displays the dates in the format "2020 January 01".

Excel Custom Number Format: "2020 January 01" Overall Goal The task requires displaying date values in a non-standard format combining a 4-digit year, full month name, and 2-digit day - something Excel's built-in date formats don't offer. A custom number format is the correct app

Manage and format data

Question

On the "New Titles" worksheet, for cells E4:E24, create and apply a custom number format that displays the dates in the format "2020 January 01".

Exhibit

Explanation

Excel Custom Number Format: "2020 January 01"

Overall Goal

The task requires displaying date values in a non-standard format combining a 4-digit year, full month name, and 2-digit day - something Excel's built-in date formats don't offer. A custom number format is the correct approach because:

The underlying cell data remains a true Excel date serial number (not text)
Only the display changes, so dates remain usable in calculations
You get precise control over the output layout

Step-by-Step Procedure

1. Select cells E4:E24

Select the exact range specified. If you apply the format to the wrong cells, other data may be corrupted or the target cells remain unchanged.

2. Open the Format Cells dialog

Right-click → Format Cells (or Ctrl+1). You must reach the Number tab → Custom category. Using a shortcut like the Home ribbon's number format dropdown won't give you access to the custom format input field.

3. In the "Type" field, enter the format code:

yyyy mmmm dd

Code	Produces
`yyyy`	4-digit year → `2020`
`mmmm`	Full month name → `January`
`dd`	2-digit day (zero-padded) → `01`

Spaces between the codes produce literal spaces in the output. Using mmm instead of mmmm would give abbreviated months (Jan), and d instead of dd would drop the leading zero (1 instead of 01).

4. Click OK

This applies the format to the selected range. Clicking Cancel discards it entirely.

What Goes Wrong If Steps Are Skipped

Wrong range selected: Format applies to wrong cells; the exam range shows no change.
Typing text instead of a format code: Excel treats literal strings inside quotes as text - e.g., "January" always prints "January" regardless of the actual month.
Using mm instead of mmmm: Displays the month as a 2-digit number (01) not a name (January).
Entering the format as a formula: Custom formats are metadata on the cell, not cell content - you cannot achieve this with a formula like TEXT() and have it count as a "custom number format."

Memory Tip

Think of the format code as "biggest to smallest, spelled out":

Year (4) → Month (spelled out = 4 M's) → Day (2 digits = 2 D's) yyyy mmmm dd

The number of letters you repeat directly controls specificity: more ms = more of the month name shown.

Topics

#Custom number format#Date formatting#Number formatting#Data formatting

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice