Calculating Elapsed Time with Excluded Periods

[Saul Espinoza]

Can anyone help me with a formula to calculate the hours worked between start date (time) and end date (time), only for networdays.......
Lunch time should be excluded 12:00 to 13:00 pm.
So far i have applied this formula but modification is still needed =(NETWORKDAYS(H8,I8)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(I8,I8),MEDIAN(MOD(I8,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(H8,H8)*MOD(H8,1),"17:00","8:00")

Assume values in columns H and I, (first values at H8 and I8).


Jan/4/12 10:55 AM Jan/6/12 12:20 PM 19:25
Jun/4/12 2:20 PM Jun/5/12 2:00 PM 08:40
Aug/2/12 1:30 PM Aug/3/12 2:00 PM 09:30
Aug/30/12 2:00 PM Aug/31/12 2:40 PM 09:40
Sep/27/12 2:00 PM Sep/28/12 2:30 PM 09:30
Oct/24/12 3:30 PM Oct/26/12 2:10 PM 16:40
Jan/29/13 10:00 AM Jan/31/13 2:10 PM 22:10
Feb/25/13 4:00 PM Feb/26/13 2:10 PM 07:10
Mar/26/13 5:00 PM Mar/28/13 8:00 AM 09:00
Apr/29/13 10:00 AM Apr/30/13 2:00 PM 13:00
May/29/13 1:00 PM May/30/13 2:50 PM 10:50
Jun/26/13 4:00 PM Jun/27/13 2:50 PM 07:50
Jul/24/13 4:00 PM Jul/25/13 4:00 PM 09:00
Aug/26/13 4:00 PM Aug/28/13 7:00 AM 10:00
Oct/16/13 4:00 PM Oct/17/13 4:00 PM 09:00
Nov/25/13 4:00 PM Nov/26/13 4:00 PM 09:00
Dec/16/13 4:00 PM Dec/18/13 4:00 PM 18:00
Jan/21/14 4:00 PM Jan/23/14 4:00 PM 18:00
Feb/12/14 4:00 PM Feb/14/14 4:00 PM 18:00

 

[NARAYANK991]

Hi Saul ,

See your file ; I have used several helper columns , so that you can verify the entire calculation for yourself. The final formula is in column R.

Narayan

 

[Saul Espinoza]

Thank you,!!!!
very much appreciated Narayan!!!!!!!,