Dates in Excel can be displayed any way you like: 05/10/2026, 5 Oct 2026, Monday 5 October. Changing the format takes a few seconds. The trickier part is when dates refuse to change, and that’s covered below too.
How Excel stores dates
Behind the scenes, every date is a number, the count of days since 1 January 1900. 5 October 2026 is stored as 46300. The date format just controls how that number is displayed.
That’s why you can subtract one date from another to get the number of days between them, and why a date sometimes suddenly shows as a 5-digit number.
Change the date format
- Select the dates.
- Press Ctrl + 1 (on a Mac, Command + 1) to open Format Cells.
- On the Number tab, click Date.
- Pick a format from the list. Change the Locale drop-down if you want another country’s style.
- Click OK.
For a quick change, use the Number Format drop-down on the Home tab and choose Short Date or Long Date.
Create your own format
For full control, choose Custom in Format Cells and type a code in the Type box:
| Code | Result |
|---|---|
dd/mm/yyyy |
05/10/2026 |
d mmm yyyy |
5 Oct 2026 |
d mmmm yyyy |
5 October 2026 |
dddd d mmmm |
Monday 5 October |
ddd dd/mm |
Mon 05/10 |
mmm-yy |
Oct-26 |
yyyy-mm-dd |
2026-10-05 |
The rule is simple: d is day, m is month, y is year. More letters means a longer version. ddd is Mon, dddd is Monday, mmm is Oct and mmmm is October.
Dates won’t change format?
If you change the format and nothing happens, your dates are probably stored as text, not real dates. Signs to look for:
- They’re left-aligned in the cell (real dates sit on the right).
- A formula like
=A2+1gives #VALUE!. =ISNUMBER(A2)returns FALSE.
This usually happens with data pasted from websites or exported from other systems.
Fix text dates with Text to Columns
- Select the column of dates.
- Go to Data > Text to Columns.
- Click Next twice.
- In step 3, choose Date and pick the order the dates are currently in, for example DMY for day/month/year.
- Click Finish.
Excel converts them into real dates, and now any format will work.
US dates in a UK spreadsheet
If your data has American-style dates (month first) but your computer uses UK dates, Excel may misread them. 03/04/2026 becomes 3 April instead of 4 March, and dates like 12/25/2026 stay as text.
Use the Text to Columns method above and choose MDY in step 3. Excel converts them correctly to your local format.
Handy date shortcuts
- Ctrl + ; types today’s date as a fixed value.
=TODAY()shows today’s date and updates every day.- Ctrl + Shift + # applies a standard date format.
Troubleshooting
The column shows #####. The column is too narrow for the date. Double-click the right border of the column letter.
Dates show as numbers like 46300. The cells are formatted as General or Number. Apply a date format.
Typing 1/2 turns into 01-Feb. Excel assumed it was a date. Type an apostrophe first (‘1/2) to keep it as text, or format the cells as Text before typing.
Wrapping up
Ctrl + 1 > Date or Custom changes how dates look. If they won’t budge, they’re text. Run Data > Text to Columns and choose the right date order to turn them into real dates.
