More Help with Conditional Formatting

[Shelly]

I now have it sort of working with this formula:

=IF(AND(L7<(A1)-L7>60),"Pending",IF(AND(L7>(A1)-L7<180),"FollowUp",IF(L7>=(A1),"Completed",0)))

My problem now is that it isn't calculating the dates correctly it is
showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it
is still showing as "pending" instead of "completed").

I also would like to see if I could use the "stop light" coloring with this
as well and have yellow for the pending section, red for followup and green
for completed, if possible.

 

[Luke M]

I don't think your formula is written for what you actually want. When you
use the AND, you're wanting both conditions? You currently have it setup as
one condition.

=IF(AND(L7<(A1),L7>60),"Pending",IF(AND(L7>(A1),L7<180),"FollowUp",IF(L7>=(A1),"Completed",0)))

Written this way, if L7 is less than A1 AND L7 is greater than 60, its
"Pending".

For the stoplight, Format-Conditional formatting
condition1
Set cell value equal to pending, format yellow,
condition2
set cell value equal to follow-up, format red
condition3
set cell value equal to completed, format green

 

[Shelly]

How would the formula work for the color coding?

You're right, I don't want the AND condition to do that. What are your
suggestions?

 

[Luke M]

The formula I wrote is the one you should be able to use in your cell.
=IF(AND(L7<(A1),L7>60),"Pending",IF(AND(L7>(A1),L7<180),"FollowUp",IF(L7>=(A1),"Completed",0)))

For the color coding, you don't need a formula. Go to Format - Conditional
Format. It should be set to cell value, equals, and then type "Pending" (sans
quotes)
Then click format, patterns, and pick color you want.

Add additional conditions for the other two outcomes.

If the formula I gave you is not what you want, please write out what
conditions you are desiring for clarification.

 

[Shelly]

Thank you, This new formula works except now instead of showing "pending",
"followup", etc., it says TRUE and FALSE....how do we fix this?

 

[David Biddulph]

You fix it by using the formula as suggested.
=IF(AND(L7<(A1),L7>60),"Pending",IF(AND(L7>(A1),L7<180),"FollowUp",IF(L7>=(A1),"Completed",0)))
It can't give TRUE or FALSE.

 

[Shelly]

It did, I fixed it, now it's back to saying "pending". But it still isn't
doing what I want. I want it to say "pending" if the time between when I
tasked something (cell M7) is 60 days or less than the current date (A1);
"followup" for items between 60 and 180 days; and "completed" from 180+
If this helps any. I really would appreciate any help for this!

 

[David Biddulph]

Well, you can see the syntax. Change the terms to suit your requirements.
--
David Biddulph

It did, I fixed it, now it's back to saying "pending". But it still isn't
doing what I want. I want it to say "pending" if the time between when I
tasked something (cell M7) is 60 days or less than the current date (A1);
"followup" for items between 60 and 180 days; and "completed" from 180+
If this helps any. I really would appreciate any help for this!

 

[Shelly]

I'm sorry, but I'm not sure if I understand what you mean. Is there a
particular formula I should use?