Calculating Net Working Hours.

This is the first formula I've used that's worked for my business purposes. Thank you!! I believe I've tried at least 20 others in the past week. I needed something that took weekends, holidays, and business hours into consideration but didn't "break" when an activity was started after business hours.

I made a few modifications because we have an 11 hour workday and folks covering lunches.

=IF($G2>0,TEXT((NETWORKDAYS($C2,$G2,$V$2:$V$8)-1)*11/24+IF(NETWORKDAYS($G2,$G2,$V$2:$V$8),MEDIAN(MOD($G2,1)-MEDIAN(0,1/24,MOD($G2,1)),$AA$1-1/24,$Y$1),$AA$1-1/24)-MEDIAN(NETWORKDAYS($C2,$C2,$V$2:$V$8)*(MOD($C2,1)-MEDIAN(0,1/24,MOD($C2,1))),$AA$1-1/24,$Y$1),"[h]:mm"),"N/A")

G2 is the activity end date/time.
C2 is the activity start date/time.
V2:V8 are holidays.
Y1 is the start of the business day.
AA1 is the end of the business day.

Hi Klohman,

In the above formula, how can i include the lunch hours as well. Means I just want to remove the weekends (saturday and sunday) and non business hours.

Regards,
Amit.