Hi,

I need some help on how to write a formula to calcuate this:

If column A is before today's date,
OR column B is < 0, then column C is 0,
otherwise, calculate networkdays between today's date and column A

What makes it more complicated is if the date in column A is beyond 2005
(for example, 3/1/06), I want the # of days from now till 3/1/06 to separate
out into
# of days from now till 12/31/05, and 1/1/05 till 3/1/06.


Currently, this is what I have and it doesn't work.

IF(OR(AG3<0,AE3<=$B$265),0,NETWORKDAYS($B$265,MIN( AE3,DATEVALUE("12/31/05")))

$B$265 is a reference cell.


THANX!

What does "it doesn't work" mean?

Assuming your "column A" is actually column AE, and that your "Column B"
is actually column AG, and that your "reference cell" contains today's
date, your example appears to work for me to return this year's work
days, though it would be more general if you changed
DATEVALUE("12/31/05") to DATE(YEAR($B$265)+1,1,0). You don't say what
"separate out" means - should the next year's portion go in a different
cell?

What do you expect to happen?

In article ,
"JN" wrote:

Hi,

I need some help on how to write a formula to calcuate this:

If column A is before today's date,
OR column B is < 0, then column C is 0,
otherwise, calculate networkdays between today's date and column A

What makes it more complicated is if the date in column A is beyond 2005
(for example, 3/1/06), I want the # of days from now till 3/1/06 to separate
out into
# of days from now till 12/31/05, and 1/1/05 till 3/1/06.


Currently, this is what I have and it doesn't work.

IF(OR(AG3<0,AE3<=$B$265),0,NETWORKDAYS($B$265,MIN( AE3,DATEVALUE("12/31/05")))

$B$265 is a reference cell.


THANX!