Format a birth date at known precision
1998 stays 1998, 2001-11 becomes November 2001, and full dates print in full.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | s,SUBSTITUTE(A8,"-","") | Removes the hyphens. LET calls this s. | "200111" |
| 2 | n,LEN(s) | Counts the digits in s. LET calls this n. | 6 |
| 3 | IF(n=6, … ) | 6 digits: month and year. Otherwise the full date. | "November 2001" |
| 4 | IF(OR( … ),A8, … ) | A year alone, or before 1901, stays as typed. | "November 2001" |
| 5 | LET( … ) | Works out s and n once, then uses them. | "November 2001" |
Excel works from the inside out. The day 1 only helps write the month name; it isn't printed.
=LET(s,SUBSTITUTE(A8,"-",""),n,LEN(s),IF(OR(n=4,VALUE(LEFT(s,4))<1901),A8,IF(n=6,TEXT(DATE(LEFT(s,4),RIGHT(s,2),1),"mmmm yyyy"),TEXT(DATE(LEFT(s,4),MID(s,5,2),RIGHT(s,2)),"mmmm d, yyyy"))))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own dates. Paste birth dates like 1998, 2001-11 or 1995-03-09 into the green column of the Template tab. The readable dates fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Months and days need two digits. A value like 2001-1 prints the wrong date, so check short ones.
Microsoft's help: LET function
OTHER ROUTES YOU MIGHT LIKE

Convert only full birth dates
2001-11-04 → November 4, 2001; 2001-11 → Review
Get the template →
Convert 2026-W45-3 text to a date
2026-W45-3 → November 4, 2026
Get the template →
Get the local date after a UTC offset
November 4, 2026 2:30:15 PM + 10 hours → November 5, 2026
Get the template →
Show a letter date as November 5, 2026
11/5/2026 → November 5, 2026
Take this route →