Summary

To calculate the number of days between two dates you can subtract the later date from the earlier date. The result will be an integer that represent the days between dates. In the example shown, the formula in D6 is:

=C6-D6

The result is 365, since there are 365 days between Jan 1, 1999 and Jan 1, 2000.

Note: make sure you format the result with the General number format.

Generic formula

=later_date-earlier_date

Explanation 

Dates in Excel are serial numbers that start on 1/1/1900, which is 1.

In the example, the formula in cell D6 simply subtracts the numeric value of 1/1/1999 (36161) from the numeric value of 1/1/2000 (36526) to get a result of 365. The steps look like this:

=C6-B6
=36161-36526
=365

Working with today

If you need to calculate the number of days between an earlier date and today, you can use the TODAY function:

=TODAY()-earlier_date

To calculate the number of days between a later date and today, use:

=later_date-TODAY()

Note that TODAY will recalculate on an on-going basis. If you open the workbook at a later date, the value used for TODAY will update and you will get a new result.

The DAYS function

The DAYS function, introduced in Excel 2013, also calculates days between two dates.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.