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:

[TABLE="class: outer_border, width: 600"]
<tbody>[TR]
[TD]id
[/TD]
[TD]admDt
[/TD]
[TD]sepDt
[/TD]
[TD]admDur
[/TD]
[TD]
[/TD]
[TD]id
[/TD]
[TD]dxDt-1
[/TD]
[TD]dxDt
[/TD]
[TD]admDurTot
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]16/01/2000
[/TD]
[TD]18/01/2000
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]24/01/2000
[/TD]
[TD]24/02/2000
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]22/01/2000
[/TD]
[TD]18/02/2000
[/TD]
[TD]27
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]30/03/2000
[/TD]
[TD]30/04/2000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]20/02/2000
[/TD]
[TD]22/02/2000
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]03/02/2000
[/TD]
[TD]03/03/2000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]24/02/2000
[/TD]
[TD]28/02/2000
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]31/12/1999
[/TD]
[TD]31/01/2000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]19/05/2000
[/TD]
[TD]20/06/2000
[/TD]
[TD]32
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]20/05/2000
[/TD]
[TD]20/06/2000
[/TD]
[TD]31
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]25/12/1999
[/TD]
[TD]28/12/1999
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]6
[/TD]
[TD]13/11/2000
[/TD]
[TD]13/12/2000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]18/01/2000
[/TD]
[TD]26/01/2000
[/TD]
[TD]8
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD]14/04/2000
[/TD]
[TD]14/05/2000
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]28/01/2000
[/TD]
[TD]28/01/2009
[/TD]
[TD]3288
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD]23/12/1999
[/TD]
[TD]23/01/2000
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]9
[/TD]
[TD]09/02/2000
[/TD]
[TD]09/03/2000
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[C_Har]

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

A very delayed response; I moved to R to conduct my analysis, but returned to this to assist a colleague using Excel. The formula functions correctly; the reason I could not use it initially was that I matched it to G:G, rather than A:A.

Many thanks for your help.