networkdays

K

kevt

I am trying to calculate working hours.i am udins NETWORKDAYS to get the
days and i need to calculate different hours for different days, ie fridays
are 4 hours and monday to thursday are 8.25 hours, if office based fridays
are 7 hours and monday to thursday are 7.5 hours.
I would really aprecitate any help

thanks
Kev
 
R

Ron Rosenfeld

I am trying to calculate working hours.i am udins NETWORKDAYS to get the
days and i need to calculate different hours for different days, ie fridays
are 4 hours and monday to thursday are 8.25 hours, if office based fridays
are 7 hours and monday to thursday are 7.5 hours.
I would really aprecitate any help

thanks
Kev
Click to expand...

Try this **array** formula:

=SUM(INT((EndDate-WEEKDAY(EndDate+1-{2,3,4,5,6})-StartDate+8)/7)*
IF(Location="office",{7.5,7.5,7.5,7.5,7},{8.25,8.25,8.25,8.25,4}))-
SUMPRODUCT((Holidays>=StartDate)*(Holidays<=EndDate)*
(WEEKDAY(Holidays)={2,3,4,5,6})*IF(Location="office",
{7.5,7.5,7.5,7.5,7},{8.25,8.25,8.25,8.25,4}))

To enter an **array** formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>. Excel will place braces {...} around
the formula.

I used named variables to make the logic a bit more clear and account for the
possibility that the work was taking place "office-based" or not.


--ron
 
Top