IF - very long IF formula

[wnstar21]

I am in charge of keeping track of tanks at my work, I am trying to create a report that will tell me the next scheduled move for each tank.

The IF formula will show the next scheduled date, only if that date is in the future. Now if the last scheduled move has taken place, I want the formula to display either "Move Soon" or "MUST move".

"MUST Move" if Today()-last move >=7

"Move Soon" if Today()-last move <7

There are three possible last moves:

1. Date it is coming into the port
2. Date it clears Customs
3. Date it arrives at plant

If there is no date listed I want it to display the comment cell for the tank.

I tried creating a formula, and was pretty close, but doesn't work for one of the possible scenarios, so

First, I made it look at the "arrive at plant" date, if there was a date input and wasn't "0", it did it's thing. If it was "0", it looked at Customs date,....and so on. And like I said, it looks at Plant date, then Customs date, then Port date. If there isn't a date in the last date it looks at, Port, then it shows the comment cell.

Here is a visual:

..........A............B..............C..............D...............E
1...................................PORT........6/13/08................
2.................................CUSTOMS....6/15/08................
3..................................PLANT.......6/25/08................
4............................................................................
5.............................................................................
6............................................................................
7................comment........................................IF formula

In E7, I have the following formula:

=IF(D1>TODAY(),D1,IF(D2>TODAY(),D2,IF(D3>TODAY(),D3,IF(D3=0,IF(TODAY()-D2>=7,"MUST move",IF(D2=0,IF(TODAY()-D1>=7,"MUST move",IF(D1=0,B7,"Move Soon")),"Move Soon")),IF(TODAY()-D3>=7,"MUST move","Move Soon")))))

With the formula above and the dates above, the value returned should be "6/25/08", because that is the next move, in the future.

But lets say today is July 7th, then the difference between the last move, 6/25/08 and 7/7/08, is greater than 7, so it should display "MUST move".

The problem I have is that when no dates are entered in any of the three date cells, it returns "MUST move". It should return the comment cell.

I attached a sample spreadsheet with what I am trying to describe above.

Please play around with the dates and see what I mean.

PLEASE HELP ME OUT!! ANY QUESTIONS, PLEASE DON'T HESITATE TO ASK!!

[NBVC]

Does this work for you?

=IF(SUM(D1:D3),IF(D1>TODAY(),D1,IF(D2>TODAY(),D2,IF(D3>TODAY(),D3,IF(OR(AND(D3=0,D2<>0,TODAY()-D2>=7),AND(D2=0,D1<>0,TODAY()-D1>=7),AND(D3<>0,TODAY()-D3>=7)),"MUST move",IF(D1=0,B7,"Move Soon"))))),B7)

[wnstar21]

Sort of.

The comment shows up when nothing is in any of the three date cells.

But, the Port arrival date takes precedence over the Customs date and the Customs date takes precedence over the Plant date.

so the order it looks at it is:
1. Port then if there is no date in Port then it goes to
2. Customs then if there is no date in Customs then it goes to
3. Plant then if there is no date in Plant then it goes to the comment

is there a way to reverse this order so that it looks at
1. Plant, then
2. Customs, then
3. Port

?

[NBVC]

You mean?

=IF(SUM(D1:D3),IF(D3>TODAY(),D3,IF(D2>TODAY(),D2,IF(D1>TODAY(),D1,IF(OR(AND(D3=0,D2<>0,TODAY()-D2>=7),AND(D2=0,D1<>0,TODAY()-D1>=7),AND(D3<>0,TODAY()-D3>=7)),"MUST move",IF(D1=0,B7,"Move Soon"))))),B7)

[wnstar21]

Yes. I got it! Thank you.

Also,

is there a way to format cells that return #N/A for this formula?

Some of my results will undoubtedly come back as #N/A, but it looks ugly and I was wondering if there was a way to format these cells, I tried conditional formatting for cells - equal to - #N/A, but it doesn't seem to work

[NBVC]

Select the cell with the formula and then select Formula Is from the drop down in the CF dialogue and enter formula:

=ISNA(G10) where G10 is the cell with the formula.

Click Format and select White from Font tab.

[buksy]

Can someone help me please with an IF formula?I tried to make a 13 condition IF formula but it doesn't allow me to put more than 8.I''ll attach the example.

The formula is:

=IF(E3=1;"PRODUCT 1";IF(E3=2;"PRODUCT 2";IF(E3=3;"PRODUCT 3";IF(E3=4;"PRODUCT 4";IF(E3=5;"PRODUCT 5";IF(E3=6;"PRODUCT 6";IF(E3=7;"PRODUCT 7";IF(E3=8;"PRODUCT 8")