Calculating the number of days within a 12-month period

[Judie null]

Hi there!

I have created a spreadsheet that displays the trips that my bosses have gone on, including the date range and the location followed by a third column of the actual # of days in the date range for the trip.  What we are trying to do is calculate the number of days within a 12-month period so that we never go over the amount of days allowed to still be covered by OHIP.  We are only allowed 212 days out of the province in order to still retain your OHIP coverage.  So I am looking for a formula that will calculate in a fourth column, the total number of days taken so far within a 12-month period and allow for updates as we continue to enter future trips into the spreadsheet so that we are always aware of what the current-to-date total is at any given time.  Keep in mind that each new row is not necessarily a new month as they may take more than one trip in a month so that there may be 3 rows allocated to 3 different trips all in the same month.  Is there a formula that will make this all possible?

Much thanks in advance for your time!

[#2]

Hi Judie,

If you split first column on two with start/end day like this

the formula could be

=SUMIF($A$2:A2,">="&EOMONTH($A2,-12),$D$2:D2)

and it depends on how do you calculate 12th months, from first day of the month, from last day of the trip or so.

In any case both could be adjusted (if both dates in one column and 12 months calculations), above is just an idea.

Attached.

[#3]

Thanks Sergei so much for the help!  I'm going to try it right now and see if it works with my data!  Will let you know how it turns out!

[#4]

Hi Sergei,

So I tried the formula you gave me and it seems to work. The only thing I can see is that it calculates the number of days between the end date and the start date which is then used for the formula. But this does not count the actual day they return (or leave).  For example, in my spreadsheet it has the number of days between the start & end dates for Column C but it either doesn't include the first day they leave or the day they return.  So then I calculated manually the actual number of days away (which includes the both the day they leave and the date they return) and entered this value into column D. Now, I'm not sure if this is how they would calculate the number of days away or if they would do it as the number of days between.  If it's calculating both the start & end date towards OHIP, then the formula leaves out the extra day.  Is there a way to account for this?  Am I making any sense to begin with? :) 

However, I do believe you are on to something!! 

[#5]

Hi Judie,

In our country we calculate day leave/day return as one day. If in your case these are two days you may use 

=DAYS(B3,A3)+1

or simply

=B3-A3+1

The only that's not clear why do you calculate the trip Jun 08-09, 2017 as one day and other similar trips as two days.

Second part is more complex. To be sure I understood correctly - if the person was in the trip on Nov 6-12, 2017 (7 days) and the next and only trip is on Nov 8-10, 2018 (3 days) when 12 months away is 6 days on the end of second trip: Nov 10-12, 2017 + Nov 8-10, 2018. Like this?

[#6]

Hi Sergei,

The only reason I calculated the June 8-9, 2017 trip as one day is because I already included June 8 in the trip prior (May 29 - June 8, 2017).  There were a few trips where it overlapped.  I separated them just because they went to a different city but maybe that just confuses things and I should just put the start of when they left Ontario and then when they returned.  i can adjust for that to make things easier.

In regards to the second part, that is exactly where I get confused as well.  It just states that you can be away for 212 days outside Ontario in a 12-month period.  So do I just account for the start of november 2017 to end of october 2018 or do I start from the actual date (eg. Nov 10, 2017 to Nov 10, 2018).  This is my confusion, on top of figuring out a formula lol.

Thanks again so much for your time and help!

[#7]

I see Judie, thank you. Thus for the first part we may check if last day of the previous trip is the same as first date of next one and make the correction.

As for the second part I'm not familiar with OHIP rules, not sure what's the logic shall be here. Some insurances work if the person is within the limit before trip starts, some work from day to day. I'd try to modify the formula, but business rule is on your site.

[#8]

Hi Sergei,

so I was able to clarify with Service Ontario that it's day to day.  Will I have to modify the formula you came up with?  I only ask instead of trying to figure it out myself, cuz your formula is heavy duty lol

[#9]

Judie, I'll modify the formula

[#10]

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!

[#11]

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.

[#12]

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?

[#13]

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.