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
[TABLE="width: 107"]
<TBODY>[TR]
[TD]What I expect to see is 5:13[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[hinesgg]

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

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


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
[TABLE="width: 107"]
<TBODY>[TR]
[TD]What I expect to see is 5:13
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Tinbendr]

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