Excel Day and Date Formats: Codes, Examples, and Fixes

In Excel for Microsoft 365 on Windows, a date’s day and date format controls how its value appears, not the underlying date itself. Select the cells, press Ctrl+1, choose a date format on the Number tab, or use Custom to set codes such as dddd, mmmm d, yyyy for “Monday, November 2, 2026.”

What an Excel date format changes

Excel usually stores a date as a number and applies a number format to display that value as a calendar date. For example, one cell can show a full date, a short date, or only the weekday, while still holding the same date value. Changing its format normally changes what you see, not the date used in calculations.

This distinction matters because date formats are not the same as date conversion. If a cell contains a real Excel date, changing the format can display it differently. If it contains text that only looks like a date, choosing a date format may have no visible effect and the value may not work correctly in date calculations.

A date’s weekday is determined by the date value itself. Applying a format that displays “Monday” does not turn a Tuesday into a Monday; it reveals the weekday associated with the stored date.

Day, month, and year format codes

In a custom number format, the number of repeated letters controls how much detail Excel displays. These are the main date codes:

d displays the day of the month without a leading zero, such as 2.
dd displays the day of the month with two digits, such as 02.
ddd displays an abbreviated weekday, such as Mon.
dddd displays the full weekday, such as Monday.
m or mm displays the month as a number, such as 11 or 11.
mmm displays an abbreviated month name, such as Nov.
mmmm displays the full month name, such as November.
yy displays a two-digit year, such as 26; yyyy displays the four-digit year, such as 2026.

Combine codes and punctuation to create a display that suits your data. For November 2, 2026, m/d/yy displays 11/2/26, dd-mmm-yyyy displays 02-Nov-2026, and dddd, mmmm d, yyyy displays Monday, November 2, 2026. The exact weekday and month names depend on the language and regional settings in use.

How to apply a built-in date format

For a quick standard format, select the date cells and use the Number Format dropdown on the Home tab. Choose Short Date or Long Date. Short Date is compact, while Long Date typically includes the weekday and a written month; the precise appearance can vary with regional settings.

To choose a more specific built-in option, open Format Cells with Ctrl+1 on Windows, select the Number tab, and choose Date. The Sample area previews the selected cell’s appearance. Select a locale in the Locale (location) list if the available date styles should follow a different regional convention.

On Excel for Mac, Command+1 opens the Format Cells dialog. Ribbon labels and dialog layout can vary somewhat by platform or update, but the Number tab and date-format choices serve the same purpose. Excel for the web offers common formats through the Home tab’s number-format controls; for more detailed custom formatting, the desktop app provides a fuller interface.

Creating a custom date format

Use a custom format when built-in choices do not show the exact level of detail you need. Select the cells, press Ctrl+1, choose Custom on the Number tab, and enter a format in the Type box. Existing formats can be edited or a new code can be entered, then checked in the Sample area before selecting OK.

Examples include ddd for a short weekday label, mmmm yyyy for a month and year, and yyyy-mm-dd for a year-first date. A format such as ddd, dd mmm gives a compact weekday and date, useful in schedules where the year is already clear from the surrounding context.

To include a word or phrase as literal text, enclose it in double quotation marks in the format code. For example, dddd, "the" d "of" mmmm can display Monday, the 2 of November. You can also use punctuation such as commas, hyphens, and slashes directly. Remember that a custom format changes the display only; it does not add words to the actual cell value.

How Excel stores dates and times

In the standard 1900 date system, Excel represents dates as serial numbers: whole-number increments correspond to successive calendar days. Time is represented as a fraction of a day, so noon is one-half of a day. This is why a date can be used in arithmetic and why date-and-time values can include both a whole-number date and a fractional time.

For instance, subtracting one valid date from another returns the number of days between them. Adding 1 to a date value advances it by one day. A format that hides the time does not remove a time fraction from the cell, so two cells can look like the same date while containing different times. If a calculation depends on an exact date boundary, check whether the values include times.

Excel retains a historical compatibility quirk in its 1900 date system: it treats 1900 as if it had a February 29. That date did not exist in the Gregorian calendar, but the behavior was retained for compatibility with older spreadsheet software. It does not normally affect modern dates, though it matters when working with very early dates or comparing serial values across software.

Workbooks can also use the 1904 date system, which has a different starting point. The same date can therefore have a serial value that differs by 1,462 days between the two systems. This is mainly relevant when copying dates between older workbooks or files configured with different date systems; it is not a formatting change.

When a date is text instead of a date value

A value may look like a date but be stored as text, particularly after copying data from a website, importing a CSV file, or receiving dates written in a format that conflicts with the computer’s regional settings. Text dates may align differently from numeric values, fail to sort in calendar order, or behave unexpectedly in formulas. Applying a date format alone does not reliably convert text into a date.

Check a suspicious cell by changing its format to General. A real date usually displays as a serial number, while text usually remains visibly date-like. This is a useful clue, not a complete test: the cell’s alignment may also have been manually changed, and some text can be mistaken for a number.

For a simple conversion, select the affected column and use Data > Text to Columns. Follow the wizard, choose the appropriate date order in the column data format step (for example, MDY or DMY), and finish. Choosing the correct order is essential: 04/05/2026 means April 5 in MDY order but May 4 in DMY order. Make a copy of important source data first, especially when the entries are ambiguous.

For an unambiguous date assembled from separate year, month, and day values, the DATE function can create a real date. With the year in A2, month number in B2, and day number in C2, enter =DATE(A2,B2,C2) in D2. If A2 is 2026, B2 is 11, and C2 is 2, the result is November 2, 2026; applying a display format then controls how it appears.

Choosing a format for your purpose

The best date format depends on who will read the sheet and what the date is used for. A compact format saves space, while a written month can reduce confusion in reports shared across regions. A format with a four-digit year is generally clearer when records span multiple years or may be printed or exported.

For a local worksheet, use Short Date if its order is familiar to the intended readers.
For reports, consider dd-mmm-yyyy or a written month to make day and month order clear.
For a calendar or rota, use ddd or dddd when the weekday is useful, but retain a full date somewhere if the year matters.
For a month-by-month summary, use mmm yyyy or mmmm yyyy.
For data exchanged between systems, an explicitly ordered format such as yyyy-mm-dd can be easier to interpret, but confirm how the receiving system parses the values.

Do not choose a format solely because it makes a column look consistent. A two-digit year can obscure the century, and a weekday-only display hides the month and year. Keep the underlying date information available wherever users need to verify the full date.

Common date-format problems

If a cell displays a number such as 46328, it may contain a valid date whose format was changed to General or Number. Apply a date format to display it as a calendar date. If it displays #####, the column may simply be too narrow for the chosen format; widen the column or use a shorter format. In some cases, hashes can also appear when a date or time value is negative.

If changing the format does nothing, check whether the entry is text. If the date appears in the wrong order, check both the chosen format and the locale used to interpret the original entry. If the weekday seems wrong, verify the underlying date rather than trying to correct the weekday through formatting. A cell can also contain a formula that returns text, which will not behave like a normal numeric date.

Formatting can be misleading when the displayed date omits information. For example, mmm can make dates from different years look identical, and ddd can make dates from different weeks look the same. When sorting, filtering, or calculating, Excel uses the underlying values—not merely the characters currently visible—provided the cells contain real dates.

Frequently Asked Questions

How do I show the day of the week in Excel?
Select the date cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and choose Custom. Enter ddd for an abbreviation such as Mon or dddd for the full weekday. The cell must contain a real date value.

Can I display only the weekday and keep the full date?
Yes. Apply the custom format dddd. Excel continues to store the complete date, so formulas and sorting can still use it. The month and year are simply hidden from view, so consider keeping a full-date column when readers need that context.

Why does changing the date format not change my cell?
The cell may contain text rather than an Excel date value, or it may contain a formula that returns text. Check the value using General format and consider converting imported text with Data > Text to Columns, selecting the correct date order.

How do I change dates from MM/DD/YYYY to DD/MM/YYYY?
For real date values, select the cells and apply a format such as dd/mm/yyyy through Format Cells > Number > Custom. This changes the display, not the stored date. If the original entries are text, convert them using the correct source order first to avoid swapping the day and month.

Does Excel store dates as text or numbers?
Excel normally stores valid dates as serial numbers and displays them using date formats. Imported entries and manually typed values can instead be text, especially when their order is ambiguous or conflicts with regional settings. That difference affects calculations, sorting, and whether number formatting works.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.