Excel Date Without Year: Display a Date Without Its Year

To show an Excel date without its year, select the date cells, press Ctrl+1 on Windows, choose Number > Custom, and enter m/d or another date format without a year code. The year stays in the underlying date value, so Excel can still sort the dates and use them in calculations.

What “date without year” means in Excel

Excel does not store a calendar date as just a month and day. A valid date is stored as a serial value that identifies a particular day, including its year; the cell’s number format controls how that value appears. For example, a cell containing July 4, 2026 can display as 7/4, Jul 4, or 04-Jul if you choose a format that omits the year.

This distinction matters because changing the display does not erase or replace the date. The formula bar may still show the full date when you select the cell, and a formula that refers to the cell can still use its year. If you need a recurring anniversary or seasonal date rather than one specific calendar day, decide how you want to handle the year before building calculations around it.

Apply a date format without a year

In Excel for Microsoft 365 on Windows, use these steps to change how existing date values appear:

Select the cells or column containing the dates.
Press Ctrl+1 to open Format Cells.
On the Number tab, select Custom in the Category list.
In Type, enter a format such as m/d, then select OK.

Use mm/dd if you want leading zeroes, such as 07/04, or d-mmm for 4-Jul. To use a full month name, enter mmmm d, which displays July 4. These formats show no year, but they do not alter the values in the selected cells.

On Excel for Mac, Command+1 opens Format Cells; the remaining steps are similar. In Excel for the web, look under Home > Number Format for the custom-format options available in your version. If the web interface does not offer the format you need, apply it in the desktop app. Menu wording and the display of date separators can vary with regional settings.

Why entering a date without a year can be confusing

If you type a month and day without a year, Excel generally interprets the entry as a date in the current year, based on your computer’s date and regional settings. For instance, entering 7/4 may create a date for July 4 of the current year in a month/day locale. In a day/month locale, the same characters may be interpreted differently. The resulting display can also depend on the cell’s existing format.

For a reliable entry, type the year as well, then apply a format that hides it. A full date such as July 4, 2026 is unambiguous once Excel recognizes it as a date. If an entry remains left-aligned, is not recognized as a date, or behaves like ordinary text, check the cell format and your regional date order. You can also use a date picker if one is available in your workbook or Excel environment.

One useful check is to select the cell and inspect the formula bar. If it shows a complete date while the grid shows only a month and day, the cell is a date with a year hidden by formatting. If the formula bar shows only the original characters, the entry may be text instead.

Choose a format that stays readable

Pick a display that is hard to misread in the context where the workbook will be used. A numeric format such as 3/4 can mean March 4 or 3 April to readers in different regions. A month-name format such as Mar 4 is often clearer, especially in a workbook shared internationally. If the year is intentionally omitted because the dates are annual events, a heading such as “Month and day” can make the convention explicit.

Some useful custom formats are:

m/d — 7/4; compact, but dependent on local date conventions.
mm/dd — 07/04; fixed-width numeric display.
d-mmm — 4-Jul; compact and usually easy to interpret.
mmm d — Jul 4; a familiar month-first style.
mmmm d — July 4; longer, but particularly clear in reports.

Custom format codes use month and day symbols and omit the year symbol. If you add a year code such as yy or yyyy, Excel will display the year again. You can return to a standard date display by choosing a date format in Format Cells.

Custom formatting versus converting the date to text

For most spreadsheets, custom formatting is the better choice: the value remains a real date, which supports date arithmetic, sorting, filtering, and date-based charts. A formula that creates a month-and-day label instead produces text. Text can be useful for a report or export where the display itself is the output, but it is not interchangeable with a date value.

With a date in A2, for example July 4, 2026, enter this formula in B2 to return the text label “Jul 4”:

With a date value in A2, the formula returns Jul 4 as text, not as a date serial.

=TEXT(A2,"mmm d")

Because the result is text, it may sort alphabetically rather than in calendar order, and it will not behave as a date in calculations. If the purpose is simply to remove the visible year from a worksheet, format the original date cell instead. Use a text formula when you specifically need a label, such as to combine the date with other text or prepare a display-only field.

Sorting and comparing dates with hidden years

Dates formatted without years still sort by their complete underlying values. If all entries are in the same year, sorting them oldest to newest gives the expected month-and-day order. If entries span several years, Excel sorts by year first and then by month and day, even though the year is not visible. A displayed list that appears out of order may therefore be correctly sorted by its hidden years.

For a list of annual events collected from different years, choose whether the year is meaningful. If each row represents a historical occurrence, keep and show the year so the order is clear. If each row represents a recurring event, you may want a consistent reference year for every entry or a separate month-and-day sorting key. Do not assume that formatting alone changes the sort order.

When filtering, Excel still uses the full date values. Date filters may group entries by year, month, or day, and the hidden year can affect which records appear. If the workbook is shared, tell readers whether the dates represent historical instances or recurring month-and-day events; otherwise, a perfectly valid date column can look ambiguous.

Recurring dates, leap days, and missing years

A date such as February 29 exposes an important limitation: it exists only in leap years. Hiding the year does not make February 29 a date that occurs every year. If you are recording birthdays, contract anniversaries, or reminders, decide how your process treats that event in non-leap years. Some organizations observe it on February 28, others on March 1, and the appropriate rule depends on the situation.

Likewise, a month-and-day label alone cannot answer questions that depend on elapsed time. To calculate someone’s age, determine whether a deadline has passed, or measure days between events, the relevant year is essential. Keep a complete date in the data and use a year-free format only for presentation. If the original year is genuinely unknown, do not quietly invent one; store the known month and day separately or mark the year as unknown in a clearly documented data structure.

For an annual calendar that must show events in a chosen year, using complete dates for that calendar year can make date calculations and sorting straightforward. Keep the source month and day as separate information if you need to generate a fresh calendar each year. That approach makes the recurring nature explicit instead of relying on a hidden year that could have been assigned automatically during entry.

Common problems and how to fix them

The year still appears: Reopen Format Cells and check that the custom code contains no y characters. Also check whether a different cell or conditional format is controlling the display.
The value does not sort like a date: The entries may be text rather than date values. Re-enter or convert them as dates, and verify that Excel recognizes the values before applying a custom format.
Dates appear in the wrong order: Check whether the underlying dates contain different years and whether your local settings interpret month and day in the order you intended.
A formula displays a number: The cell may be using a General or numeric format. Apply a date format to the formula result if it returns a date serial.
The displayed date differs across computers: Regional settings can affect date interpretation and separators. Use month names or provide a clearly specified format when sharing workbooks.

When diagnosing a problem, inspect both the formula bar and the Number tab in Format Cells. The formula bar helps distinguish a complete date from text, while the format setting explains why the grid displays only part of the value.

Frequently Asked Questions

Can Excel store a date with no year at all?

Not as a standard Excel date value: a real date includes a year. You can store the month and day as text or in separate fields, but those values will not automatically have all the behavior of a complete date.

Will hiding the year affect formulas?

No. A custom number format changes only the appearance. Formulas that refer to the cell still use its full date value, including the year, unless the formula specifically extracts or ignores part of the date.

How do I show a date as “4-Jul”?

Select the date cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Custom, and enter d-mmm. The year remains stored even though it is not displayed.

Why does Excel add a year when I type only a month and day?

Excel needs a year to create a complete date, so it generally assigns the current year when it recognizes a month-and-day entry. The interpretation also depends on regional date settings and the cell’s format.

Can I sort dates by month and day while ignoring the year?

Not by changing the display format alone. Excel sorts the complete date values, including hidden years. For recurring events, use a consistent reference year or create a separate month-and-day key for sorting.

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.