How to Add Dates in Excel So They Actually Calculate Right

Excel treats dates as numbers, so you have to format them correctly or they won't sort, calculate, or filter the way you expect

When you type a date into Excel, the program converts it to a serial number behind the scenes — January 1, 1900 is 1, January 2, 1900 is 2, and so on. Excel then displays that number as a date using whatever format you choose. This matters because if Excel doesn't recognize what you typed as a date, it treats it as text instead, and text won't sort in chronological order or work in date calculations. A column that looks like dates but sorts wrong is almost always a formatting problem, not a data problem.

The most reliable way to enter a date is to type it in a format Excel recognizes when ready: MM/DD/YYYY (for US English settings) or DD/MM/YYYY (for UK and many other locales). Once you press Enter, Excel converts it to its internal number and displays it using whatever date format is set for that cell. If you type something like "January 15, 2024" or "15-Jan-24", Excel usually recognizes it too, but the slash format is fastest and causes fewer mistakes across different computer settings.

Key Takeaways

Type dates in MM/DD/YYYY format (or DD/MM/YYYY depending on your location) so Excel recognizes them as dates, not text.
If a date column won't sort chronologically, the cells are probably formatted as text — select them, use Format Cells, and change the format to Date.
Use the TODAY() function to insert today's date automatically, or NOW() if you also need the time.
To calculate the number of days between two dates, subtract the earlier date from the later one in a formula like =B2-A2.
Excel's date serial system starts at January 1, 1900, so dates before that year cannot be entered directly.

Entering dates so Excel recognizes them

Start by clicking the cell where you want the date. Type the date using slashes: 01/15/2024 for January 15, 2024. Press Enter. Excel converts it to a date serial number and displays it in the default date format for your region — usually MM/DD/YYYY in the United States.

If you type a date in a format Excel doesn't recognize — like "January 15" without a year, or "15 Jan" with a space instead of a slash — Excel may treat it as text. Text dates won't sort in the right order and won't work in date math. You can tell a cell contains text instead of a date if it aligns to the left side of the cell by default (dates align right). To fix this, delete the entry and retype it using the MM/DD/YYYY format.

If you are entering many dates and want to avoid typing the year each time, type the first date in full (01/15/2024), then select that cell and the cells below it where you want dates. Use the Fill menu or drag the small square at the bottom right corner of the cell downward. Excel will recognize the pattern and fill in consecutive dates automatically.

Using TODAY() and NOW() to insert automatic dates

The TODAY() function inserts today's date and updates it automatically every time you open the file. Click a cell and type =TODAY() then press Enter. The cell will display today's date in your default date format.

If you need the time as well, use NOW() instead: type =NOW() and press Enter. This function inserts both the current date and time, updating whenever the file recalculates. Both functions take no arguments — you don't put anything inside the parentheses.

These functions are useful for tracking when a record was created or last updated. If you want a date that does not change, type it directly instead of using a function — functions update, but typed dates stay fixed.

Changing how dates display without changing the actual date

Once a cell contains a date, you can change how it looks without changing the date itself. Select the cell or cells with dates. Right-click and choose Format Cells, or press Ctrl+1 on Windows or Command+1 on Mac. Click the Number tab if it is not already selected.

In the Category list on the left, click Date. The middle panel shows different date formats: "1/15/24", "January 15, 2024", "15-Jan", and many others. Click the format you want and click OK. The dates now display in that format, but the underlying serial number has not changed, so sorting and calculations still work correctly.

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, then look for a Custom option at the bottom. You can type a format code like YYYY-MM-DD to display dates as 2024-01-15. Custom formats are powerful but require learning the code syntax — the built-in formats cover most everyday needs.

Calculating days between two dates

To find how many days lie between two dates, subtract the earlier date from the later one. Click a cell where you want the result. Type =B2-A2 (if the later date is in B2 and the earlier date is in A2) and press Enter. Excel displays the number of days between them.

This works because Excel stores dates as serial numbers, so subtracting one from the other gives you the difference in days. If the result shows as a decimal or a date instead of a whole number, the cell is formatted wrong. Right-click the cell, choose Format Cells, select Number from the Category list, set Decimal Places to 0, and click OK.

For more complex date math — like finding the number of months or years between two dates — use the DATEDIF() function. Type =DATEDIF(A2,B2,"D") to get days, =DATEDIF(A2,B2,"M") to get months, or =DATEDIF(A2,B2,"Y") to get years. The third argument in quotes tells Excel which unit to use.

Fixing dates that are stored as text

If you paste dates from another source or inherit a spreadsheet where dates are text, they will not sort or calculate correctly. You can tell because they align left in the cell instead of right, and sorting puts them in alphabetical order rather than chronological order.

To convert text dates to real dates, select the column with the text dates. Go to the Data menu and click Text to Columns. Click Next. On the next screen, make sure Delimited is selected and click Next again. On the final screen, click the column header to select the entire column, then click the Date dropdown and choose the format that matches your dates (like MDY for month-day-year). Click Finish. Excel converts the text to dates and reformats them.

If Text to Columns does not work, the dates may be in a format Excel does not recognize. In that case, you may need to retype them or use a formula to extract and reassemble the parts. For example, if dates are stored as "15-Jan-2024" but Excel is treating them as text, try selecting the column, using Find & Replace (Ctrl+H) to replace the hyphens with slashes, and then using Text to Columns again.

Common date problems and how to fix them

Dates display as numbers like 45000 instead of a date. This means the cell is formatted as a number instead of a date. Select the cell, right-click, choose Format Cells, select Date from the Category list, pick a format, and click OK.

A date column sorts in the wrong order, like 1/2/2024, 1/20/2024, 1/3/2024. The dates are stored as text. Select the column, go to Data > Text to Columns, click Next twice, make sure Date is selected in the Column Data Format section, and click Finish.

A formula like =B2-A2 returns an error or a strange result. Make sure both cells actually contain dates, not text that looks like dates. Click each cell and check the formula bar — if it shows the date in quotes or as plain text, it is text, not a date. Retype it in MM/DD/YYYY format or use Text to Columns to convert it.

TODAY() or NOW() is not updating. These functions update only when Excel recalculates the file, which happens when you open it or when you press F9 (or Ctrl+Shift+F9 to force a full recalculation). If you need a date that updates in real time, TODAY() and NOW() are not the right tools — they update only when the file recalculates, not continuously.

Frequently Asked Questions

Can I enter a date without the year and have Excel fill it in?

Excel will try, but the result depends on your system settings. If you type 01/15 without a year, Excel usually assumes the current year or the year 1900, which is not what you want. Always include the full four-digit year to avoid confusion: 01/15/2024.

What if I need to add a specific number of days to a date?

Type a formula like =A2+30 to add 30 days to the date in A2. Excel treats dates as numbers, so adding a whole number adds that many days. To add months or years, use the DATE function: =DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)) adds three months to the date in A2.

Why does my date show as 1/0/1900 or some other wrong date?

This usually means you typed something that Excel interpreted as a date but got wrong — like typing "1/0" instead of "1/15". Delete the entry and retype it carefully in MM/DD/YYYY format. If the problem persists, the cell may be formatted as Date but contain text — use Text to Columns to convert it.

Can I use dates before January 1, 1900?

Not directly. Excel's date system starts at January 1, 1900 (serial number 1), so you cannot enter dates before that year. If you need to work with historical dates, store them as text and handle them separately, or use a different tool designed for historical data.

How do I make a date field that shows the current date but never changes?

Type the date directly instead of using a formula: 01/15/2024. Typed dates stay fixed. If you want today's date but it should not change tomorrow, type TODAY() once, then when ready copy the cell, right-click, choose Paste Special, click Values, and click OK. This converts the formula result to a fixed date.

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.