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]

I am a little confused about the weekends.
Will they be charged every weekend after what is showed?

I am asking to see if it is just four days free then , dollar a day there after

Please clarify as I was thinking you may be able to use

=NETWORKDAYS(E1,F1,G1:G7)-4

where -4 would be subtracting four full workdays from the difference of two dates...not including weekends or holidays.

 

[ITSDISP]

Yes, once the charges start, they will include the weekend and holidays. Lets look at this month of Oct with a start date of 10/4/2010 and end date of 10/22/2010.

The free days would be
10/4 Mon
10/5 Tue
10/6 Wed
10/7 Thur

The chargeable days would be
10/8 Fri
10/9 Sat
10/10 Sun
10/11 Mon
10/12 Tue
10/13 Wed
10/14 Thur
10/15 Fri
10/16 Sat
10/17 Sun
10/18 Mon
10/19 Tue
10/20 Wed
10/21 Thurs
10/22 Fri the termination day or end day

$15 owed.

Hopefully I got my dates and days correct for Oct 2010.

 

[repairman615]

So then if the customer starts on and overlaps a weekend or holiday, then in addition to the four days free, the also get the holidays and weekends.

otherwise after the four days free they will be charged for everyday, including weekends and holidays.

Is that right?

 

[ITSDISP]

Yes, after the 4 days free they will be charged for everyday, including weekends and holidays.

 

[repairman615]

Maybe something like this:

where:
A8 is the end date
A7 is the start date
formatted as currency

=(A8-A7)-NETWORKDAYS(A7,A7+2,J2:J12)

 

[repairman615]

This may be closer.

=(A8-A7)-(7-(NETWORKDAYS(A7,A7+6,J2:J20))+1)

 

[ITSDISP]

Nope? Comes up with -$3.00

 

[repairman615]

This is interesting.

A1 is the start date
A2 is the end date

=((A2-A1)+1)-IF(WEEKDAY(A1,2)=2,4+2,IF(WEEKDAY(A1,3)=2,4+1,4))

This approach finds the difference from start date to end date.

The if statements looks at start date, subtracts:
6 if Tuesday
5 if Wed.
4 for all others

Is subtracting 4 for all other days correct for the outcome desired?

Still not able to deal with holidays yet. Unsure how the holidays will affect the free days.
What if the holiday is in the middle of the four day free?

From the looks of this...Tuesday might be a high volume business day.

 

[barry houdini]

I think you need to use WORKDAY function. Presumably if the start date was a Saturday or Sunday the free days would still be Monday to Thursday of the following week with charging beginning on the Friday? If so try

=A2-WORKDAY(A1-1,5,holidays)+1

where A1 contains the start date, A2 the end date and holidays is a named range containing holiday dates