Exclude Sat and Sun, Median Formula

[Bre-x]

Hi,

I have this formula: =MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-1)*$F4))

Column E = Total Hours
Column F = Hours per Day

It helps me to distribute the amount of hours per day. So if column E has a
value of 40 Hrs

Colums G equals to 16
Column H equals to 16
Column I equals to 8
Column J equals to 0
and so on.........

Works great. But What I would like to do is to some how check the day of the
week, if my column is sat or sun the value will be zero.
The problem I have is that the next Column which will be Monday return a
value of Zero. Making the formula useless.

Any help will be really appreciated.

Regards,

Bre-x

 

[T. Valko]

Assuming you have dates in row 3:

=IF(WEEKDAY(G3,2)>5,0,MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-SUMPRODUCT(--(WEEKDAY($G3:G3,2)>5))-1)*$F4)))

 

[T. Valko]

We can reduce that a little. Instead of subtracting weekends we can just
count the weekdays:

=IF(WEEKDAY(G3,2)>5,0,MEDIAN(0,$F4,$E4-(SUMPRODUCT(--(WEEKDAY($G3:G3,2)<6))-1)*$F4))