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?

 

[Eric W]

Try using NETWORKDAYS.INTL, which allows you to say which days are "weekend" days. You can build a string for an employee that works with that's employee's schedule.

https://support.office.com/en-us/ar...function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28

 

[Justinian]

Using NETWORKDAYS.INTL does not work on 9/3/19 - 9/7/19 becuase it returns a 4, even when i write the formula like this to include Saturday:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=NETWORKDAYS.INTL(C5,D5,1)[/FONT]

 

[Joe4]

Looks like you chose the wrong option for the third argument.
If you want to include Saturday as a work day, it should be:

=NETWORKDAYS.INTL(C5,D5,11)

as per the documentation Eric referenced.

 

[Eric W]

Rather than use a number as the 3rd parameter, try using a string, like this:

=NETWORKDAYS.INTL(C5,D5,"1000011")

where each digit in the string represents a day of the week, starting with Monday. If the employee works on that day, put a 0, if he does not, put a 1.

 

[Justinian]

Looks like you chose the wrong option for the third argument.
If you want to include Saturday as a work day, it should be:

=NETWORKDAYS.INTL(C5,D5,11)

as per the documentation Eric referenced.

11 is for Sunday and my example ends on Saturday,

 

[Joe4]

11 is for Sunday and my example ends on Saturday,

I think you are misunderstanding how this argument works.
What "11" tells the formula that Sunday is the only weekend day (so Saturday is treated as a workday).
That is what you are after, isn't it?

Did you try it? It gives you an answer of 5, which is correct, right?
If not, they you need to explain further what your expected result is and how you arrived at it.

 

[Justinian]

Rather than use a number as the 3rd parameter, try using a string, like this:

=NETWORKDAYS.INTL(C5,D5,"1000011")

where each digit in the string represents a day of the week, starting with Monday. If the employee works on that day, put a 0, if he does not, put a 1.

Doing it this way for 9/3/19 - 9/7/19 yields a 4, not 5, which is incorrect. There will be no days worked for that employee because he is on vacation.

 

[Joe4]

Doing it this way for 9/3/19 - 9/7/19 yields a 4, not 5, which is incorrect. There will be no days worked for that employee because he is on vacation.

That is because you haven't updated the string like Eric instructed (he was just providing an example).

The formula I provided:

=NETWORKDAYS.INTL(C5,D5,11)

returns 5, which is correct (if Saturiday is considered a workday).

If you want to exclude vacation days, you need to provide us with more information, such as where does that information resides in your workbook that may be able to to access it and apply it to the formula, and what exactly does that data look like?

 

[Justinian]

I think you are misunderstanding how this argument works.
What "11" tells the formula that Sunday is the only weekend day (so Saturday is treated as a workday).
That is what you are after, isn't it?

Did you try it? It gives you an answer of 5, which is correct, right?
If not, they you need to explain further what your expected result is and how you arrived at it.

It worked but only for some arguments. For example, below is what the formula yielded for these three examples:

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