NETWORKDAYS Formulas excluding weekends

[ITSDISP]

I am trying to make a spreadsheet that will calculate free days for our business. This formula is to exclude Holidays (which I understand how to setup in the formula) and Weekends as long as the weekends fall during the free time. Here is an example of 4 free days and charging $1 per day there after. The free day starts on Mon
Mon = Free
Tue = Free
Wed = Free
Thur = Free
Fri = $1
Sat = $1
Sun = $1

Now same rule except the start day will be Tue.
Tue = Free
Wed = Free
Thur = Free
Fri = Free
Sat = Free
Sun = Free
Mon = $1
Tue = $1

Once the $1 charge starts it continues until a stop date is entered into the spreadsheet.

 

[repairman615]

Nice job and thank you barry houdini!

I was still trying to work this on out on the long road.

I tested it a few times and it looks good to me.

 

[ITSDISP]

I have something that explains this better. Is there a way to upload a document to this forum? I dont see a way to do this?

 

[repairman615]

No, however you could use box.net or any other file share site and post the link here.

 

[ITSDISP]

OK, I have a better example for everyone to understand go to

https://spreadsheets.google.com/pub?key=0ApIYY_7hagxPdEM4QzROelB4LW5BMmlIU0VJSlhhZlE&single=true&gid=0&output=html

There are two examples on this calendar

 

[ITSDISP]

OK, I have a better example for everyone to understand go to

https://spreadsheets.google.com/pub...MmlIU0VJSlhhZlE&single=true&gid=0&output=html



There are two examples on this calendar

 

[repairman615]

ITSDISP,

I look at the linked example and then tried barry houdinis suggestion and it did work flawlessly on both examples.

Where he has 'holidays' be sure to substitue your holiday range like b1:b10

 

[ITSDISP]

barry, I have tried your way, but cant get it to work? I have a spread sheet and in A1 i have 10/17/10 and A2 i have 10/22/10. I put your formula in A3 and i get "#NAME?" error for the answer? The answer should be one day Friday?

 

[barry houdini]

I assume WORKDAY function will work OK for you as you were using NETWORKDAYS......but ensure that you have Analysis ToolPak enabled if you are uisng Excel 2003 or earlier

Tools > add-ins > tick "Analysis ToolPak"

Also if you are using holidays you need to define that as a named range......or just use the cell reference for the range containing holiday dates, i.e.

=A2-WORKDAY(A1-1,5,H$1:H$10)+1

where H1:H10 contains holiday dates