The fastest way to change a date in Excel

To change a date in Excel, click the cell containing the date, type the new date in the format Excel recognizes (such as 1/15/2024 or January 15, 2024), and press Enter. Excel will automatically format it as a date if the cell is already formatted as a date, or you can right-click the cell afterward, select Format Cells, choose the Date category, and pick your preferred date format.

If you are working with many dates at once, you can also use Find & Replace to change all instances of one date to another. Press Ctrl+H (or Cmd+H on Mac), type the old date in the Find field, the new date in the Replace field, and click Replace All.

Key Takeaways

  • Click the cell with the date, type the new date, and press Enter to change a single date quickly.
  • Excel recognizes dates in common formats like 1/15/2024, 01-15-2024, and January 15, 2024.
  • Use Find & Replace (Ctrl+H) to change the same date across many cells at once without editing each one individually.
  • Right-click a cell and select Format Cells to change how a date looks without changing the actual date value.
  • If Excel treats your date as text instead of a number, convert it first by using the Data menu's Text to Columns feature.

When Excel treats your date as text instead of a number

Sometimes Excel stores a date as text rather than as a date value. You will notice this when the date is left-aligned in the cell (dates are normally right-aligned) or when formulas that reference the date do not work. This happens most often when you copy dates from another program or when a date is entered with a leading apostrophe.

To fix this, select the cells containing the text dates, go to the Data menu, and click Text to Columns. In the wizard that opens, click Next twice to skip the first two steps, then make sure the Date column format is selected in the third step. Click Finish. Excel will convert the text to actual date values, and you can then change them normally.

If you only have one or two cells, you can also click the cell, type an equals sign, then the cell reference (for example, =A1), and press Enter. This forces Excel to treat the contents as a formula result, which converts text dates to real dates.

Changing dates using formulas instead of editing directly

If you want to change a date by adding or subtracting days, months, or years without manually typing a new date, use a formula. The simplest approach is to use the DATE function combined with the YEAR, MONTH, and DAY functions to extract parts of the original date and modify them.

For example, to add 30 days to a date in cell A1, type =A1+30 in a new cell and press Enter. To add one month, use =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)). To add one year, use =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)). Once the formula calculates the new date, you can copy the result and paste it back into the original cell as a value (using Paste Special > Values) if you want to replace the old date.

Changing the format a date displays without changing the date itself

Sometimes you do not need to change the actual date—you just need it to look different. Excel stores dates as numbers behind the scenes, so changing how a date appears does not change the underlying value. Select the cell or cells with the dates you want to reformat, right-click, and choose Format Cells.

In the Format Cells dialog, click the Number tab (if it is not already selected), then click Date in the Category list on the left. You will see a list of date formats on the right. Click the format you want—for example, "3/14/2024" or "March 14, 2024"—and click OK. The date will now display in the new format, but the value stored in the cell remains the same.

If none of the built-in formats match what you need, you can create a custom format. In the Format Cells dialog, click Date in the Category list, then look for a Custom category at the bottom. Click it and type a custom format code (for example, dddd, mmmm d, yyyy displays "Wednesday, March 14, 2024"). Click OK when you are done.

Changing dates in a range or column at once

If you need to change many dates by the same amount—for example, moving all dates forward by one week—select the entire range of dates first. Then use Find & Replace with a formula, or use a helper column with a formula and copy the results back.

The helper column method works like this: click a blank column next to your dates, type a formula like =A1+7 (to add 7 days), and press Enter. Then click that cell again, copy it, select the range of blank cells below it that matches the number of dates you have, and paste. Excel will adjust the formula for each row. Once all the new dates are calculated, select them, copy, then click the original date column, right-click, and choose Paste Special. Click Values and then OK. This replaces the old dates with the new ones. You can then delete the helper column.

When to call a spreadsheet informed instead

If you are working with dates across multiple sheets, dates linked to other files, or dates that need to update automatically based on a rule or condition, a spreadsheet informed or your IT department can help you set up a more robust solution. Similarly, if changing dates breaks formulas or charts elsewhere in your workbook, it is worth getting help to understand the dependencies before making changes.

For most everyday date changes—correcting a typo, reformatting how dates look, or shifting a range of dates forward or backward—the methods above will handle the job. Test your changes on a copy of your file first if you are unsure, so you can undo without losing your original data.

Frequently Asked Questions

Why does Excel show my date as a number like 45000 instead of a date?

The cell is formatted as a number instead of a date. Right-click the cell, click Format Cells, select Date from the Category list, choose a date format, and click OK. The number will display as a date.

Can I change a date in Excel without typing it manually?

Yes. Use a formula like =A1+30 to add days, or =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) to add a year. You can also use Find & Replace to swap one date for another across many cells at once.

What happens if I change a date that is used in a formula?

The formula will recalculate automatically using the new date. If the formula is summing dates or calculating time between dates, the result will change. Check any charts or reports that depend on that date to make sure the change does not break them.

How do I change the date format for an entire column at once?

Click the column header to select the whole column, right-click, choose Format Cells, click Date in the Category list, pick your format, and click OK. All dates in that column will display in the new format.

Can I undo a date change if I make a mistake?

Yes. Press Ctrl+Z (or Cmd+Z on Mac) when ready after the change to undo it. If you have made other changes since, you can undo multiple steps by pressing Ctrl+Z repeatedly, or click the undo arrow in the toolbar and select the specific action you want to undo.