NETWORKDAYS with start date, end date, total days - if weekend subtract 1 from total

Greetings.

I am developing a spreadsheet to track travel days for tax purposes. I have already used the networkdays function as well as an array function to give me a total of days for multiple personnel minus overlap days. What I am trying to do now is puzzling me.

The following is a generic example:

Start Date: End Date: Total:
8/01/2013 8/15/2013 11
9/15/2013 9/27/2013 10


Now,

What I need, is a function to put in column "C". If the start date or end date fall on a weekend, it must subtract 1 from the total. If both the start date and end date fall on a weekend, then obviously the total would be subtracted by 2.

I have a feeling this is a simple IF THEN statement, but it is late. I appreciate all who can share their knowledge. Thank you.

=networkdays(start date, end date)

Thanks for the quick reply.

I am currently using this function in my column C. What I need is a statement that will identify if column A or B is a weekend. If this is true, then I need that function to subtract 1 or 2 (if both A and B are weekends) from the total sum in column C.

Networkdays function is ignored the weekened date. My formula works with your example.

Correct, the function does ignore weekends, but I also need that function to determine if in fact cell A3 is a weekend date. If A3 is a weekend date, then I need it to subtract 1 from the total. If B3 is a weekend date, I need it to subtract 1 from the total as well. The purpose of this may seem confusing, because it is. Even though its ignoring the weekends, if I have a start date or end date on a weekend, I need that subtracted from the total. Hope this makes sense.

maybe this:

=NETWORKDAYS(A3,B3)-(WEEKDAY(A3,2)>5)-(WEEKDAY(B3,2)>5)

Tony / Mama,

Thank you for the quick responses. I have used the function provided and it works great. My other question is, can I change a variable in the function to give me a different output in relation to what excel classifies as "weekends"? I understand the number value assigned to each day of the week, so if I were to change the >5 and 2 to >6 and 1, would that change my output?

Originally Posted by PConn31

Tony / Mama,

Thank you for the quick responses. I have used the function provided and it works great. My other question is, can I change a variable in the function to give me a different output in relation to what excel classifies as "weekends"? I understand the number value assigned to each day of the week, so if I were to change the >5 and 2 to >6 and 1, would that change my output?

Yes, it will change the output. Try it yourself and find out

I seem to be having another issue here. With this formula - =NETWORKDAYS(C7,D7)-(WEEKDAY(C7,2)>5)-(WEEKDAY(D7,2)>5), if my start date is 8/11 and end date 8/16, it gives me an output of 4 which is correct. The networkdays function automatically ignores the weekend of the 11th and ignores the 16th which is my travel day, perfect. What I need now, is a formula that will recognize the start date or end date as a weekday, and subtract 1 or 2 (if both are weekdays) from the total. Example:

Start End Total
8/1 8/10 6 <---correct
8/11 8/16 4 <---correct
8/16 8/23 6 <---INCORRECT, using the logic of the others, this should be 4 not 6.
9/1 9/10 6 <---correct

For some reason, with certain dates it does not recognize the weekday as being in the start or end position and does not subtract correctly. Thoroughly puzzling that it works 90% of the time. Any ideas?

Mr Legs,

Thank you very much for your responses! I actually figured it out about 10 minutes ago. It's working flawlessly. The other issue I'm having is with my overlap array function. This is my current -

=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(E5:E24))&":"&MAX(INT(F5:F24)))),2)<6)*(ROW(INDIRECT(MIN( INT(E5:E24))&":"&MAX(INT(F5:F24))))>=TRANSPOSE(INT(E5:E24)))*(ROW(INDIRECT(MIN(INT(E5:E24))&":"&MAX(INT(F5:F24))))<=TRANSPOSE(F5:F24))+0,ROW(E5:E24)^0),1))

Is there a way I can modify this function to take into account the subtraction of weekdays in the start and end dates? Right now the function is working, but due to the modification of the networkdays function, it's no longer adding properly and adding extra days to the total overlap count. Your help is much appreciated!!

Here is a snapshot of what my chart looks like...
Capture.PNG

I've think I've exhausted all of my Excel knowledge here. I can get the array to work, but it's still counting weekdays in the start and end columns which is throwing off my total overlap count.

You are brilliant, thank you very much!

One more question for the overlap function. I have a list of excluded holidays I have put into the networkdays function. What modification would I have to make to the overlap function so it excludes the holidays in my range?

You are the master. I bow to your excel excellence sir. PROBLEM SOLVED!

I have a similar problem.
My start date is on a holiday.

please help me with a formula.

I have a similar problem.
Data: Start date and time in col F, end date and time in col L, lead time (hh:mm:ss) in col M. Lead time calculation formula:=IF(ISBLANK(L2),(NETWORKDAYS(F2,NOW(),Holidays)-1+MOD(NOW(),1)-MOD(F2,1)),(NETWORKDAYS(F2,L2,Holidays)-1+MOD(L2,1)-MOD(F2,1)))
Lead time is calculated as soon as the start date is entered, considering current time (NOW) as end date until actual end date and time are entered.
2nd October is in holiday list.

Issue: For rows with start date as 2nd october, lead time does not give the correct result.
E.g. start date and time = 02-10-2014 13:56:00 and End date and time = 07-10-2014 12:03.
Result should be 60:03:00 but I get 46:07:00

I guess this is because, though 2nd october is considered holiday, start time is calculated from 13:56 and not as 3rd october 00:00

Hope I have clearly laid the case. Any help appreciated.