# calulate working hours and minutes between 2 dates and times

N

#### 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

M

#### 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

M

#### 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

N

#### nigeo

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

M

#### Mike H

hi,

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

Mike

N

#### 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

P

#### Peo Sjoblom

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

--

Regards,

Peo Sjoblom

N

#### 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

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

--

Regards,

Peo Sjoblom

nigeo said:
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

N

#### nigeo

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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.