Excel Date: How to Change, Format, and Fix Date Values

To change a date in Excel, select the cell and type the correct date; to change only how it looks, select the cell and use Home > Number Format to choose a date style. That difference matters: editing changes the date value, while formatting changes its display.

First decide what needs to change

Excel dates can be confusing because a cell’s displayed text is not necessarily the value Excel uses in calculations. Excel generally stores a date as a serial number and applies a number format to show it as a calendar date. For example, the same date value might appear as 11/2/2026, 2-Nov-2026, or November 2, 2026, depending on the cell’s format and regional settings.

Use the approach that matches the problem:

The date itself is wrong: enter the correct date, or use a formula to calculate a replacement.
The date is right but looks wrong: change the number format.
The cell will not calculate as a date: it may contain text rather than a date value; convert it.
Dates are all shifted by years: check whether the workbook uses the 1904 date system.

Before changing a large range, test the change on a copy or a few representative cells. A display change is usually reversible, but replacing values or converting text can affect data.

Change how a date is displayed

In Excel for Microsoft 365 on Windows, select the date cells, then go to Home > Number Format and choose Short Date or Long Date. Short Date is compact, while Long Date spells out the weekday and month. The exact appearance depends partly on Windows’ regional settings.

For more control, open the Format Cells dialog with Ctrl+1, choose the Number tab, and select Date. Pick a sample in the Type list and check the displayed sample before clicking OK. Under Custom, you can enter a format such as dd-mmm-yyyy to display 02-Nov-2026, or mmmm d, yyyy to display November 2, 2026. On Excel for Mac, Command+1 opens Format Cells; the number-format options are similar, though the dialog layout can differ.

Formatting does not alter the underlying date. If a cell contains November 2, 2026, choosing a different date style still leaves that same date available to formulas, sorting, and date-based filters. Conversely, a format cannot repair an incorrect date value or turn text into a valid date by itself.

One detail to watch for: a cell may contain both a date and a time. Excel stores the time as a fractional part of the date value. Applying a date-only format can hide the time without removing it. If a calculation or comparison seems to be off by part of a day, inspect the cell’s format or display the time as well.

Edit the actual date value

To replace one date, select the cell, type a valid date in a form Excel recognizes, and press Enter. You can also select the cell and edit the value in the formula bar. If the cell is part of a table or a date column, check nearby entries so the replacement follows the same intended date order.

For dates that follow a pattern, use a formula rather than manually editing each value. For example, with the starting date in A2, enter =A2+7 in another cell to return the date seven days later. If A2 contains November 2, 2026, the result is November 9, 2026. Use =A2-1 to return the previous day. Format the result cell as a date if Excel displays a serial number instead.

To build a date from year, month, and day components, enter =DATE(2026,11,2). This returns November 2, 2026, as a date value; its visible style depends on the cell format. If year, month, and day are in separate cells, with the year in A2, month in B2, and day in C2, use =DATE(A2,B2,C2). DATE is useful because it handles month and year boundaries when the supplied day or month is outside its usual range.

For a calculated result to replace the original dates, copy the formula results and use Home > Paste > Paste Values over the original range. This removes the formulas and keeps the resulting values. Make a backup first if the original dates are important, since pasting values over a range replaces what was there.

Check date order and regional settings

A date such as 04/05/2026 is ambiguous: it can mean April 5 or May 4. Excel interprets entries according to its regional settings and the format it recognizes. If a workbook moves between regions that use month/day/year and day/month/year, the same typed string may be understood differently. Do not assume the order from the way the value happens to display.

When entering dates, use an unambiguous pattern such as 2-Nov-2026, or enter year, month, and day with the DATE function. When importing a file, confirm its date order before converting or sorting it. Changing a cell’s display format after entry will not undo a value Excel already interpreted as the wrong day and month; you must correct the underlying value.

If only one person sees a different display, the workbook may be fine and the difference may come from regional settings or cell formatting. If the dates are reversed or shifted for everyone, investigate the stored values and the import process rather than just applying a new format.

Convert dates stored as text

A value that looks like a date may actually be text. Common clues include left alignment when other dates are right-aligned, an apostrophe at the start of the entry, or formulas and date filters failing to treat it as a date. Alignment is only a clue, not proof, because cells can be manually aligned.

For a column of consistently structured text dates, use Data > Text to Columns. Select the column, choose Delimited, click Next, leave the delimiter settings unchanged if the entries do not use delimiters, and click Next again. In the column data format options, select Date and choose the order that matches the text, such as MDY or DMY. Click Finish, then check several results and apply the desired date format. This conversion step tells Excel how to interpret the existing text; selecting the wrong order can create incorrect dates.

If entries are inconsistent, contain extra spaces, or mix dates with other text, clean and inspect the source before converting the full column. Keep an untouched copy of the imported data until you have verified the results. In Excel for the web, the available import and conversion options can differ from the Windows desktop app; if Text to Columns is unavailable in your version, open the workbook in desktop Excel to use that command.

Common date-change problems

A number appears instead of a date: the value may be a valid date serial displayed with a General or Number format. Apply a date format. If the number is not a plausible date serial, inspect the value before changing it.
Changing the format has no effect: the cell may contain text, or the entry may not have been recognized as a date. Convert the text or re-enter the value as a valid date.
Hashes appear in the cell: the column may be too narrow to show the formatted date. Widen the column or use Home > Format > AutoFit Column Width.
The date changes unexpectedly when typed: Excel may be interpreting an ambiguous entry using a different regional order. Re-enter it unambiguously and verify the result.
A date displays with a time or calculations differ slightly: the value may include a time fraction that a date-only format is hiding.
Sorting puts dates in an odd order: some entries may be text while others are real date values. Convert the text entries before sorting.

These symptoms have different causes. Reformatting is appropriate for a display issue, but it will not fix text, incorrect date order, or a value that includes an unintended time.

Understand the workbook date system

Excel workbooks use a date system to map serial numbers to calendar dates. The standard Windows system is the 1900 date system. Some workbooks, particularly older workbooks created on Mac, may use the 1904 date system. The same serial number represents dates 1,462 days apart in these systems, so a workbook opened or copied between systems can show dates shifted by that amount.

On Windows, check the setting at File > Options > Advanced, then under When calculating this workbook look for Use 1904 date system. Do not toggle it casually: changing the workbook’s date system can shift existing date values. If dates are off by about four years, check the setting and the workbook’s history before editing individual cells. The Mac setting and menu placement can differ by Excel version; look in Excel’s preferences or settings for the workbook’s date-system option.

This issue is different from a date-format mismatch. A format changes the way a value appears; the date system changes the relationship between serial numbers and calendar dates. If a workbook is shared or used for calculations, confirm the intended date system before making a workbook-wide change.

Frequently Asked Questions

How do I change the date format in Excel without changing the date?
Select the cells and choose a style from Home > Number Format, or open Format Cells with Ctrl+1 on Windows and select a date format. This changes the display, not the underlying date value.

Why does Excel show a number instead of a date?
Excel may be showing the date’s serial number because the cell has a General or Number format. Apply a date format. If that does not help, the entry may be text rather than a recognized date.

How can I change all dates from month/day/year to day/month/year?
If the cells contain valid date values, select them and apply a format such as dd/mm/yyyy. If the entries are text, convert them with the correct source order first; formatting text alone does not reliably swap the month and day.

How do I add days to a date in Excel?
With the starting date in A2, enter =A2+7 to get a date seven days later. Excel performs the calculation on the date value, so format the result cell as a date if it appears as a number.

Why are my Excel dates four years and one day off?
The workbook may use a different date system from the one expected. Check whether it uses the 1900 or 1904 system before changing individual dates, since the systems differ by 1,462 days.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.