Determine longest date range, from two date ranges - Excel

Question

I have the following excel table
------A ----------------- B ----------------------C ------------------------D
1 --First--------------Last-----------------Start Date--------------End Date
2 --John--------------Smith-------------10/09/2014------------24/11/2014
3---John--------------Smith--------------20/11/2014------------31/01/2015

(Occasionally I have duplicate names on a spread sheet). I am creating a formula which determines the number of days between the earliest start date and latest end date from two date ranges. Ultimately the answer from this table should be C2 - D3 (143 days).

I have nearly finished the formula, but I am stuck with the nested IF ELSE logic in between. It is as follows.

=IF(AND($C3>$C2,$D3<$D2), DATEDIF($C3,$D3,"d"),
IF($C3>$C2, DATEDIF($C3,$D2,"d"),
IF($D3<$D2, DATEDIF($C2,$D3,"d"), DATEDIF($C2,$D2,"d"
))
)) 

With this I get 4 days, which is using C3 - D2. This means the statement is terminating after the True condition in the second IF statement. What would I need to add to ensure it checks the logic in the third IF statement (Is D3 a later date than D2).

Answer 1

Using your provided example, this should work for you:

=MAX($D2:$D3)-MIN($C2:$C3)