Excel 2010: Need formula to calculate hours/minutes required to perform task( but exclude weekends and holidays)

[hinesgg]

I2 = start time
J2 = end time
I2 and J2 are formatted as custom: m/d/yyyy h:mm

Named Range: Holidays includes holidays formatted as Date (*3/14/2001)

Trying to calculate how much time (in hours and minutes) lapses between I2 and J2, but exclude weekends and holidays

 

[Tinbendr]

You're looking for networkdays function.

 

[hinesgg]

You're looking for networkdays function.

I found that and performed other searches and came up with this formula:

=((NETWORKDAYS(I2,J2,HOLIDAYS)-1)+MOD(J2,I2)-MOD(I2,1))*24

I2 contains 7/10/2012 7:50:00 AM
J2 contains 7/10/2012 1:03:00 AM
K2 contains formula above, producing result of 23673437:12 although format is set to Custom [h]:mm

What I expect to see is 5:13

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[hinesgg]

So now I have a couple of formulas, but neither workds for all situations. Here's what it looks like:

Date/Time CTM Requested Clarification</SPAN>	Date/Time Clarification Received</SPAN>	Time Spent Waiting for Clarification (in Hours)</SPAN>	Approx Expected Result</SPAN>
7/10/12 7:50 AM	7/10/12 1:03 PM	5:13	5
6/27/12 12:10 PM	7/3/12 9:00 AM	32:50	29
6/28/12 11:10 AM	6/28/12 2:37 PM	3:27	4
6/29/12 10:30 AM	7/2/12 9:30 AM	8:00	23
7/2/12 9:33 AM	7/2/12 12:44 PM	3:11	3

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

The formula I'm currently using is this:
=(NETWORKDAYS(I2,J2,HOLIDAYS)-1)*(WorkdayEnd-WorkdayStart)+IF(NETWORKDAYS(J2,J2,HOLIDAYS),MEDIAN(MOD(J2,1),WorkdayEnd,WorkdayStart),WorkdayEnd)-MEDIAN(NETWORKDAYS(I2,I2,HOLIDAYS)*MOD(I2,1),WorkdayEnd,WorkdayStart)

And it works for all rows except row 4. I'm looking for something that will produce the results similar to those in column 4.

I found that and performed other searches and came up with this formula:

=((NETWORKDAYS(I2,J2,HOLIDAYS)-1)+MOD(J2,I2)-MOD(I2,1))*24

I2 contains 7/10/2012 7:50:00 AM
J2 contains 7/10/2012 1:03:00 AM
K2 contains formula above, producing result of 23673437:12 although format is set to Custom [h]:mm

What I expect to see is 5:13

<TBODY>
</TBODY>

 

[Tinbendr]

Sorry, I'm no help with this, but I did find this website.