Formula to calculate the number of days occurring in multiple date ranges for different individuals

[C_Har]

Hello,

I am trying to develop an Excel formula that allows me to count the number of days occurring within two date ranges for different individuals. Here is a sample of my dataset:

id	admDt	sepDt	admDur		id	dxDt-1	dxDt	admDurTot
1	16/01/2000	18/01/2000	2		1	24/01/2000	24/02/2000	28
1	22/01/2000	18/02/2000	27		2	30/03/2000	30/04/2000	0
1	20/02/2000	22/02/2000	2		3	03/02/2000	03/03/2000	0
1	24/02/2000	28/02/2000	4		4	31/12/1999	31/01/2000	0
5	19/05/2000	20/06/2000	32		5	20/05/2000	20/06/2000	31
8	25/12/1999	28/12/1999	3		6	13/11/2000	13/12/2000	0
8	18/01/2000	26/01/2000	8		7	14/04/2000	14/05/2000	0
8	28/01/2000	28/01/2009	3288		8	23/12/1999	23/01/2000	9
					9	09/02/2000	09/03/2000	0

<tbody>
</tbody>

For columns A-D, each row reflects an event (in this case, a hospital admission):
• id = individual's ID number
• admDt = date of admission
• sepDt = date of discharge
• admDur = duration (in days) of admission

Please note that multiple rows can relate to one individual (e.g., A2:E5).

For columns F-I, each row reflects one individual's data:
• id = individual's ID number
• dxDt-1 = date one month preceding diagnosis
• dxDt = date of diagnosis
• admDurTot = total duration (in days) of admission

Date ranges for each admission (admDt and sepDt) and individual (dxDt-1 and dxDt) can be different. While the date ranges for each event may span across months (e.g., cells A3:C3 or A9:C9), I am interested in calculating the number of days the person spent in hospital (admDurTot) in the month preceding their diagnosis (dxDt).

Please note that the values in admDurTot reflect those I would like to obtain, rather than those obtained from different formulae I have used.

I have tried variations of the SUMIFS formula:
• = SUMIFS($D:$D, $A:$A, $G2, $B:$B, ">=" & $H2, $C:$C, "<=" & $I2)
• = SUMIFS($D:$D, $A:$A, $G2, $B:$B, "<=" & $I2, $C:$C, ">=" & $H2)
• = SUMIFS($D:$D, $A:$A, $G2, $B:$B, ">=" & $H2, $C:$C, ">" & $H2, $C:$C, "<=" & $I2)

However, they are confounded by the fact that both admDt and sepDt often fall outside the range of interest (i.e., they are not between dxDt-1 and dxDt). For example, in the case of individual 1, the above formulae indicate that he spent 33 days in hospital in the month preceding his diagnosis. In reality, he spent 28 days in hospital during that period.

I have found little guidance on other websites. I have reviewed the following link; however, the formula provided is not quite appropriate for my needs.

http://www.mrexcel.com/forum/excel-questions/851273-counting-number-overlapping-days-between-multiple-date-ranges.html


Any assistance would be greatly appreciated.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

If a helper column is acceptable, maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	id​	admDt​	sepDt​	admDur​			id​	dxDt-1​	dxDt​	admDurTot​	Helper​
2​	1​	16/01/2000​	18/01/2000​	2​			1​	24/01/2000​	24/02/2000​	28​	0​
3​	1​	22/01/2000​	18/02/2000​	27​			2​	30/03/2000​	30/04/2000​	0​	25​
4​	1​	20/02/2000​	22/02/2000​	2​			3​	03/02/2000​	03/03/2000​	0​	2​
5​	1​	24/02/2000​	28/02/2000​	4​			4​	31/12/1999​	31/01/2000​	0​	1​
6​	5​	19/05/2000​	20/06/2000​	32​			5​	20/05/2000​	20/06/2000​	31​	31​
7​	8​	25/12/1999​	28/12/1999​	3​			6​	13/11/2000​	13/12/2000​	0​	3​
8​	8​	18/01/2000​	26/01/2000​	8​			7​	14/04/2000​	14/05/2000​	0​	6​
9​	8​	28/01/2000​	28/01/2009​	3288​			8​	23/12/1999​	23/01/2000​	9​	0​
10​							9​	09/02/2000​	09/03/2000​	0​
11​

Formula in K2 copied down (helper column)
=IF(A2="","",MAX(0,MIN(C2,1+INDEX(I:I,MATCH(A2,G:G,0)))-MAX(B2,INDEX(H:H,MATCH(A2,G:G,0)))))

Formula in J2 copied down
=SUMIF(A:A,G2,K:K)

Hope this helps

M.

 

[C_Har]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

Thanks Marcelo,

It seems to be working well!


C.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

You are welcome. Glad to help

M.

 

[C_Har]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

My apologies,

I have just found a large cluster of people (all toward the bottom of my spreadsheet) for whom the formula did not work. For example, one person had six hospital admissions and spent 80 days in hospital before she was diagnosed; however, when using the formula, her record states that she had six admissions but spent zero days in hospital. Results are similar for about 20% of my sample.


C.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

Probably i misunderstood what you want. I thought that when column C (sepDt) is less than column H (dxDT-1), as the row 2 in your data sample, no one day should be counted.

It would be helpful if you could provide examples when the formula did not work along with expected results

M.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

Date ranges for each admission (admDt and sepDt) and individual (dxDt-1 and dxDt) can be different. While the date ranges for each event may span across months (e.g., cells A3:C3 or A9:C9), I am interested in calculating the number of days the person spent in hospital (admDurTot) in the month preceding their diagnosis (dxDt).

I thought you wanted to count only the days inside the period comprised by dxDt-1 and dxDt.

M.

 

[C_Har]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

I am creating a dataset that will be used to develop a series of models that will predict and analyse trends in hospital admissions and other outcomes in a cohort of about 20,000 people. At present, I am trying to determine for each person the number of admissions and number of days spent in hospital one month, three months, six months, twelve months, and at any time pre- / post-diagnosis and pre- / post- a health intervention. I didn't want my question to be too demanding, so I mentioned the one month period only. I was hoping to have a formula developed for that time period (i.e., between dxDt-1 and dxDt), so I could then extrapolate it to all other time periods.

Below is a table comprising the four variables I have constructed to determine the number of admissions, days in hospital, and days at risk of being admitted to hospital in the one month before diagnosis. Note that the four columns are replicated for the other time periods (with relevant adjustments made to the formulae to account for different date ranges).

admPreDx1Nbr	admPreDx1Dur	Helper	admPreDx1Risk
=COUNTIFS($A:$A, $F2,$B:$B, "<=" & $L2,$C:$C, ">=" & $K2)	=SUMIF($A:$A, $F2,$AJ:$AJ)	=IF($A2="", "", MAX(0, MIN($C2, 1 + INDEX($L:$L, MATCH($A2,$F:$F, 0)))-MAX($B2, INDEX($K:$K, MATCH($A2,$F:$F, 0)))))	=$L2-$K2

<tbody>
</tbody>

The formulae are then copied down. Although some of the cell references are different to those in the table in my initial post, they are, in fact, consistent (my actual dataset has more columns).


Below are the data for two cases in my sample. Their respective diagnosis dates are as follows:

• 5420135 - 09/03/2000
• 5460705 - 18/10/2000

id <tbody> </tbody>	admDt	sepDt	admLeng
5420135	26/8/1997	19/9/1997	24
5420135	15/9/1998	9/10/1998	24
5420135	22/7/1999	1/9/1999	41
5420135	9/3/2000	28/4/2000	50
5420135	28/7/2000	6/9/2000	40
5420135	12/10/2000	11/1/2001	91
5420135	23/7/2001	22/8/2001	30
5420135	10/12/2001	24/12/2001	14
5420135	27/3/2002	16/4/2002	20
5420135	16/9/2002	30/9/2002	14
5420135	17/3/2003	10/4/2003	24
5420135	11/4/2005	4/5/2005	23
5420135	16/8/2005	6/9/2005	21
5420135	16/5/2006	25/5/2006	9
5420135	17/3/2008	10/4/2008	24
5460705	13/10/2000	18/10/2000	5
5460705	2/9/2003	10/9/2003	8
5460705	25/11/2003	8/12/2003	13
5460705	31/5/2007	12/6/2007	12
5460705	21/4/2009		59

<tbody>
</tbody>

By my count, their admissions data in the month preceding their diagnosis should be as follows (the helper column is blank because it is generated by using the formula):

id	admPreDx1Nbr	admPreDx1Dur	Helper	admPreDx1Risk
5420135	1	1		29
5460705	1	5		30

<tbody>
</tbody>

And their admissions data for all time preceding their diagnosis should be as follows:

id	admPreDxNbr	admPreDxDur	Helper	admPreDxRisk
5420135	4	90		15328
5460705	1	5		9046

<tbody>
</tbody>

However, the following data are generated when using the formula:

id	admPreDx1Nbr	admPreDx1Dur	Helper	admPreDx1Risk
5420135	1	0	24	29
5460705	1	0	0	30

<tbody>
</tbody>

id	admPreDxNbr	admPreDxDur	Helper	admPreDxRisk
5420135	4	0	24	15328
5460705	1	0	0	9046

<tbody>
</tbody>

The same issue is present for a many people in the sample; however, they seem to be in the bottom half of my spreadsheet only. That is, the formula seems to have worked for those people in the top half of my dataset.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

It's not easy to understand the complete scenario. So let's discuss just the counting in the previous month (dxDt-1 to dxDt).

You said

By my count, their admissions data in the month preceding their diagnosis should be as follows (the helper column is blank because it is generated by using the formula):

id	admPreDx1Nbr	admPreDx1Dur	Helper	admPreDx1Risk
5420135	1	1		29
5460705	1	5		30

<tbody>
</tbody>

It seems that the formula i suggested does exactly this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1 ​	id​	admDt​	sepDt​	admLeng​			Id​	dxDT-1​	dxDt​	admDurTOT​	Helper​
2 ​	5420135​	26/08/1997​	19/09/1997​	24​			5420135​	09/02/2000​	09/03/2000​	1​	0​
3 ​	5420135​	15/09/1998​	09/10/1998​	24​			5460705​	18/09/2000​	18/10/2000​	5​	0​
4 ​	5420135​	22/07/1999​	01/09/1999​	41​							0​
5​	5420135​	09/03/2000​	28/04/2000​	50​							1​
6​	5420135​	28/07/2000​	06/09/2000​	40​							0​
7​	5420135​	12/10/2000​	11/01/2001​	91​							0​
8​	5420135​	23/07/2001​	22/08/2001​	30​							0​
9​	5420135​	10/12/2001​	24/12/2001​	14​							0​
10​	5420135​	27/03/2002​	16/04/2002​	20​							0​
11​	5420135​	16/09/2002​	30/09/2002​	14​							0​
12​	5420135​	17/03/2003​	10/04/2003​	24​							0​
13​	5420135​	11/04/2005​	04/05/2005​	23​							0​
14​	5420135​	16/08/2005​	06/09/2005​	21​							0​
15​	5420135​	16/05/2006​	25/05/2006​	9​							0​
16​	5420135​	17/03/2008​	10/04/2008​	24​							0​
17​	5460705​	13/10/2000​	18/10/2000​	5​							5​
18​	5460705​	02/09/2003​	10/09/2003​	8​							0​
19​	5460705​	25/11/2003​	08/12/2003​	13​							0​
20​	5460705​	31/05/2007​	12/06/2007​	12​							0​
21​	5460705​	21/04/2009​	19/06/2009​	59​							0​

<tbody>
</tbody>

K2 copied down (Helper)
=IF(A2="","",MAX(0,MIN(C2,1+INDEX(I:I,MATCH(A2,G:G,0)))-MAX(B2,INDEX(H:H,MATCH(A2,G:G,0)))))

J2 copied down
=SUMIF(A:A,G2,K:K)

Try to adapt the formula to the other situations.
Good luck!

M.

 

[Marcelo Branco]

Re: Excel: Formula to calculate the number of days occurring in multiple date ranges for different individuals

A more complete version

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

1​

id​

admDt​

sepDt​

admLeng​

Id​

dxDT-1​

dxDt​

admPreDx1Nbr​

admDurTOT​

Helper1​

admPreDxNbr​

admPreDxDur​

Helper2​

2​

5420135​

26/08/1997​

19/09/1997​

24​

5420135​

09/02/2000​

09/03/2000​

1​

1​

0​

4​

90​

24​

3​

5420135​

15/09/1998​

09/10/1998​

24​

5460705​

18/09/2000​

18/10/2000​

1​

5​

0​

1​

5​

24​

4​

5420135​

22/07/1999​

01/09/1999​

41​

0​

41​

5​

5420135​

09/03/2000​

28/04/2000​

50​

1​

1​

6​

5420135​

28/07/2000​

06/09/2000​

40​

0​

0​

7​

5420135​

12/10/2000​

11/01/2001​

91​

0​

0​

8​

5420135​

23/07/2001​

22/08/2001​

30​

0​

0​

9​

5420135​

10/12/2001​

24/12/2001​

14​

0​

0​

10​

5420135​

27/03/2002​

16/04/2002​

20​

0​

0​

11​

5420135​

16/09/2002​

30/09/2002​

14​

0​

0​

12​

5420135​

17/03/2003​

10/04/2003​

24​

0​

0​

13​

5420135​

11/04/2005​

04/05/2005​

23​

0​

0​

14​

5420135​

16/08/2005​

06/09/2005​

21​

0​

0​

15​

5420135​

16/05/2006​

25/05/2006​

9​

0​

0​

16​

5420135​

17/03/2008​

10/04/2008​

24​

0​

0​

17​

5460705​

13/10/2000​

18/10/2000​

5​

5​

5​

18​

5460705​

02/09/2003​

10/09/2003​

8​

0​

0​

19​

5460705​

25/11/2003​

08/12/2003​

13​

0​

0​

20​

5460705​

31/05/2007​

12/06/2007​

12​

0​

0​

21​

5460705​

21/04/2009​

19/06/2009​

59​

0​

0​

Formula in L2 (Helper1) copied down
=IF(A2="","",MAX(0,MIN(C2,1+INDEX(I:I,MATCH(A2,G:G,0)))-MAX(B2,INDEX(H:H,MATCH(A2,G:G,0)))))

Formula in J2
=COUNTIFS(A:A,G2,B:B,"<="&I2,C:C,">="&H2)

Formula in K2
=SUMIF(A:A,G2,L:L)


Formula in P2 (Helper2) copied down
=IF(A2="","",MAX(0,MIN(C2,1+INDEX(I:I,MATCH(A2,G:G,0)))-B2))

Formula in N2
=COUNTIFS(A:A,G2,B:B,"<="&I2)

Formula in O2
=SUMIF(A:A,G2,P:P)

Remark: didn't understand the concepts (the logic to find the values you showed above) admPreDx1Risk (29 and 30?) and admPreDxRisk (15328 and 9046?)

M.