Nested IF Statement

[frogz2564]

I have a spreadsheet that has Date of Report (column H), Date of Assignment (column J), and Close Date (column I).

I need a function that will do the following and place value in column M.

IF I=0 and J=0 then M= Today-H
IF I=0 and J>0 then M= Today-J
IF I>0 and J>0 then M= I-J
IF I>0 and J=0 then M= I-H

[Craig K.]

Try this in cell M1

=IF(AND(I1=0,J1=0),TODAY()-H1,IF(AND(I1=0,J1>0),TODAY()-J1,IF(AND(I1>0,J1>0),I1-J1,IF(AND(I1>0,J1=0),I1-H1,0))))

[daffodil11]

=IF(and(I2=0,J2=0),now()-H2,if(and(I2=0,J2>0),now()-J2,if(and(I2>0,J2>0),I2-J2,if(and(I2>0,J2=0),I2-H2))))

[frogz2564]

That worked. Thanks! I had the right idea but was making it waaay too difficult.

[TMS]

=IF(AND(I=0, J=0), Today()-H, 

  IF(AND(I=0, J>0), Today()-J,

  IF(AND(I>0, J=0), I-H, 

  IF(AND(I>0, J=0), I-J,""))))

Or, more specifically (and working):

=IF(AND(I2=0, J2=0), TODAY()-H2,

  IF(AND(I2=0, J2>0), TODAY()-J2,

  IF(AND(I2>0, J2=0), I2-H2,

  IF(AND(I2>0, J2=0), I2-J2,""))))

Regards, TMS

[Pepe Le Mokko]

@daffodil

As now() returns date and actual time, it might slow down calculations due to it's volatility,and produce false results ( don't know if it's the case here)

Fi now()-J2 is larger than today()-J2. Personally I would suggest TODAY() (as OP only seems to need dates)

[daffodil11]

I never even heard of Today()! This is great!

[TMS]

@Pepe: TODAY() is volatile too

@daffodil11: if you compare TODAY() and NOW() you will not get a match. Equally, if the OP compares his dates to NOW() he's not going to match them either.


Regards, TMS

[Pepe Le Mokko]

@Pepe: TODAY() is volatile too

Did I imply somewhere that it wasn't ? If so,as I already said, please excuse my poor command of the English language

[TMS]

Did I imply somewhere that it wasn't ?

Maybe not. Perhaps just my interpretation of the sentence:

As now() returns date and actual time, it might slow down calculations due to it's volatility

And then:

Personally I would suggest TODAY()

Hence, volatility is not a factor as both would cause a re-calculation.