Change Date Format
Select the cells, press Ctrl+1 (or right-click > Format Cells), open the Number tab, and pick a built-in date format, or choose Custom to type your own code — d/dd for day, m/mm for month, yyyy for a four-digit year, so dd/mm/yyyy shows 12/08/2026 while mm/dd/yyyy shows 08/12/2026 for the same date. One trap: m or mm means month on its own, but switches to meaning minutes the moment it sits next to a time code — right after h or hh, or right before ss — since Excel reads h:mm:ss as a clock, not a date. Format codes also do nothing whatsoever to a cell that merely resembles one; the value underneath has to be a real date before any code can act on it.
The formula
=TEXT(A2,"dd/mm/yyyy") day/month/4-digit year, as text
=TEXT(A2,"mmm d, yyyy") short month name, e.g. Aug 12, 2026
=TEXT(A2,"h:mm AM/PM") time only — mm here means MINUTES: it follows h
=TEXT(A2,"mm:ss.0") mm here also means minutes: it precedes ssA worked example
A2 holds the date August 12, 2026, entered and stored as a real date value (right-aligned, not text).
Ctrl+1 > Number > Custom > dd/mm/yyyy
12/08/2026 in the cell — but the formula bar still shows the date in your system's own default short-date format (commonly 8/12/2026 for US English), since the formula bar ignores a cell's custom number format. Only the display changed; the stored date value didn't.
Which one do I need?
| If you want to… | Use |
|---|---|
| Changing how a real date displays wherever it's shown | Ctrl+1 > Number > Date for a built-in format, or Custom for your own code like dd/mm/yyyy |
| The dates look right but are actually text (left-aligned, ignoring format codes) | Convert them first — =DATEVALUE(A2), or Data > Text to Columns with the column set to Date — then apply the format code |
| You need the formatted date as a text string inside a formula, not just a display change | =TEXT(A2,"dd/mm/yyyy") — the result is text, so date math no longer works on it |
| Highlighting or flagging dates automatically based on when they fall (overdue, due this week), not changing how they're displayed | That's conditional formatting's date rules, a different feature from a display format code |
| Every reader should see the same day/month order regardless of their own regional settings | Use an explicit custom code like dd-mmm-yyyy instead of a built-in format — built-ins can reorder day and month for a different locale |
| You're in Google Sheets, not Excel | Format > Number > Custom date and time — the same day/month/year letters and the same mm-means-minutes-near-h rule apply |
Frequently asked questions
Why won't Format Cells change how my dates look?
The values are almost certainly stored as text, not real dates — text is left-aligned by default and format codes have nothing to act on. Convert to a real date first, with =DATEVALUE(A2) in a helper column or Data > Text to Columns with the column set to Date, then reapply the format code.
Why does my date format code show minutes instead of the month?
m and mm mean month by default, but Excel switches them to mean minutes whenever they sit immediately after an h or hh code, or immediately before an ss code — matching how a clock is written, h:mm:ss. A code like h:mm is a time, not a date, even though mm looks identical to the month code used elsewhere.
Do the same format codes work in Google Sheets?
Yes — Format > Number > Custom date and time in Google Sheets uses the same day/month/year letters as Excel (d/dd, m/mm, yyyy), and the same rule that mm next to an hour code means minutes rather than month.
Does changing a date's format also change the value stored in the cell?
No. A date is stored as a serial number regardless of how it displays; a format code only changes what's shown, not the underlying value. That's also why the formula bar shows the date in your system's own default format rather than whatever custom code is applied to the cell.
New guides and tools, once a month
DE + EN · double opt-in · no spam