To find range of dates overlapping for maximum input date ranges
- Thread starter Rajan
- Start date
navic
New member
- Joined
- Aug 27, 2013
- Messages
- 901
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2013
The maximum number of days for difference two dates
In cell M2 put the formula (copy across to N2)
In the Conditional Formatting put the formula for a cell range E3:K7
If you can do to add to the beginning (or someplace else) additional column in cell A2 put formula (copy down)
Code:
=D3-C3
Code:
=INDEX(C3:C7;MATCH(MAX(A3:A7);A3:A7;0);1)
Code:
=AND(E$2>=$C3;E$2<=$D3)Attachments
Hello navic
Thanks for your reply. I guess my question was not that clear, let me put it once again.
Requirement is:
Example Case1) To find range of dates, that have maximum overlap.
>> There are two date ranges that satisfy overlap condition, dates "15-Apr to 31-May" & "1-Aug to 2-Aug" have maximum overlapping i.e. 4 each
>> In case there are more than one range with max overlapping period, than overlapping period with max duration should be the ans.
>> Like in this Case 1, Answer should be "15-Apr to 31-May"
Example Case2) To find range of dates, that have maximum overlap.
>> There is only one range that satisfy overlap condition, dates "1-Aug to 2-Aug" i.e. 5
>> Like in this Case 2, Answer should be "1-Aug to 2-Aug"
Hope i have added some more clarity to the problem statement.
--
Rajan
Thanks for your reply. I guess my question was not that clear, let me put it once again.
Requirement is:
Example Case1) To find range of dates, that have maximum overlap.
>> There are two date ranges that satisfy overlap condition, dates "15-Apr to 31-May" & "1-Aug to 2-Aug" have maximum overlapping i.e. 4 each
>> In case there are more than one range with max overlapping period, than overlapping period with max duration should be the ans.
>> Like in this Case 1, Answer should be "15-Apr to 31-May"
Example Case2) To find range of dates, that have maximum overlap.
>> There is only one range that satisfy overlap condition, dates "1-Aug to 2-Aug" i.e. 5
>> Like in this Case 2, Answer should be "1-Aug to 2-Aug"
Hope i have added some more clarity to the problem statement.
--
Rajan