Nested IF Statement

[swadd06]

Hello, I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.

What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".

Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".

If it is any day in between the two, it should display the term "Active"

I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.

=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))

=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))

(D# corresponds to the respective cell that contains the scheduled ISD)

[ConneXionLost]

I haven't confirmed this, but try:

Please Login or Register  to view this content.

[swadd06]

Well it works in that it doesn't produce an error but it doesn't display the proper output. I used that equation with 4/1/2010 as the in service date which should bring up a future status and it displayed verify

[tinyjack]

Formula works fine for me.

[ConneXionLost]

D'oh, sorry.

How about this:

Please Login or Register  to view this content.

[ArneT]

Assuming your date is in US format (i.e. 1/4/2010 is April 1st 2010), I think this is what you are looking for:

=IF(D7=0,"active",IF(D7-TODAY()<-40,"Verify",IF(D7-TODAY()>365,"Future","active")))

Please let me know if this solves the problem or if I've misunderstood what you are looking for!

I've just seen the formula above from ConneXionLost which was added just before I posted mine - CXL's formula is shorter than mine - however our formulae handle an empty or zero value differently. Take your pick!

Thanks,

Arne T

[swadd06]

works perfectly, thank you very much.

[swadd06]

On a side note, the spreadsheet I am using isn't one created by me.

When the word "verify" pops up, the box has a red background with yellow text. If I wanted to format the background and text color for "future" how would I do that?

[ConneXionLost]

Have a look at "Format > Conditional Formatting..."

[swadd06]

Conditional Formatting is greyed out, even when I have the box with the code selected. Any other ideas?

[ArneT]

Have a look in conditional formatting (Format > Conditional formatting). You either need to edit what's there or add a new condition (using the "Add >>" button).

Note: Ensure you select the complete range of cells you wish to apply conditional formatting to when you have figured this out (as it will only do the cells selected before you edit the formatting.

Hope this helps!

Arne T

[ConneXionLost]

If you've double-clicked the cell, or clicked in the formula bar, then Excel switches to Edit mode and Conditional Formatting will be greyed out. Just do a single click to select the cell of interest, and it should work.

[swadd06]

It is also a shared document, would that prohibit my from entering conditional formatting? Because it is greyed out no matter what I do

[ArneT]

Yes, Protect and share seems to be the culprit. You need to take control of it without sharing and then reprotect and share again!

Arne T