can I add something to this which seems to work perfectly, to accomodate
holidays
--
nigeo


"Mike H" wrote:

Try this,

=((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24

Whe-

A1 = start date/time formatted mm/dd/yyyy hh:mm
B1 = end date/time formatted mm/dd/yyyy hh:mm
I1 = Workday start formatted hh:mm
J1 = Workday end formatted hh:mm

the formula is draggable for other date/times in columns A & B

Mike

"nigeo" wrote:

I need to calculate the working hours and minutes between two dates and times.
saturday and sunday need to be excluded and the core hours are 08:00 to 17:00.
I have a task that needs to be completed in 2 working hours but can arrive
any time in the 24 hour clock including weekends but the task time starts
from the working hours. At 17:00 the clock stops and begins again at 08:00
next working day.
date time format is m:d:yyyy h:mm
Task are presented across the rows so for example arrival time column M and
task close time column O. There is a column for overall time but I just need
the working hours and minutes taken to complete. A complete week list is
about 6000 rows so you can see the pain to do this manually.
I have scoured the internet / this site for an answer but at present have
had no luck getting something to work.
Can anbody help please
--
nigeo