Summary

To calculate days between two dates, ignoring year values, use the DATEDIF function. In the example shown, the formula in D6 is:

=DATEDIF(B6,C6,"yd")

Generic formula

=DATEDIF(start_date,end_date,"yd")

Explanation 

The DATEDIF function can handle a variety of "date difference" calculations to calculate the difference between two dates in years, months, and days. DATEDIF takes 3 arguments: start date, end_date, and "unit", which controls which result is returned.

In this case, we want days ignoring years so we supply "yd" for unit. (For the full list of options, see the DATEDIF page).

Once configured, the function is fully automatic and returns a result in the unit requested.

Related formulas

Excel formula: Get days between dates

Get days between dates

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 =...
Excel formula: Get workdays between dates

Get workdays between dates

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates. For example, in the screenshot shown, the formula in D6 is: =NETWORKDAYS(B6,C6...
Excel formula: Get months between dates

Get months between dates

In this example, the goal is to calculate the number of months between two valid Excel dates . This is a curiously tricky problem in Excel because the number of days in a month varies, and the rules about how a whole month might be calculated are not obvious. In addition, there is not a modern...

Related functions

Excel DATEDIF function

DATEDIF Function

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work...
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.

Formulas

Functions