formula to count matching days between two date ranges (for largelist of names)

  • Thread starter Thread starter Dave K
  • Start date Start date
D

Dave K

I have two sheets. Sheet 1 and sheet 2.

Sheet1: has a list of Social Security Numbers, followed by a date
Range (i.e., start date field and end date field). The date ranges are
discrete (i.e., do not overlap) with each other for any given name.

SHEET 1
Name A Start Date A End Date
SSN1 1/1/09 1/5/09
SSN2 4/1/09 4/15/09
SSN1 3/2/10 3/15/10
SSN3
ETC...

Sheet 2 has the same information (i.e., multiple records with SSNs,
followed by date ranges

Name B Start Date B End Date RESULT NEEDED
SSN1 1/1/09 1/31/09 5
SSN2 4/1/09 4/31/09 15
SSN1 12/2/10 12/15/10
SSN3
ETC...

Both sheets can have more than one name in multiple rows (but discrete
date ranges for that same name).

What i need to display is the count of matching days for each date
range shown (for each unique SSN) in Sheet2!. The Result Needed column
above shows a sample of what i am aiming for. That is, i need to
compare the date range on Sheet1 to the date ranges on Sheet2 (for a
given name) to see if there is a match, and then display the count of
matching days adjacent to that date range in question in Sheet2.

Is anyone aware of a formula (or series of formulas, or macro) that
could produce that result for all records in sheet2?

Thanks for any suggestions.
 
Hello !

Hope I have understood your case.

For the example, I assumed your data in Sheet1 begin at row 2 until row 4
(ie A2:C4)

Name your Data in Sheet1:
Name1 for A2:A4
StartDate1 for B2:B4
EndDate1 for C2:C4

I assumed your data in Sheet2 begin at row 2
and are in columns A:C

Put the following formula into cell D2 in Sheet2
=SUMPRODUCT(IF(IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)<0,0,1+IF(EndDate1<=C2,EndDate1,C2)-IF(StartDate1<B2,B2,StartDate1)),--(Name1=A2))

This is an array formula. You must validate this formula with the
combination of the three keys : Ctrl+Shift+Enter,
and not with the single key Enter. If the validation is correct the formula
will be inclosed with { }

Drag this formula down to the other rows of your data in column C.

Hope it will help you (not sure)



"Dave K" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
Notice:
There should be no empty date in your data.
Otherwise the formula will return a wrong result.
 
Back
Top