Stay within month knowing admission date and discharge date

Question

I need to work out how many days patients were in the hospital each month using the admission date and discharge date data.

I have a formula I need to create which has many possibilities. Example rows (US formatted dates):

Admission Date  |Discharge Date  |Stay within month Oct 2016| Stay within month Nov 2016|  Stay within month Dec 2016|
11/03/2016      |11/17/2016      |  0                       | 15                        |  0                         | 
10/02/2016      |12/17/2016      |  22                      | 30                        |  17                        |
12/03/2016      |01/01/1990      |  0                       | 0                         |  19                        |
09/03/2016      |01/01/1990      |  31                      | 30                        |  31                        |
11/05/2016      |01/01/1990      |  31                      | 5                         |  0                         |
12/21/2016      |01/01/1990      |  31                      | 30                        |  21                        |                      

01/01/1990 means that the patient is still in the hospital. I can't type all the dates but, these columns will extend all the way to the month Dec 2018.

There are over 100 records so you can imagine why I want to automate this.

Appreciate any help!

Answer 1

Change your Stay within month Oct 2016 to the first of each month, 10/01/2016. Then you can format the Date MMM YYYY

Then put this in C2 and copy over and down:

=SUMPRODUCT((ROW(INDEX($A:$A,$A2):INDEX($A:$A,IF($B2=DATE(1990,1,1),TODAY(),$B2)))>=EOMONTH(C$1,-1)+1)*(ROW(INDEX($A:$A,$A2):INDEX($A:$A,IF($B2=DATE(1990,1,1),TODAY(),$B2)))<=EOMONTH(C$1,0)))