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