Here are are simply subtracting an earlier date from a later date. To figure out what dates to use for each date range comparison, we use MIN to get the earliest end date, and MAX to get the latest end date.
We add 1 to the result to make sure we are counting "fence posts" and not "gaps between fence posts" (analogy from John Walkenbach from the Excel 2010 Bible).
Finally, we use the MAX function to trap negative values and return zero instead. Using MAX this way is a clever way to avoid using IF.
T calculate the number of days between two dates you can simply subtract the older date from the newer 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...
The Excel MIN function returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.