Re: calulate working hours and minutes between 2 dates and times

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" <nigeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D80E8CF0-594F-499A-A013-51AC1A3ADE4F@xxxxxxxxxxxxxxxx
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

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

"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



.

Relevant Pages