NETWORKDAYS with minutes?

[dan]

Hi, I have a table similiar to this:

ColA ColB
6/06/2006 9:58:16 AM 6/11/2006 2:33:25 PM

I want to calculate the difference between ColA and ColB, excluding the
amount of time for weekends. I would like to return this in hours. I am
currently doing this using:

=((B2-A2+IF(A2>B2,1))*24)

This returns the amount of hours between the two dates. I would like to
modify this to exclude weekends in the date calculation.

Thank you so much!

 

[Bob Phillips]

=(NETWORKDAYS(A2,B2)-2)*24+(1-MOD(A2,1))*24+(MOD(B2,1)*24)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hello Dan,

=24*((INT(A2)-WEEKDAY(INT(A2),2)+WEEKDAY(INT(A1),2)-INT(A1))/7*5-MIN(5,
WEEKDAY(INT(A1),2))+MIN(5,WEEKDAY(INT(A2),2))+IF(WEEKDAY(A1,2)<6,1-MOD(A1,1),0)+IF(WEEKDAY(A2,2)<6,MOD(A2,1)-1,0))

A1: Start date
A2: End date

Please notice: Result is given in hours, fraction represents minutes
(0.5 = 30 minutes).

Formula is a derivative from
http://www.sulprobil.com/html/date_formulas.html

Please test it thoroughly...

HTH,
Bernd