Network Hours

  • Thread starter Thread starter Bob Morris
  • Start date Start date
B

Bob Morris

Hi,

I am looking for a fuction like networkdays but I would like the calculation
in work hours. For example, if something starts on Monday at 2:00pm and
finishes on Wednesday at 10:00am that would be 9 working hours. This should
also exclude weekends like networkdays. Does anyone know of such a
function?

Thanks,
Bob
 
Hi Bob
if you only enter the starting and finish time in two cells (lest say
A1 and B1) use the formula
=B1-A1+(B1<A1)

If you want to exclude weekends I#m not so sure how you would enter
such working times?. You have to a least include the date for this. you
may give an example of your date/time entry and your expected result
 
Hi Bob!

Try:

=(NETWORKDAYS(A1,B1)-2)*8/24+(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(
9,0,0))
Format [hh]:mm


Starting day and time is in A1 and stopping day and time in B1
NETWORKDAYS returns the number of working days excluding the starting
and stopping days.
Deduct 2 and you get the complete working days.
Multiply that by 8/28 and you get the complete working day hours as a
decimal part of a day (for Excel time format)
Now add the hours worked on the starting day and stopping day (in this
case I've ignored lunch hours and assumed start at 9:00AM and finish
at 5:00PM.

You can work on this as a base for adding other complications such as
holidays and lunch breaks



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top