Calculating the number of days within a 12-month period

Judie, you better know the rules - if that's more correct return the formula back.

I'm not sure that affects 12-months calculations. The only point here if the person was, for example, in the trip ended at Sep 04 previous year and starts new trip this year from Sep 04 that will be 2 days in 12 months period. If that's not correct may remove +1 within the formulas for calculations.

Hi Sergio!

Hope you had a great long weekend!  Thanks for updating the spreadsheet.  I am blown away by your capabilities of figuring out these formulas I am looking for, let alone that Excel had capabilities such as this.  I thought I could figure my way around Excel if need be, but I am a complete novice when it comes to what you can do with Excel, it's amazing!

So I took a look at your update.  So to calculate the number of days away, OHIP counts one day as a sleep day (if you are to sleep overnight). So for the first range Nov 8, 2015 to Nov 12, 2015, it would actually be 4 days away (4 nights vs 5 days).  So for column C, would i change it back to just the =DAYS(B3,A3) formula? And does it affect the formulas for 12-month period to Start or to End?

Hi Judie,

Here is the the update.

Dates in between are

=DAYS(B3,A3)+1 - (A3=B2)

Cumulative days in 12-months period I'd suggest to calculate on the start and on the end of each new trip. If to calculate in between when we need helper calendar table with all calendar dates and calculate previous 12 months for each day in calendar. Not sure that's really needed.

For the dates on the start

=SUMIF($A$2:A2,">"&EDATE($A3,-12),$C$2:$C2)+IFERROR(SUMPRODUCT(($B2:$B$3-EDATE(A3,-12)+1)*($A2:$A$3<=EDATE(A3,-12))*($B2:$B$3>=EDATE(A3,-12))),0)

where first part of the formula calculates cumulative days out for all trips which started later than 12 months ago. Second part calculates overlapped trips.

Similar for the days on the end of the trip

=SUMIF($A$3:A3,">"&EDATE($B3,-12),$C$3:C3)+SUMPRODUCT(($B$3:$B3-EDATE(B3,-12)+1)*($A$3:$A3<=EDATE(B3,-12))*($B$3:$B3>=EDATE(B3,-12)))

Please see attached.

And I sorted your table in chronological order.

Leaving work Sergei, so I will look out for your response on Tuesday.  Thanks so much, you are amazing and have a great long weekend!