trouble nesting IF(AND

[robotlust]

hi,
Im having some isses nesting two arguments together.

basically(ha, ha), id like to do the following operation in cell J9:

If the Actual Date (F9) is blank leave the cell blank, and if the actual date is blank is the target date is less than NOW(), leave it blank.

If the Actual date is not blank, do Actual minus Target, and if NOW() is greater then the target date, do now() minus target.


if(F9="", ""
if(E9 < NOW(), ""

AND

if(F9=<>, F9 -E9
if(NOW()>E9, NOW()-E9

I have attached a spreadsheet to illustrate. Any help will reduce my stress level.
Cheers

[rylo]

Hi

Try

Please Login or Register  to view this content.

rylo

[robotlust]

hi, i cant quite get it working how i need to. I've had a go at rewriting it but its tills not behaving properly:

Where: E = Target, F = Actual

Please Login or Register  to view this content.

the four cases it needs to satisfy are:
IF Actual is blank, return blank
IF Actual is blank, and now < Target, return blank
IF now > Target, and Actual is blank, return now - Target
IF Actual is not blank, return Actual - Target

Target Actual Result
10-Feb 11-Feb 1
18-Feb 25-Feb 7
20-Feb 18-Feb -2
20-Feb blank
10-Feb 7

[Paul]

Hi Robot,

Try:

Please Login or Register  to view this content.

[robotlust]

nearly... i was having trouble with the last requirement, where if NOW> the target date to return now()-target.
If you put a Target of 10/2/2008 and leave Actual blank the result is currently blank, where is should equal 7 i.e. NOW()-target.

Cheers
Cam

[Paul]

Robot, here is your request:

IF Actual is blank, return blank
IF Actual is blank, and now < Target, return blank
IF now > Target, and Actual is blank, return now - Target
IF Actual is not blank, return Actual - Target

and here is the formula:

Please Login or Register  to view this content.

[robotlust]

Ok so its argument number 3 that's not operating as it should be (the blue one)
Because as the formula says, IF now > Target, and Actual is blank, return now - Target the result should be 7 is the Target has 10/2 and Actual is blank. (ie today 17/2 - 10/2 = 7)

[Paul]

You say it *should* be returning 7. What *is* it returning?

The formula provided does the correct comparison that you're asking, so there's probably something else in the mix. If you'd like, try uploading a zipped copy of your workbook (minus any private data) and we can take a look.

[robotlust]

Hi Paul,
I've since been playing around with it and have worked it out.

Please Login or Register  to view this content.

It was the condition on row 37 of the attached xls that was giving us grief. Where now is less than target, calculate today()-target.

Thanks for all your assistance Paul. Much appreciated
Cheers
Cam

[daddylonglegs]

Probably not a good idea to use NOW() when dealing with dates only. Because NOW() also contains a time element your values might display as integers but the underlying value includes decimals too, so your 8 in E37 will change to a 9 at midday and if used in other formulas the underlying value would be used. I'd suggest

=IF(AND(D37="",C37>TODAY()),"",IF(D37,D37,TODAY())-C37)

[robotlust]

Thanks for that tip. i changed the last NOW() to TODAY() because the output was in decimal. i'll change the others over too.

Cheers!