The simplest way to add a date in Excel

Type the date directly into a cell using a format Excel recognizes. The most reliable formats are MM/DD/YYYY (like 03/15/2024), DD/MM/YYYY (like 15/03/2024 if your system is set to that region), or the full text format like March 15, 2024. Excel will automatically convert it to a date value and align it to the right side of the cell — that right alignment is how you know Excel understood it as a date, not text.

You can also type today's date by pressing Ctrl+; (semicolon) on Windows or Cmd+; on Mac. This inserts the current date in your system's default format. If you need today's date plus the current time, press Ctrl+Shift+; on Windows.

Once a date is entered, you can change how it displays without changing the actual date value. Right-click the cell, select Format Cells, click the Number tab, choose Date from the Category list on the left, and pick the format you want from the list. The underlying date stays the same — only the appearance changes.

Key Takeaways

  • Excel recognizes dates typed as MM/DD/YYYY, DD/MM/YYYY, or spelled-out months, and automatically aligns them right in the cell to show it understood them as dates.
  • Use Ctrl+; on Windows or Cmd+; on Mac to insert today's date instantly without typing it.
  • Right-click a date cell, open Format Cells, and choose Date from the Category list to change how the date displays without changing its actual value.
  • If a date appears as text (left-aligned) instead of a date (right-aligned), the cell format is set to Text and needs to be changed to Date or General.

When Excel doesn't recognize your date as a date

If you type a date and it stays left-aligned in the cell, Excel treated it as text instead of a date. This usually happens because the cell format is already set to Text. To fix it, select the cell, right-click, choose Format Cells, click the Number tab, and change the Category from Text to General or Date. Then press Enter. Excel will reinterpret what you typed.

Another common cause is typing a date in a format your system doesn't recognize. If your computer is set to US English, typing 15/03/2024 might confuse Excel because it expects month first. Stick to MM/DD/YYYY or spell out the month name to avoid this problem.

If you're copying dates from another program or website and they paste as text, select the pasted cells, go to Data in the menu bar, click Text to Columns, click Next twice, make sure Date is selected in the Column data format section, and click Finish. This converts text that looks like dates into actual date values.

Using formulas to add dates automatically

The TODAY() function inserts today's date and updates automatically each time you open the file. Type =TODAY() into a cell and press Enter. The date will appear in your system's default format and will change to tomorrow's date when you open the file tomorrow.

The DATE() function lets you build a date from separate year, month, and day values. Type =DATE(2024,3,15) to create March 15, 2024. This is useful when you have year, month, and day in different cells — you can reference them like =DATE(A1,B1,C1) where A1 contains the year, B1 the month, and C1 the day.

To add a number of days to a date, just add the number. Type =TODAY()+7 to get the date seven days from now, or =A1+30 to add 30 days to whatever date is in cell A1. Subtract the same way: =TODAY()-14 gives you the date two weeks ago.

Changing how dates display without changing their value

Excel stores dates as numbers behind the scenes — January 1, 1900 is 1, January 2, 1900 is 2, and so on. The format you choose only changes what you see on screen. Select the cells with dates you want to reformat, right-click, and choose Format Cells. Click the Number tab, select Date from the Category list, and browse the formats available. Common options include 3/15/2024, 15-Mar-24, March 15, 2024, and 2024-03-15.

You can also use the formatting buttons in the toolbar. Select your date cells and look for a dropdown that shows date formats — it varies by Excel version, but it's usually in the Home tab. Click the dropdown to see preset formats you can apply in one click.

If none of the built-in formats match what you need, you can create a custom format. In the Format Cells dialog, select Date from the Category list, scroll to the bottom, and click Create New Format (or look for a Custom category). You can then type a code like dddd, mmmm d, yyyy to display dates as Friday, March 15, 2024. The codes are: d for day, m for month, y for year, and repeating them changes the format (dd gives 01–31, ddd gives Mon, dddd gives Monday).

Sorting and filtering dates correctly

If your dates are entered as actual dates (not text), Excel will sort them chronologically when you select the column and click Data > Sort A to Z or Sort Z to A. Text that looks like dates will sort alphabetically instead, which puts them in the wrong order. This is why it matters whether Excel recognizes your dates as dates or text.

To sort by date, select any cell in the column with dates, go to Data in the menu, click Sort, make sure the column is selected, and choose Oldest to Newest or Newest to Oldest. If those options don't appear, your dates are stored as text and need to be converted first using the Text to Columns method described earlier.

Filtering works the same way. Click Data > Filter to add dropdown arrows to your column headers. Click the arrow in a date column and you'll see options to filter by date range, specific dates, or relative dates like "This Month" or "Last Quarter" — but only if the column contains actual dates, not text.

Common date entry mistakes and how to fix them

Typing dates with two-digit years like 03/15/24 usually works, but Excel interprets two-digit years between 00 and 29 as 2000–2029, and 30–99 as 1930–1999. If you type 03/15/30, Excel reads it as March 15, 1930, not 2030. Always use four-digit years to avoid confusion.

If you paste dates from a website or PDF and they appear as numbers like 45000, they're stored as Excel's internal date number but formatted as General instead of Date. Select the cells, right-click, choose Format Cells, select Date from the Category, and click OK. The numbers will display as readable dates.

Dates that include times (like 3/15/2024 2:30 PM) need the Date Time category in Format Cells, not just Date. If you enter a date with a time but it displays as only the date, the format is set to Date only. Change it to a format that includes time, or use a custom format like mm/dd/yyyy hh:mm AM/PM.

Frequently Asked Questions

Why does my date show as a number like 45000?

Excel stores dates as numbers internally. If a date displays as a number, the cell format is set to General or Number instead of Date. Right-click the cell, choose Format Cells, select Date from the Category list, and click OK. The number will display as a readable date.

Can I add a date that doesn't change, or does TODAY() update every time I open the file?

TODAY() updates every time you open the file. To enter a date that stays the same, type it directly or use Ctrl+; to insert today's date as a fixed value. If you've already used TODAY() and want to lock it, copy the cell, right-click, choose Paste Special, select Values, and click OK.

How do I calculate the number of days between two dates?

Subtract one date from the other. Type =A2-A1 where A1 and A2 contain dates. The result is the number of days between them. If the result displays as a date instead of a number, right-click, choose Format Cells, select Number from the Category, and click OK.

What's the difference between entering a date and using the DATE function?

Typing a date directly is faster for single entries. The DATE function is useful when you have year, month, and day in separate cells and want to combine them, or when you're building dates with formulas. Both create the same date value — the method just depends on where your data comes from.

Can I format a date column to show only the month and year, like "March 2024"?

Yes. Select the date cells, right-click, choose Format Cells, select Date from the Category, and look for a format showing month and year. If you don't see one, click Custom, and type mmmm yyyy to display "March 2024" or mm/yyyy for "03/2024".