How to Change Date Format in Excel (Including Custom Codes)

Is 5/3/2026 the fifth of March or the third of May? It depends who's reading, and that's exactly why date formats cause so much confusion in shared spreadsheets. A booking list made by a US client, an export from an American app, a UK team reading it: suddenly half your dates look wrong.

The good news is that changing how Excel shows a date doesn't change the date itself. You're only changing the display. Here's how to pick a ready-made format, build your own with Microsoft's date codes, and sort out the usual problems.

Changing date format in Excel with Format Cells

This is the main route, and it works the same on Windows and Mac apart from the shortcut.

1. Select the cells with your dates. Click a column letter to select a whole column.
2. Press Ctrl+1 on Windows. On a Mac, press Control+1 or Command+1.
3. In the Format Cells box, open the Number tab.
4. Select Date in the Category list.
5. Under Type, click the format you want. The Sample box shows how your first date will look.
6. Select OK.

If you don't like keyboard shortcuts, right-click the selected cells and choose Format Cells instead. You land in the same place.

Why some Excel date formats have an asterisk

In the Type list, a few formats start with an asterisk. Microsoft says those formats change if you change the regional date and time settings in Control Panel. The ones without an asterisk stay fixed.

That matters if you share files internationally. An asterisk format will follow each person's computer settings, so a colleague abroad may see the date differently from you. If you want everyone to see the same layout, pick a format without an asterisk, or build a custom one.

Showing dates in another country's style in Excel

If the format you want isn't in the list, the date style may be tied to a different language or region. Microsoft says Windows users can change this under File, Options, Language. On a Mac, go to System Settings, General, Language and Region, then Region.

For most small business owners, though, a custom format is quicker, and it doesn't affect anything else on your computer.

Creating a custom date format in Excel

Custom formats let you show dates exactly how you like, such as Mon 5 Mar, or month-and-year headings for a content calendar.

1. Select the cells and press Ctrl+1, or Command+1 on a Mac.
2. On the Number tab, choose Date, then pick the format closest to what you want. Microsoft says built-in formats can't be edited, so this gives you a starting point.
3. Now select Custom in the Category list. The Type box shows the code for the format you picked.
4. Edit the code using the letters in the table below, and watch the Sample box update.
5. Select OK.

Excel date format codes to type

These are the codes Microsoft lists. You combine them with slashes, spaces, dashes or commas.

Code	Shows
d	Day as 1 to 31
dd	Day as 01 to 31
ddd	Short day name, Sun to Sat
dddd	Full day name, Sunday to Saturday
m	Month as 1 to 12
mm	Month as 01 to 12
mmm	Short month, Jan to Dec
mmmm	Full month, January to December
mmmmm	First letter of the month
yy	Year as 00 to 99
yyyy	Year as 1900 to 9999

So for a UK-style date with leading zeros, type dd/mm/yyyy. For a friendly calendar heading, try dddd d mmmm, which spells out the day and month in full.

One quirk from Microsoft's help: if you put m straight after h or hh, or straight before ss, Excel treats it as minutes rather than months. That only matters when your format includes a time.

Handy Excel date shortcuts

• Ctrl+Shift+# applies Excel's default date format on Windows.
• Ctrl+; puts today's date in a cell as a fixed date.
• =TODAY() shows today's date and updates each time the workbook recalculates.

I use the fixed date for logging when an order came in, and =TODAY() for a dashboard header that should always show the current day.

If your Excel date format won't change

1. Seeing ##### in the cell? Microsoft says the column probably isn't wide enough. Double-click the right border of the column heading to widen it.
2. Format changes but nothing happens? The dates may be stored as text, often after an import. Excel can only reformat real dates, so you'll need to convert them first.
3. Dates look different on a colleague's computer? You may be using an asterisk format that follows regional settings. Switch to a non-asterisk or custom format.
4. Showing minutes instead of months? Check where m sits in your custom code next to h or ss.

Date formats for creators and small business spreadsheets

Consistent dates make sorting, filtering and reporting far easier. A few habits help:

• Pick one format for the whole workbook and stick to it.
• Spell the month out, as in mmm, on anything shared internationally, so nobody mixes up day and month.
• Keep a separate column for the date and another for any notes, rather than typing words into date cells.

Dates of birth are one of the most common columns in membership and customer lists, and they're handled very differently on social platforms. If you manage accounts for clients, my guides to updating a birthday on Facebook and correcting your age on TikTok explain what each platform allows.

Frequently asked questions

How do I change the date format in Excel?

Select the cells, press Ctrl+1 or Command+1 on a Mac, choose Date on the Number tab, pick a format under Type and select OK.

How do I make Excel show dates as dd/mm/yyyy?

Open Format Cells, choose Custom in the Category list and type dd/mm/yyyy in the Type box. Microsoft's code table explains each part.

Why does my date show as #####?

Microsoft says the column probably isn't wide enough. Double-click the right border of the column heading to widen it.

Does changing the date format change the date?

No. Formatting only changes how the date is displayed, not the date stored in the cell.

What does the asterisk mean in Excel date formats?

Microsoft says formats starting with an asterisk change if the regional date and time settings in Control Panel change. Others stay the same.