Count Amount of Days Support Received in a Set Period

Hi all,

I have attached an example spreadsheet for you. This is similar to some of my other questions, but need to count days rather than items.

The spreadsheet is a schedule for support hours given to individuals in a (on the whole) 28 day period; the period start date is shown in B6, and the end date is in F6 (these are taken from the second worksheet, 16-17 pay period dates). Column D (from row 13 down) shows the date an individual originally started support; this could be two years ago, or a few days into the period (see above). Added to this, we also have a possible end date (e13 downwards). The end date will only be entered if it is within the period dates (i.e. you won't get an end date entered that is in the future, or from the previous period). If no date is entered, it will automatically enter (and hide) the day after the end of the period, enabling the formula to work (see formula and conditional formatting in cell E13 onwards where there is no date entered).

What I need to work out is a formula that will tell me how many days support an individual has recevied in the relevant period (result in I13 onwards). So for example, if the period is 1st April to 1st May, and someone doesn't start till 30th April, I need the resulting days received to show as 2 (up to and including the end date); this in itself is easy, I just enter =(+E16-D16)+1. However, start dates can be before the period start date, so if I use this formula for someone who start date is 1/8/15 (sorry, UK date format, mean 1st August 2015!), and they don't have an end date, I get the result 276 (I14), when it should be 31! Same goes for the same person who's start date is 1st August 2015, but they ended support on 2nd April; I need to get the days result to show 2. I have also tried a sum product (I13), but this won't count days.

Help!

Thanks so much! I did have an epiphany on the way home from work today that I might need to have formula hidden in other cells to work this out, but you have completly nailed it! As the quote goes "you are a gentleman (or gentlewoman) and a scholar"!