The formula I wrote is the one you should be able to use in your cell.
=IF(AND(L7<(A1),L760),"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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Shelly" wrote:

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?
--
Thank you!
Shelly


"Luke M" wrote:

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),L760),"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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Shelly" wrote:

I now have it sort of working with this formula:

=IF(AND(L7<(A1)-L760),"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.

--
Thank you!
Shelly