Counting Weekends Off

**uhcord98** · 07-28-2011, 03:37 PM

I need help figuring out a formula.

For this example, a weekend off will be defined as a consecutive Saturday & Sunday off. So an employee that is scheduled not to work on August 6th & August 7th would have a weekend off.

Every person on my team needs to have at least 1 of these weekends off per month. So my main purpose of this is to ensure that I gave each employee one weekend off.

I would like a formula in the column beside Saturdays Off & Sundays Off that looked at the entire months schedule and showed me how many "weekends" each employee has off.

If this does not make sense let me know.

**ron2k_1** · 07-28-2011, 04:48 PM

So this is what I came up with from what I can follow from you, please see the attached.

These are my assumptions:

If Joe, doesn't work on the 3rd AND 4th, this is one weekend off. If in addition, he doesn't work on the 10th AND 11th, then he has a second weekend off, and if in addition to those two weekends off, he doesn't work on the 17th AND 18th, then he has a 3rd weekend off. And so forth and so forth.
If he has the first Sunday (28th on your example) and the last Saturday (1st on your table), these are not counted as they are not part of the same weekend.
If he is off only 1 day of the weekend, then it is not counted. Therefore, if Joe is off only for 17th but not the 18th, then he didn't have a weekend off.
If Joe was off the 3rd, 10th, and 17th, then he didn't have not even 1 weekend off, as these days were not part of the same weekend.

If the assumption are not right then, provide and answer to each of the above assumptions and we'll see if we can help you. The formulas are shown only from I3:I17.

Hope it helps,

**uhcord98** · 07-28-2011, 07:29 PM

Exactly what I was looking for! Many thanks!

**uhcord98** · 08-10-2011, 10:27 PM

hello, i had to make an update to this spreadsheet on the monthly tab to include "req off"

i looked at the formula that is counting weekends off by the word "off" and could not figure out how to make it count "off" & "req off" into the weekends off formula. any ideas?