Excel Date Formatting: Complete Guide to Custom Date Formats
Master custom date formats in Excel, from basic month/day/year to complex conditional formatting.
The same date — January 15, 2025 — can be displayed dozens of different ways. Understanding date format codes gives you complete control over how dates appear in your spreadsheets.
Format Cells → Custom
Right-click a cell → Format Cells → Number tab → Custom. Here you enter format codes that control display.
Remember: formatting only changes how a date looks, not the underlying value.
Day Codes
- d — Day without leading zero (5)
- dd — Day with leading zero (05)
- ddd — Abbreviated day name (Wed)
- dddd — Full day name (Wednesday)
Month Codes
- m — Month without leading zero (1)
- mm — Month with leading zero (01)
- mmm — Abbreviated month name (Jan)
- mmmm — Full month name (January)
- mmmmm — Single letter month (J)
Year Codes
- yy — Two-digit year (25)
- yyyy — Four-digit year (2025)
Common Custom Formats
- yyyy-mm-dd — ISO format (2025-01-15)
- d-mmm-yyyy — Unambiguous (15-Jan-2025)
- mmmm d, yyyy — Formal (January 15, 2025)
- dddd, mmmm d — Day focused (Wednesday, January 15)
- mm/dd — Month and day only (01/15)
- mmm 'yy — Short form (Jan '25)
Adding Text to Formats
Wrap literal text in quotes:
"Due: "mmmm d, yyyy → Due: January 15, 2025
dddd", the "d" of "mmmm → Wednesday, the 15 of January
Enter Dates Faster with a Real Date Picker
Excel doesn't have a built-in date picker. XLNavigator adds a calendar popup that makes date entry fast and error-free.
Related Reading
- International Date Formats — DD/MM vs MM/DD handling
- Date Calculations — DATEDIF, EDATE, and more
- Complete Guide to Dates — master Excel dates
Official Resources
- Format dates in Excel — custom date formats
- TEXT function — convert dates to text
Want more Excel tips like this?
Get our free guide: 10 Excel Shortcuts Microsoft Doesn't Tell You About
Join 3,000+ Excel users boosting their productivity.