How to Enter and Format Dates in Excel

The simplest way to add a date in Excel

Type the date directly into a cell using a format Excel recognizes — like 1/15/2024, 15-Jan-2024, or 2024-01-15. Press Enter, and Excel converts it to a date value. The cell will show the date, but behind the scenes Excel stores it as a number (the count of days since January 1, 1900), which is why you can do math with dates or sort them in order.

If Excel doesn't recognize what you typed as a date, it treats it as text instead. This matters because you won't be able to sort or calculate with text that looks like a date. The safest approach is to use a format with the month as a number or abbreviation — 1/15/2024 or 15-Jan-2024 — rather than spelling out the month name, since that depends on your computer's language settings.

You can also type =TODAY() into a cell to insert today's date automatically, or =NOW() to add today's date plus the current time. These formulas update every time you open the file.

Key Takeaways

Excel recognizes dates typed as 1/15/2024, 15-Jan-2024, or 2024-01-15, but the exact format depends on your computer's regional settings.
Use =TODAY() to insert today's date automatically, or =NOW() to add the current date and time.
If Excel treats your date as text instead of a date value, you won't be able to sort or calculate with it — check the cell alignment (dates are right-aligned by default, text is left-aligned).
You can change how a date displays without changing the actual date value — right-click the cell, choose Format Cells, and pick a different date format from the list.
To add a number of days to a date, use a formula like =A1+30 (where A1 contains a date) to get the date 30 days later.

When Excel doesn't recognize your date

If you type something that looks like a date but Excel treats it as text, the cell will be left-aligned instead of right-aligned (dates align right by default). This happens most often when you type a date in a format your computer doesn't expect — for example, if your computer is set to US English but you type 15/01/2024, Excel may read it as text because it doesn't match the MM/DD/YYYY pattern.

To fix this, you have two options. The first is to retype the date in a format your computer recognizes — use the month-number-first format if you're in the US (1/15/2024), or add a three-letter month abbreviation (15-Jan-2024) which works regardless of region. The second option is to use the DATE function: type =DATE(2024,1,15) where the order is always year, month, day. This formula works the same way on any computer.

Changing how a date looks without changing the date itself

Once Excel recognizes a cell as a date, you can display it in dozens of different formats without altering the actual date value. Right-click the cell, select Format Cells, and click the Number tab. Choose Date from the Category list on the left, then pick the format you want from the list — you'll see options like "1/15/2024", "15-Jan-2024", "January 15, 2024", and many others.

The format you choose is purely visual. If you change a cell from "1/15/2024" to "January 15, 2024", the underlying date value stays the same, and any formulas that use that cell will still work correctly. This is useful when you want dates to match a specific style for a report or when you need to show the full month name for clarity.

Adding days to a date with a formula

To calculate a new date by adding days to an existing date, use simple addition. If cell A1 contains a date, type =A1+30 to get the date 30 days later. You can subtract days the same way: =A1-7 gives you the date 7 days earlier. Excel handles the month and year changes automatically — if you add 20 days to January 15, you get February 4 without any extra work.

You can also add or subtract months and years using the DATE and MONTH functions together, though the syntax is more complex. For most everyday tasks, simple addition and subtraction work fine. If you need to add exactly 3 months to a date in A1, you could use =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)), but for quick calculations, =A1+90 (approximately 3 months) is often good enough.

Calculating the number of days between two dates

To find how many days apart two dates are, subtract one from the other. If A1 contains 1/1/2024 and B1 contains 1/15/2024, type =B1-A1 to get 14 (the number of days between them). The result is a plain number, not a date.

If the result shows as a date instead of a number (like "1/14/1900"), the cell is formatted as a date. Right-click it, choose Format Cells, select Number from the Category list, and click OK. Now it will display as 14.

Using TODAY() and NOW() for automatic dates

The =TODAY() function inserts today's date into a cell and updates it automatically every time you open the file. This is useful for documents where you want the current date to appear without typing it manually each time. The =NOW() function does the same thing but includes the current time as well (for example, 1/15/2024 2:30 PM).

Both formulas update every time the file recalculates, which happens when you open the file or press F9. If you need a date that stays fixed — one that doesn't change when you open the file tomorrow — type the date directly instead of using a formula.

Sorting and filtering by date

Once your dates are recognized as actual date values (not text), you can sort them in order. Select the column containing dates, go to the Data menu, and choose Sort. Excel will arrange them from earliest to latest or latest to earliest depending on which you choose. Filtering works the same way — click the filter button at the top of a date column and you can hide rows that fall outside a date range you specify.

This is why it matters whether Excel treats your dates as text or as date values. Text that looks like a date won't sort in chronological order — it will sort alphabetically instead, which puts "1/2/2024" before "1/15/2024" because it's comparing the characters, not the actual 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 instead of a date. Right-click the cell, choose Format Cells, select Date from the Category list, pick a format, and click OK. The underlying value hasn't changed — only how it displays.

Can I type a date with the month spelled out, like "January 15, 2024"?

Yes, if your computer's language is set to English. Excel will recognize it as a date. However, using a number or abbreviation (1/15/2024 or 15-Jan-2024) is safer because it works regardless of language settings, especially if you share the file with others.

How do I add the current date to a cell so it updates automatically?

Type =TODAY() into the cell. It will show today's date and update every time you open the file. If you want the date plus the time, use =NOW() instead.

What's the difference between =TODAY() and typing the date manually?

=TODAY() updates every time you open the file, so it always shows the current date. A manually typed date stays the same forever. Use =TODAY() for documents where you want the date to reflect when you're working on it, and type the date manually when you need it to stay fixed.

Can I add months or years to a date, or just days?

You can add days with simple math (=A1+30). For months and years, use the DATE function: =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) adds 3 months. For most everyday tasks, adding approximate days (like 90 for 3 months) is simpler and works fine.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.