Formula For Counting Vacation Days

[Justinian]

I have a vacation calendar to create for 2020 and I use NETWORKDAYS to give me number of days that a person needs for vacation. For example, NETWORKDAYS formula applied to a vacation from 9/2-9/6/19 yields 5, which is correct. My problem is that some employees work on the weekends, so applying NETWORKDAYS to a vacation from 9/3-9/7/19 yields 4, which is incorrect because it is excluding that Saturday.

What formula can I use so that no matter what days of the week an employee is out on vacation, I will always get a correct count of the number of days?

 

[Joe4]

9/6/19 - 10/4/19 (yields 25, which is INCORRECT as it should be 21)

25 is actually correct, based on your original assertion to include Saturday as workday, as you would have the following workdays each week:
9/6 - 9/7 (2 days)
9/9 - 9/14 (6 days)
9/16 - 9/21 (6 days)
9/23 - 9/28 (6 days)
9/30 - 10/4 (5 days)

That adds up to 25 days!

I have the feeling that there is more to this question that you have described here, and you have left off some important details, such as maybe there are really only 5 workdays each week, but it is Monday - Friday for some people, and Tuesday - Saturday for others. If that is the case, you will actually have two different formulas, one for each group of people.

 

[Justinian]

Yes, maybe I have been unclear.

9/2-9/6 means the employee is on vacation for 5 days and I want the formula to return a 5

9/3-9/7 means the employee is on vacation for 5 days and I want the formula to return a 5 ​

_{^{<strike>
</strike>}}

9/6-10/4 means the employee is on vacation for 21 days, excluding the weekends, and I want the formula to return a 21​

​

 

[Joe4]

I don't understand why Saturday is not excluded in the 2nd example, but is in the 3rd.
Can you explain when Saturdays are to be excluded, and when they are not?

 

[Justinian]

I knew this would be confusing. Ok, here it goes:

9/2-9/6 is Monday through Friday and the employee is on vacation all 5 days

9/3-9/7 is Tuesday through Saturday and the employee is on vacation all 5 days

9/6-10/4 is Monday through Friday and the employee is on vacation all 21 days

So when an employee requests only 5 days of vacation or less, they are out all 5 days (or less) in that date range and all 5 days (or less) should be counted and return a 5 (or less). When an employee requests more than 5 days, that means weekends will be involved and need to be removed from the calculation, so that only weekdays will be counted. 9/6-10/4 is 29 days but 8 of those are weekends and should be excluded.

I hope that makes more sense.​

_{^{<strike>
</strike>}}​

 

[Joe4]

See if this does what you want:

=IF(D5-C5+1<=5,D5-C5+1,NETWORKDAYS(C5,D5))

 

[Justinian]

We are getting close!

That definitely helps most scenarios but if I apply that to the dates 12/3-12/14, the result should be 10 (Tuesday through Saturday vacation) but the formula yields 9.

 

[Joe4]

That definitely helps most scenarios but if I apply that to the dates 12/3-12/14, the result should be 10 (Tuesday through Saturday vacation) but the formula yields 9.

That seems to be in direct contradiction to your explanation in your previous post.

So when an employee requests only 5 days of vacation or less, they are out all 5 days (or less) in that date range and all 5 days (or less) should be counted and return a 5 (or less). When an employee requests more than 5 days, that means weekends will be involved and need to be removed from the calculation

We cannot come up with a working solution if we don't know what all the rules are (or if they keep changing).

 

[Justinian]

Yes, that is true. Like 9/6-10/4 (21 days), weekends are involved. In the example of 12/3-12/14, one weekend is involved. If someone requests more than 5 days, their vacation will bleed into the following month and therefore include a weekend which should be excluded from the formula result. I guess I could state things like this:

5 day vacation request = no weekends in calculation and NETWORKDAYS can be used
10 day vacation request = 1 weekend involved and those 2 days should be excluded from the formula result
15 day vacation request = 2 weekends involved and those 4 days should be excluded from the formula result
20 day vacation request = 3 weekends involved and those 6 days should be excluded from the formula result
25 day vacation request = 4 weekends involved and those 8 days should be excluded from the formula result

 

[Joe4]

I think that only leads to more questions.
Such as, can they only request in 5 day increments? If not, how does that affect the rules you listed above?

And note that 25 vacations could encompass 3 weekends or 4 weekends, i.e.
9/3-9/27 encompasses 3 weekends (6 weekend days)
while 9/6-9/30 encompasses 4 weekends (8 weekend days)

This has gotten SO much more complex than your original question. I don't think I would even attempt to do this with a formula, and would probably use VBA instead.
But not until all the rules have been clearly defined.

 

[Justinian]

You are correct - this is complex. I could easily manually write in the number of days reuqested instead of having Excel calc it for me. I thought there was an easier way but apparently, there is not.

Thank you for helping anyway.