calulate working hours and minutes between 2 dates and times

[nigeo]

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

 

[Mike H]

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

Where:-

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

 

[Mike H]

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

 

[kaveh]

Hi
This site might help.
Kaveh
http://www.cpearson.com/excel/datetime.htm

 

[nigeo]

cheers mike nearly there what format do the results cell have to show hours
and minutes

 

[Mike H]

hi,

Both will work in hours minutes if you delete the *24 at the and and format
as HH:MM

Mike

 

[nigeo]

Kaveh thanks, tried that but couldn't get it to work, mikes first response
appears to work just need the results in hh:mm

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom

 

[nigeo]

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

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

--


Regards,


Peo Sjoblom

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

 

[nigeo]

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