I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11
PM on Sunday night.

Example
Time 2: 1/23/07 8:31 (B1)
Time 1: 1/19/07 13:52 (A1)
Difference is 90.7 hrs
Excluding weekend hrs difference would be 42.7 hrs

note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with
the above then you need to install. Tools > addins > tick "Analysis ToolPak"

Assuming you have the Analysis Toolpak loaded, Tools>Addins>Analysis
Toolpak, then

=NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
If you want to exclude Holidays as well as weekends, then add the 3rd
parameter to Networkdays which can either be cell references holding a
list of holiday dates or a named range holding holiday dates

=NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24)
where X1:X9 hold a list of holiday dates

The formula work great for most of my data, however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period.

The formula worked great. Any suggestions for when the start date would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start date
would fall in this time period but the stop date would not. On these
ocassion the total time would start from 11 PM Sunday night.

I think the following will work, making use of a "helper cell". I used
A2.
In A2
=IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24>=23),
WEEKDAY(A1,2)>5),DATE(YEAR(A1),
MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1)

Then in cell to have the result
=IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24)

"however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop
date however will never fall in this time period"

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.