Difference between Time Stamps

[Spoorthi]

I have found the difference between the below two datetime stamps and formatted in time format "37:30:55". I have excluded the non working hours and weekends and holidays in it. By using the below formula I got the result as 17:20:39, but when I calculate it manually, I get a different result 16:51:00 which is the correct one. Can anyone help me arrive at the correct result.. Your help will be highly appreciated.

Start Date=H13= 9/14/11 11:09 AM
End date=I13= 9/16/11 10:30 AM
Working hrs start time=M2= 9:30 AM
Working hrs end time=M3= 6:30 PM
Holiday List = N2:N4
Formula used: (NETWORKDAYS(H13,I13,$N$2:$N$4)-1)*($M$3-$M$2)+IF(NETWORKDAYS(I13,I13,$N$2:$N$4),MEDIAN(MOD(I13,1),$M$3,$M$2),$M$3)-MEDIAN(NETWORKDAYS(H13,H13,$N$2:$N$4)*MOD(H13,1),$M$3,$M$2)
Result = 17:20:39

 

[barry houdini]

How do you get 16:51?

11:09 until 18:30 on 14th = 7:21
whole of 15th = 9:00
9:30 to 10:30 on 16th = 1:00

Total = 17:21

...give or take some seconds which i can't calculate because you haven't shown them.....

 

[Spoorthi]

You are right Barry I was calculating it wrongly. But now I realised that I want to exclude the lunch hours which is 1 Hr a day. Will you please help me with it.

 

[barry houdini]

Your current formula allows start and end dates/times to be any time - is that what you need? If start and end times/dates can only be within business hours then you could simplify the formula.

To exclude luch breaks you also need to specify the time of the break.....would it also be possible for the start/end times/dates to be within lunch break?

 

[Spoorthi]

Yes Barry, the start and end time can be anytime and they can be within the lunch break too.. which is from 1 PM to 2 PM

 

[barry houdini]

If you put the lunch break start and end times in M4 and M5 respectively then this formula should work for you

=(NETWORKDAYS(H13,I13,N$2:N$4)-1)*(M$3-M$2+M$4-M$5)+IF(NETWORKDAYS(I13,I13,N$2:N$4),MEDIAN(MOD(I13,1), M$3,M$2)-MEDIAN(MOD(I13,1), M$5,M$4),M$3-M$5)-IF(NETWORKDAYS(H13,H13,N$2:N$4),MEDIAN(MOD(H13,1), M$3,M$2)-MEDIAN(MOD(H13,1), M$5,M$4),M$2-M$4)

 

[Spoorthi]

It works.... Thanks a lot for your help. You rock!!