The simplest way to calculate days between dates

To find the number of days between two dates in Excel, subtract the earlier date from the later date. Excel stores dates as numbers, so when you subtract one date from another, you get the number of days between them as a whole number.

If your earlier date is in cell A1 and your later date is in cell B1, type this formula in an empty cell: =B1-A1. Press Enter, and Excel shows you the number of days. That's the entire process — no special function needed.

This works because Excel counts January 1, 1900 as day 1, January 2, 1900 as day 2, and so on. When you subtract, you're subtracting those underlying numbers from each other.

Key Takeaways

  • Subtract the earlier date from the later date using a simple formula like =B1-A1 to get the number of days between them.
  • Excel treats dates as numbers behind the scenes, so subtraction gives you a whole number of days automatically.
  • Use the DAYS function as an alternative: =DAYS(B1,A1) produces the same result and reads more clearly in your spreadsheet.
  • If your result shows as a date instead of a number, change the cell format to General or Number to see the actual day count.
  • To include both the start and end dates in your count, add 1 to your formula: =B1-A1+1.

Using the DAYS function for clarity

Excel has a dedicated function called DAYS that does the same calculation but makes your formula easier to read. Type =DAYS(B1,A1) where B1 is the later date and A1 is the earlier date. The result is identical to subtracting, but anyone looking at your spreadsheet later will immediately understand what the formula does.

The DAYS function takes two arguments: the end date first, then the start date. This order is opposite to subtraction, so pay attention to which date goes where. If you reverse them, you'll get a negative number instead of a positive one.

Both methods work equally well. Choose whichever feels more natural to you or matches the style your workplace uses.

When your result shows as a date instead of a number

Sometimes Excel displays your result as a date (like "1/15/1900") instead of showing "41" days. This happens because the cell is formatted as a date rather than a number. The calculation is correct — Excel is just displaying it the wrong way.

To fix this, right-click the cell with your result and select Format Cells. In the Format Cells window, click the Number tab. Under Category on the left, select General or Number, then click OK. Your cell now displays the day count as a number instead of a date.

If you're using a Mac, right-click the cell and choose Format Cells, then select Number from the Category list.

Counting both the start date and the end date

By default, the subtraction method counts the days between two dates but doesn't include both endpoints. For example, the days between January 1 and January 3 would show as 2 (January 2 and January 3), not 3.

If you need to count both the start and end dates as part of your total, add 1 to your formula: =B1-A1+1. Now January 1 through January 3 counts as 3 days. This matters when you're calculating things like the number of days someone worked (including their first and last day) or the length of a project from start to finish.

The DAYS function doesn't include the start date by default either, so use =DAYS(B1,A1)+1 if you need both dates counted.

Handling dates that are entered as text

If your dates are stored as text instead of actual date values, subtraction won't work. Excel will either show an error or give you an incorrect number. You can tell dates are text if they're left-aligned in their cells instead of right-aligned (numbers and dates are right-aligned by default).

To convert text dates to real dates, use the DATEVALUE function. If your text date is in cell A1, type =DATEVALUE(A1) in a new cell. This converts the text to a date that Excel recognizes. Then you can subtract normally.

Alternatively, select all the cells with text dates, go to the Data tab, and click Text to Columns. Click Next twice, make sure the column format is set to Date, and click Finish. Excel converts all the text dates in that column at once.

Calculating business days only

If you need to count only weekdays (Monday through Friday) and exclude weekends, use the NETWORKDAYS function. Type =NETWORKDAYS(A1,B1) where A1 is the start date and B1 is the end date. This counts all the working days between those two dates.

NETWORKDAYS also lets you exclude holidays. Add a third argument with a range of holiday dates: =NETWORKDAYS(A1,B1,C1:C10). If your holidays are listed in cells C1 through C10, Excel subtracts those days from the count even if they fall on a weekday.

This function is useful for calculating project timelines, work schedules, or any situation where weekends don't count as part of your total.

Frequently Asked Questions

Why does my formula show a negative number?

You've subtracted the later date from the earlier date instead of the other way around. If A1 is January 15 and B1 is January 1, then =A1-B1 gives you -14. Reverse the order to =B1-A1 to get 14 days instead.

Can I calculate days between dates in different cells across multiple rows?

Yes. Enter your formula in one cell, then click and drag the small square at the bottom-right corner of that cell down to copy the formula to other rows. Excel automatically adjusts the cell references for each row, so each row calculates its own date difference.

What if one of my dates is blank or missing?

Excel will show an error like #VALUE! or #NUM!. You can use the IF function to handle missing dates: =IF(OR(A1="",B1=""),"",B1-A1). This leaves the cell blank if either date is missing instead of showing an error.

How do I show the result as years, months, and days instead of just total days?

Use the DATEDIF function: =DATEDIF(A1,B1,"Y")&" years "&DATEDIF(A1,B1,"YM")&" months "&DATEDIF(A1,B1,"MD")&" days". This breaks down the time span into years, months, and remaining days. Replace "Y", "YM", and "MD" with other codes if you need different units.