Calculate a Date Difference in Excel

Calculate the Number of Days Between Two Dates in Excel

Use a Subtraction Formula to Calculate the Number of Days Between Two Dates

If you want to calculate the number of days between two Excel dates, this can be done by simply subtracting the earlier date from the later date.

For example, if the start date is stored in cell B1 and the end date is stored in cell B2, the number of days between these two dates is calculated by typing the following into any Excel cell:

=B2-B1

This method works because Excel dates are internally stored as simple integers. It is only the formatting of an Excel cell that causes its value to be displayed as a date, rather than as a simple number.

This is shown in the example below.

Example of Using a Subtraction Formula to Calculate the Number of Days Between Two Excel Dates

Cell B3 of the following spreadsheet shows a simple Excel date difference calculation. The calculation simply subtracts the date in cell B1 from the date in cell B2.

 Formulas:
 AB
1Start Date:01/01/2016
2End Date:03/03/2016
3Date Difference (in days):=B2-B1
 Results:
 AB
1Start Date:01/01/2016
2End Date:03/03/2016
3Date Difference (in days):62

The above subtraction formula returns the result 62.

I.e. There are 62 days between the dates 01/01/2016 and 03/03/2016.

Use the Excel Days Function to Calculate the Number of Days Between Two Dates

The Excel Days Function also calculates the number of days between two supplied dates. However, the function was only introduced in Excel 2013, so is not available in earlier versions of Excel.

The syntax of the Days function is:

DAYS( end_date, start_date )

where the end_date and start_date arguments are the two dates that you want to calculate the difference between.

Excel Days Function Example

Cell B3 of the following spreadsheet shows the Excel Days function, used to calculate the number of days between the two dates in cells B1 and B2:

 Formulas:
 AB
1Start Date:01/01/2016
2End Date:03/03/2016
3Date Difference (in days):=Days( B2, B1 )
 Results:
 AB
1Start Date:01/01/2016
2End Date:03/03/2016
3Date Difference (in days):62

The above Days function returns the value 62.

I.e. There are 62 days between the dates 01/01/2016 and 03/03/2016.

Calculate the Number of Years Between Two Dates in Excel

The Excel Yearfrac Function calculates the number of years between two supplied dates. The result is returned as a decimal value.

Excel Yearfrac Function Example

Cell B3 of the following spreadsheet shows the Excel Yearfrac function, used to calculate the number of years between the two dates in cells B1 and B2:

 Formulas:
 AB
1Start Date:01/01/1983
2End Date:03/03/2016
3Date Difference (in years):=Yearfrac( B1, B2 )
 Results:
 AB
1Start Date:01/01/1983
2End Date:03/03/2016
3Date Difference (in years):33.17222222

The above Yearfrac function returns the value 33.17222222.

I.e. There are 33.17 years between the dates 01/01/1983 and 03/03/2016.


The Yearfrac function is also useful if you want to Calculate Age in Excel.


See also the page on Calculating Time Difference in Excel  >>