Forum Discussion
Calculating the number of days within a 12-month period
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.
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!!
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?
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!
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.