the original answer from mike works with the last bit from Peo Sjoblom
thanks to all who offered advice, will be back with new problem soon. thanks
again
--
nigeo


"Peo Sjoblom" wrote:

Remove the *24 and format as [hh]:mm

--


Regards,


Peo Sjoblom

"nigeo" wrote in message
...
the simpler version does't appear to work it gives a result out of working
hours (not required) the original version appears to be ok but appears to
give the result as decimal, what can i format that to, to get hh:mm
nigeo


"Mike H" wrote:

A bit simpler

=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24

And if you want you can add the additional argument where Holidays is a
named range that contains holiday dates. Format both as general

=((NETWORKDAYS(A1,B1,holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24

Mike


"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