Excluding vacation days in calculations

[kirash4]

I'm getting ever so close to figuring things out with my Excel project, all thanks to the excellent help I've received here. Now I'm on to doing additional calculations based on set vacation days. So this is the jist of the workbook:

1. Calculate the last payday in any given month - this could be either a Tuesday, Wednesday, or Friday
Based on the above formula, I now have a "work period", basically the last payday from the previous month to the day prior to the last payday in the current month.
2. One person works every day in that "work period"
3. One person only works on Monday and Tuesday, and every other Sunday (Sunday must be an "even" WEEKNUM)

So far I've gotten these done. With the help of AliGW, I was able to figure out how many "working" Sundays are in that "work period". Now I'm stuck on figuring out the vacation days. These are set days for various events. The way they apply is:

a) For the one person working every possible day, ALWAYS subtract the amount of vacation days for that "work period"
b) For the person only working part of the time, ONLY subtract the vacation day if it happens to fall on one of their work days (Mon, Tues, or the Sunday they work)

The attached workbook has a list of the vacation days, and the current state of the calculations as I've done them. I have two questions:

1. Looking at the formula for the vacation days, I don't entirely understand why I had to do a +1 to get the correct number. I'm using NETWORKDAYS.INTL to filter things out based on the list of vacation days, and subtracting it from the total work days. But somewhere I am losing what's inclusive and what's exclusive in the date ranges and doing the +1 seems to fix that. If someone could explain that to me I would appreciate it.

2. How can I calculate when a vacation day falls on any of the workdays for Person 2 and only subtract those? If a vacation day falls on a day they're not working, it doesn't need to get subtracted.

I hope I explained this well enough. If not, please ask for clarifications and I will attempt to explain again, or in a different way, if possible.

[AliGW]

In O10 copied down:

=SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<=I10))

[kirash4]

Ok, that is a much easier way of doing the calculations for how many vacation days there are in the given work period. I feel like I'm always overthinking things. Now, how do I work out the 2nd part: only subtracting vacation days from Person 2 if it falls on one of their workdays?

[AliGW]

Try this:

=SUM(J10:L10)-SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<=I10)*(WEEKDAY($C$5:$C$14,1)<>2)*(WEEKDAY($C$5:$C$14,1)<>4))-SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<=I10)*(WEEKDAY($C$5:$C$14,1)=1)*(MOD($V$5:$V$14,2)=0))

[kirash4]

Hi AliGW, once again, thank you for your response. Before I try that, I'd like to understand how you approach this. In my mind I'm trying to figure it out and this is what I came up with ...
The goal is to subtract only those vacation days from Person 2's work days if they fall on one of their work days. In trying to figure out how to do that, I'm thinking I need to know all the vacation days (we have that) and I need to know their work days. Their TOTAL work days has been calculated by counting all the Mondays, Tuesdays, and those Sundays that they would work. The part I don't know is how do I get dates to compare, and how I should be doing that comparison. OR, if I should be thinking more in terms of converting things into TRUE/FALSE conditions and going that route ...

Now, I haven't tried your solution yet, but could you perhaps briefly look over my logic for solving the goal and tell me if I'm even on the right track?

[AliGW]

I have taken a slightly different approach - I think yours will take you into a dead end.

I'd really like to know if what I suggested has worked for you, as I did spend quite a while on it for it not to be tested.

If it does work, then I can explain my logic to you.

[AliGW]

OK, so when you confirm whether or not my solution works for you, I'll take the discussion further, if necessary. If you are waiting for something more from me before you test my solution, then I am afraid there is nothing more for me to add.

[kirash4]

Absolutely, I understand that. I'm running around doing some errands right now but will have time this afternoon to test your solution. I appreciate your help!

[kirash4]

I got a chance to test your provided solution and it appears to work for some months but not others. I took the time to write out every single month and highlighted the work periods. Then I went through and marked every work day and added them up. You can see all that data on the second sheet in the attached file. After counting the days, some of them align with your calculations but for some reason others don't.

Now I will admit that I may be completely oblivious to something here and it's possible that your solution is correct but my manual data entry is wrong. I've been staring at this for far too long.

[AliGW]

I think I have resolved it. This works:

=SUM(J10:L10)-SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<I10)*(WEEKDAY($C$5:$C$14,1)=2))-SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<I10)*(WEEKDAY($C$5:$C$14,1)=3))-SUMPRODUCT(($C$5:$C$14>=I9)*($C$5:$C$14<I10)*(WEEKDAY($C$5:$C$14,1)=1)*(MOD($V$5:$V$14,2)=0))

Now I will admit that I may be completely oblivious to something here ...

We both were! There was a miscalculation in the base formula for calculating Mondays, Tuesdays and Sundays that manifested itself in May because it was including the end payday. I have amended it thus:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(I9&":"&I10-1)), 2) = 1))

I've also amended all other formulae that refer to the end of the range to exclude the payday. Let me know if you are now happy.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[kirash4]

Aaaaah there it is! I had to uncover the column where you were calculating the WEEKNUM separately (did that the last post) but it's all there. It all works as expected. Now for the fun part, me going bit-by-bit through it to understand the calculations and how it's done. As always, thank you so much! And as of right now I am not allowed to give you more reputation but you have my appreciation. Cheers!

[AliGW]

Just shout here if you need any explanation. I enjoyed the challenge!

[kirash4]

So a couple of things that immediately became apparent to me early on:

1. I didn't know I could use an entire range to compare against a single cell. I always assumed it had to be done cell-to-cell. For example:

   Please Login or Register  to view this content.

2. I'm slowly learning to use TRUE/FALSE conditions and using SUMPRODUCT to get the actual numbers I need. It's funny that I know to do this in other programming languages, but never considered the possibility of doing it in an Excel formula.
3. Learning to use F9 to debug the conditions within each formula. This has, by far, been the biggest help in understanding what I was working with in the formula.

[AliGW]

I have found Evaluate Formula on the Formulas ribbon to be very useful when learning more complex formulae.