The fastest way to enter a date in Excel

Type the date the way you normally write it — 1/15/2024 or January 15, 2024 — and Excel recognizes it as a date, not text. Press Enter. Excel stores it as a number behind the scenes (so you can do math with it later) but displays it as a date.

If Excel doesn't recognize what you typed as a date, it treats it as plain text instead. This happens most often when you use a format Excel doesn't expect, like 15-Jan-2024 in a region set to US English. The safest formats are 1/15/2024, 01/15/2024, or January 15, 2024.

Once a date is entered correctly, you can sort by it, filter by it, or use it in formulas. Text that looks like a date cannot do any of those things.

Key Takeaways

  • Type dates in a standard format like 1/15/2024 or January 15, 2024, and Excel recognizes them automatically.
  • If Excel treats your date as text instead, delete it and retype it in a format your region expects, or use the Format Cells dialog to tell Excel what format you are using.
  • You can change how a date looks on screen without changing the actual date — right-click the cell, choose Format Cells, and pick a different date format from the list.
  • Use TODAY() to insert today's date automatically, or NOW() to insert today's date and time; both update every time you open the file.
  • Dates stored correctly in Excel can be sorted, filtered, and used in formulas to calculate days between dates or find dates in the future.

When Excel treats your date as text instead

A date entered as text looks like a date but behaves like words. Excel will not sort it with other dates, will not let you do math with it, and will not let you filter by date range. The cell usually shows a small green triangle in the corner as a warning.

This happens when you type a format Excel does not recognize for your region, or when you paste a date from another program. The fix is to delete the cell and retype it in a format your region expects. In the United States, that is usually 1/15/2024 or January 15, 2024. In many European countries, it is 15/1/2024 or 15 January 2024.

If you have many cells with dates stored as text, select them all, then go to the Data tab and click Text to Columns. Click Next twice, then click Finish. Excel converts them to real dates.

Changing how a date looks without changing the date itself

Once a date is entered correctly, you can display it any way you want without changing the actual date. Right-click the cell, choose Format Cells, and click the Number tab. Select Date from the Category list on the left.

The Format list shows you options: 1/15/2024, Tuesday, January 15, 2024, 15-Jan, Jan-24, and many others. Pick the one you want and click OK. The date itself does not change — only how it appears on screen.

You can also create a custom format if none of the built-in ones match what you need. In the same Format Cells dialog, select Custom from the Category list and type a format code. For example, mmmm d, yyyy displays January 15, 2024.

Using formulas to insert dates automatically

TODAY() inserts today's date into a cell. Type =TODAY() and press Enter. The cell shows today's date and updates automatically every time you open the file. This is useful for tracking when something was created or last updated.

NOW() does the same thing but includes the time as well. Type =NOW() and press Enter. The cell shows the current date and time.

You can also use formulas to calculate the number of days between two dates. Type =B2-B1 where B1 and B2 are cells containing dates. Excel shows the number of days between them. This works because Excel stores dates as numbers behind the scenes.

Adding dates to multiple cells at once

If you need to fill a column with dates in order — January 1, January 2, January 3, and so on — type the first date in a cell, then type the second date in the cell below it. Select both cells, then grab the small square at the bottom right corner of the selection and drag down. Excel continues the pattern.

You can also use the Fill Series feature. Type the first date, select the range where you want the dates to go, then go to the Sheet tab (or Edit tab in older versions), click Fill, and choose Series. Tell Excel whether you want to add days, months, or years, and how many to add each time.

Sorting and filtering by date

Dates entered correctly can be sorted from oldest to newest or newest to oldest. Click any cell in your date column, then go to the Data tab and click Sort A to Z (oldest first) or Z to A (newest first).

You can also filter by date range. Click the Data tab, then click Filter. A dropdown arrow appears in the header of each column. Click the arrow in your date column, then click Date Filters and choose the range you want — Last Month, This Year, Between Two Dates, and others.

These features only work if your dates are stored as actual dates, not as text. If sorting or filtering does not work, go back and check whether Excel is treating them as text.

Common mistakes and how to fix them

Typing a date with a leading apostrophe — '1/15/2024 — forces Excel to treat it as text. Delete the apostrophe and retype it. This sometimes happens when you paste from another program.

Entering a date in a format your region does not expect — like 15/1/2024 in the United States — may cause Excel to swap the month and day or reject it as text. Check your region settings or retype the date in a format you know Excel recognizes.

Leaving a cell blank when you meant to enter a date, then using that cell in a formula, can produce unexpected results. Formulas treat blank cells as zero, which Excel may interpret as December 30, 1899. Always fill in date cells, even if you use a placeholder like today's date and plan to change it later.

Frequently Asked Questions

Can I type just the month and year without a day?

Yes. Type 1/2024 or January 2024 and Excel recognizes it. It stores it as the first day of that month (January 1, 2024) but you can format it to display only the month and year. Right-click, choose Format Cells, select Date, and pick a format that shows only month and year.

What if I need to enter a date from a different year?

Type the full date including the year: 1/15/2020 or January 15, 2020. If you type only the month and day, Excel assumes the current year. You can also type a two-digit year — 1/15/24 — and Excel interprets it as 2024 (or 1924 if you type a year before 30).

How do I calculate how many days old something is?

Use the formula =TODAY()-B1 where B1 is the date you want to measure from. The result is the number of days between that date and today. If you want the result in years instead, use =DATEDIF(B1,TODAY(),"Y").

Can I enter a time along with the date?

Yes. Type 1/15/2024 2:30 PM and Excel stores both the date and time. You can format it to show only the date, only the time, or both. Right-click, choose Format Cells, select Date or Time, and pick the format you want.

Why does my date show as a number like 45000?

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