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