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 (like 1/15/2024 or 01-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, select Format Cells, choose Date from the Category list, and pick your preferred format.

If you have many dates to change at once, you can select all the cells at the same time by clicking the first one, holding Shift, and clicking the last one in the range. Then type the new date and press Ctrl+D (on Windows) or Cmd+D (on Mac) to fill down the same date to all selected cells. For dates that need different values, you'll need to change them one at a time or use a formula.

Key Takeaways

  • Click the cell, type the new date, and press Enter to change a single date quickly.
  • Excel recognizes dates in formats like 1/15/2024, 01-15-2024, and January 15, 2024, but the format you see depends on how the cell is formatted.
  • Right-click a cell and choose Format Cells to change how a date displays without changing the actual date value.
  • Use Ctrl+D (Windows) or Cmd+D (Mac) to copy a date down to multiple selected cells at once.
  • If Excel treats your date as text instead of a number, you may need to change the cell format to Date before the date will calculate correctly.

Understanding how Excel stores dates

Excel stores dates as numbers, not text. The number 1 represents January 1, 1900 (on Windows) or January 2, 1904 (on Mac). When you type a date, Excel converts it to this number and then displays it in whatever format the cell is set to show. This matters because it means you can do math with dates—subtract one from another to find the number of days between them, or add days to a date.

When you change a date by typing a new one, you're replacing the old number with a new one. If the cell is formatted as a date, the new number will display as a date. If the cell is formatted as a number or text, you'll see the underlying number instead, which looks like 45,000-something. This is why the format of the cell matters as much as what you type.

Changing the date format without changing the date itself

Sometimes you want to keep the same date but show it differently—for example, changing from 1/15/2024 to January 15, 2024. Right-click the cell or cells containing the date, and select Format Cells. In the dialog that opens, click the Number tab (if it's not already selected), then click Date in the Category list on the left.

You'll see a list of date formats on the right side. Click the one you want—for example, "January 15, 2024" or "15-Jan-24"—and click OK. The date value itself doesn't change; only how it appears on the screen changes. This is useful when you need to match a format for a report or when a date is hard to read in its current format.

Using formulas to change dates based on a rule

If you need to change many dates by the same amount—for example, moving all dates forward by 30 days—a formula is faster than changing each one individually. In an empty column next to your dates, type a formula like =A1+30 (if your dates are in column A). This adds 30 days to the date in cell A1. Press Enter, then select that cell again and drag the small square at the bottom-right corner down to copy the formula to all rows with dates.

You can also use formulas to extract parts of a date or combine dates with other information. For example, =YEAR(A1) pulls out just the year, =MONTH(A1) pulls out the month number, and =TEXT(A1,"MMMM") converts the date to the month name spelled out. Once you have the results you want, you can copy them and paste them back as values to replace the original dates if needed.

Fixing dates that Excel treats as text

Sometimes a date looks like a date but Excel treats it as text, which prevents you from using it in calculations or sorting it correctly. You'll often see a small green triangle in the corner of the cell as a warning. To fix this, select the cell or cells, and look for a small yellow warning box that appears. Click it and select Convert to Number.

If the warning box doesn't appear, right-click the cell, select Format Cells, and change the format from Text to Date. Then press F2 to edit the cell and press Enter to confirm. This tells Excel to re-read the cell contents as a date rather than text. If the date still doesn't convert, you may need to delete it and retype it, or use a formula like =DATEVALUE(A1) to convert the text to a date value.

Changing dates in bulk with Find and Replace

If you need to change a date that appears in many cells—for example, replacing all instances of 12/31/2023 with 1/1/2024—use Find and Replace. Press Ctrl+H (Windows) or Cmd+H (Mac) to open the Find and Replace dialog. Type the date you want to find in the Find what field and the new date in the Replace with field.

Click Replace All to change every instance at once, or click Find Next and then Replace one at a time if you want to check each one first. Be careful with this method on dates, because Excel may interpret the date differently depending on your computer's regional settings. If the replacement doesn't work as expected, undo it with Ctrl+Z and try typing the date in a different format.

Changing dates in a pivot table or data range

If your dates are part of a pivot table, changing them directly in the pivot table won't work—you have to change the dates in the original data source instead. Find the worksheet that contains the raw data (not the pivot table), change the dates there, and then refresh the pivot table. Right-click anywhere in the pivot table and select Refresh, or click the Refresh button in the Data or PivotTable tab of the ribbon.

If your dates are in a named range or a table (created with Ctrl+T or the Format as Table button), you can change them the same way you would change any other cells. The table structure stays intact, and any formulas that reference the table will automatically include the new dates.

Frequently Asked Questions

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

The cell is formatted as a number or text instead of a date. Right-click the cell, select Format Cells, choose Date from the Category list, and click OK. The underlying date value is correct; only the display format has changed.

Can I change a date by typing just the day or month?

No, Excel needs the full date to understand what you're typing. If you type only "15", Excel will treat it as the number 15, not as a date. Always include the month and year, in a format Excel recognizes like 1/15/2024 or January 15, 2024.

How do I add or subtract days from a date?

Click an empty cell and type a formula like =A1+7 to add 7 days, or =A1-7 to subtract 7 days. Press Enter, and the result will be the new date. You can then copy this formula down to explore it to many dates at once.

What if I accidentally change a date and want to undo it?

Press Ctrl+Z (Windows) or Cmd+Z (Mac) when ready to undo the change. You can undo multiple steps by pressing Ctrl+Z or Cmd+Z several times. If you've already saved the file, you won't be able to undo past the last save.

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

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