Networkdays function not returning correct value

Question

I have been trying to get the number of working days between a Friday and Tuesday as shown in the image below, but it is counting Saturday and Sunday as working days when it shouldn't be. It is therefore returning 3 instead of 1. However, when I take start date and end date as the first and last day of the month respectively, I get the correct number of working days i.e., excluding weekends.

Can someone please tell me why is this happening and how can I fix this?

Answer 1

NETWORKDAYS includes both the start date and end date in the count, if they are not weekend days.

There are five days in your range: Friday, Saturday, Sunday, Monday and Tuesday.

Two of those are weekend days. NETWORKDAYS counts Friday, Monday and Tuesday, so it returns 3.

If you don't want to count both the start date and end date if they are working days, use

=NETWORKDAYS(C1,D1)-AND(WEEKDAY(C1,2)<6,WEEKDAY(D1,2)<6)

Answer 2

WEEKDAY(C1,2) returns the number of the weekday of the date in C1, with Monday = 1, Tuesday = 2, ..., Saturday = 6 and Sunday = 7.

(WEEKDAY(C1,2)<6) is TRUE if the day is a working day (Mon-Fri) and FALSE if it is a weekend day (Sat-Sun).

In calculations. TRUE is treated as 1 and FALSE as 0.

So the formula subtracts 1 if C1 contains a working day. Similar for D1.

Answer 3

Try this:

=NETWORKDAYS(C1,D1)-(WEEKDAY(C1,2)<6)-(WEEKDAY(D1,2)<6)

Answer 4

Ok, understand that it counts start and end dates, however, this is what I got when I use the formula you shared:

now why is it returning 5 since it shouldn't count start and end date i.e., 21-10-22 and 28-10-22 and number of work days between them is 4?

Answer 5

Thank you very much, this works! However, can you please explain the meaning of "-(WEEKDAY(C1,2)<6)-(WEEKDAY(D1,2)<6)" since I didn't quite understand how does it help in not counting start and end dates?