Summary

To count the number of days between two dates you can use the DAYS function or just subtract the start date from the end date. In the example shown, the formula in D6 is:

=C6-B6

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

Note: To see the result as a number and not a date, format the result with the General number format.

Generic formula

=end_date-start_date

Explanation 

Dates in Excel are serial numbers that start on 1/1/1900, which is represented by the number 1. In the example shown, 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

To count 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 ongoing basis. If you open the workbook at a later date, the value used for TODAY will update and you will see a new result.

The DAYS function

The DAYS function calculates the number of days between two dates using a start date and an end date. With a start date in A1 and end date in A2:

=DAYS(A2,A1)

Will return the same result as:

=A2-A1

Both dates must be valid Excel dates or text values that can be coerced to dates. If start and end dates are reversed, DAYS will return a negative number.

Workdays between dates

The formulas above count all days between two dates, to calculate working days between dates, see the NETWORKDAYS function or NETWORKDAYS.INTL function. See this article for more details.

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.