Computation of actual working time in HH:MM format between two dates

[Bibin Kannappan]

I need to find out the actual work done time ina situation where say, the work started at 11.45 AM on a Thursday say 07/07/11 and it was send for some clarification on 08/07/11 at 14.30 PM and we received the clarification at 17.00 PM on 08/07/11 itself. The work again started at 17.00 PM on 08/07/11 and it was finished at 18.30 on Monday 11/07/2011. The normal working hours would start at 11.30 AM and end at 20.30 PM.
I need to find out in HH:MM the actual time that was taken for completing this work excluding the time lost for referrals, if we are tracking all these times in one single row associated with a task.
I used the NETWORKDAYS formula to exclude the saturday and sunday but the final time I am getting is not correct. Moreover the networkdays formula gives me a number.
Is there any formula which can consider all this times and give me the actual time. Remember I am using office 2003.
PLease help me with this

 

[barry houdini]

Hello Bibin, welcome to MrExcel

Did you get anywhere with this?

If you have a start time/date in A2 and end time/date in B2 then this formula will give elapsed business hours

=(NETWORKDAYS(A2,B2)-1)*("20:30"-"11:30")+MOD(B2,1)-MOD(A2,1)

assumes that A2 and B2 will always fall within working hours

 

[Bibin Kannappan]

Hi Barry,
thanks for your reply, there are two peculiarities. I am capturing time in a different cell and date in a different cell.
It works like this if I capture today's date in cell A1, i have a vba code which runs and capture the current time in cell B1. I have similar time captured for start time, referred time, clarified time and close time.
And i need to subtract the referred time to get the actual processing time.
I think I have got the solution for that. But dont know whether that is the best one.