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, it is useful if you first understand how dates are stored in Excel.

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.

The numbering system used for Excel dates varies with different settings and computer systems, but most commonly:

1=January 1st, 1900
2=January 2nd, 1900
.
.
.
42369=December 31st, 2015

... and so on.

Therefore, when you want to calculate a date difference in Excel, you can simply subtract the earlier date from the later date, in the same way as you would subtract any other numeric values in Excel. The resulting value tells you the number of days between the two dates. 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

In the example above, the date in cell B1 is internally represented by the number, 42370 and the date in cell B2 is internally represented by the number 42432. Subtracting these two numbers gives 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 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  >>