calculate days

[Tiffany]

Hi,

I have put in a formula to calculate the no. of working days between
4/1/2004 (in C2) and 3/31/2005 (E2).

Started work on 11/9/2004 (in J2 cell)

The formula is
=ROUND(IF(DATEVALUE(J2)<=DATEVALUE(BAI!$C$2),(DATEVALUE(BAI!$E$2)-DATEVALUE(BAI!$C$2)+1),(IF(AND((DATEVALUE(J2)>BAI!$C$2),(DATEVALUE(J2)<=DATEVALUE(BAI!$E$2))),(DATEVALUE(BAI!$E$2)-DATEVALUE(J2)+1),0))),2)

However, the formula returns #VALUE!

Can someone help.

Thank you very much

Jessica

[Bob Phillips]

Hi Jessica,

Based upon the cells having real dates, this worked for me

=ROUND(IF(J2<=BAI!$C$2,(BAI!$E$2-BAI!$C$2+1),(IF(AND((J2>BAI!$C$2),(J2<=BAI!
$E$2)),(BAI!$E$2-J2+1),0))),2)

--
HTH

Bob Phillips

"Tiffany" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have put in a formula to calculate the no. of working days between
> 4/1/2004 (in C2) and 3/31/2005 (E2).
>
> Started work on 11/9/2004 (in J2 cell)
>
> The formula is
>
=ROUND(IF(DATEVALUE(J2)<=DATEVALUE(BAI!$C$2),(DATEVALUE(BAI!$E$2)-DATEVALUE(
BAI!$C$2)+1),(IF(AND((DATEVALUE(J2)>BAI!$C$2),(DATEVALUE(J2)<=DATEVALUE(BAI!
$E$2))),(DATEVALUE(BAI!$E$2)-DATEVALUE(J2)+1),0))),2)
>
> However, the formula returns #VALUE!
>
> Can someone help.
>
> Thank you very much
>
> Jessica
>
>

[Bernd Plumhoff]

Hi Jessica,

see Excel's help on NETWORKDAYS (install and load Analysis ToolPak add-in!)
or see http://www.sulprobil.com/html/workingdays.html (but beware, kind of
different definition and without holidays).

HTH,
Bernd