Cutting a length of time into month segments

Hello,

I am trying to figure out how to get something called monthly census for a nursing home. I have a list(fairly long) of start dates and length of stays. Ig 1/1/15 stayed for 45 days. I need to allocate 31 days to Jan and 14 days to Feb. I would do it for the next one and keep a running tally of for the months involved. I attached a sample and am looking to fill the cells highlighted yellow. (First Sheet)

I've been able to break it out using formulas, but the output is a distribution for each individual patient vs month and then I use a pivot table to aggregate. The problem with this solution is that I need everything to update automatically when I place data into the tool I am making. (Second Sheet)

Any thoughts on improving on my knuckle dragging solution would be appreciated.

Thanks!

See attached file - hopefully it is fairly obvious to you what is happening, but come back if you need further explanations.

Hope this helps.

Pete

This makes sense, but I was hoping for something without the huge place holder table. It does the trick of automatically updating though.

Thanks,

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete