Calculate Business Hours exluding weekends and holidays with a caveat

Hi all,

I have been really struck trying to find a formula to calculate business hours between 2 dates, especially if it is started on a weekend and ending on a weekend.

I have attached an excel spreadsheet with certain dates, and the answers that I am looking for. I have also attached a holiday list and the workday start (6:00 AM) and work day end (9:00 PM).

If an action is started on Saturday and ended on Sunday (of the same week), I want the calculation to return "N/A" (or something that I can identify that should not be considered when measuring performance). However, if an action is started on a Saturday and ended the following Saturday (without any holidays in the middle), I want the calculation to return 75.00 (business hours).

Essentially I want to calculate elapsed business hours between two dates while disregarding weekends, holidays, and "after-hours" AND if it turns out that NO business hours took place during those two dates, I want to the calculation to return an easily identifiable text field like "N/A" for example. ALSO, and this is important, if an action was started and ended at the exact same time (WITHIN NORMAL WORKING HOURS), I need the calculation to return 0.0 and NOT "N/A".

I've included a few other examples in my spreadsheet that have stumped me. I can find a calculation for 1 of them, but yet to find 1 calculation for ALL of them.

Hope I was clear, if not please feel free to ask any follow-up questions.

I REALLY APPRECIATE YOUR HELP!

Teamdob

A somewhat belated response but should you still require a formula (and/or anyone else should stumble upon this post with similar requirement) you could, using your sample, use something along the lines of the below:

Formula:

C3:

=IF($A3=$B3,0,IF(NETWORKDAYS($A3,$B3,$F$2:$F$13)=0,"n/a",(NETWORKDAYS($A3,$B3,$F$2:$F$13)-1)*($H$2-$G$2)+IF(NETWORKDAYS($B3,$B3,$F$2:$F$13),MEDIAN(MOD($B3,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS($A3,$A3,$F$2:$F$13)*MOD($B3,1),$H$2,$G$2)))

copied down

Above requires Analysis ToolPak be activated if using XL2003 or below (non-ATP alternatives exist but are less efficient)