Help with Conditional Formula

[Shelly]

I'm using Excel 2007; I am trying to write a conditional formula. What I
essentially want is that if the date is less than 60 days I want the status
to show pending, if the date is between 60 and 180 days I want to follow up
with what I have to do and after 180 days, I would like it to show completed
or something like that.
So far I have =IF(M8>60,"FOLLOWUP", IF(M8>180,"OK")) excel won't let me put
in more data, and when I've played with the dates I'm not too entirely sure
if this works either.....
And my formula for the dates is fine, I'm just not sure what to do for this
one.

 

[David Biddulph]

Your M8>180 won't work because you've already trapped all values of M8>60,
and you'll only go down the alternative route if M8<=80. Think again about
the order of your tests.

 

[Sheeloo]

If M8 has the date (assumed to be in the past) then try this
=IF((Today()-M8) < 60, "Pending",IF((Today()-M8) < 180,"Follow
Up","Completed"))

 

[Shelly]

This formula looks great, but when I put it in, I keep getting the #NAME?
error message; and I was incorrect, it was actually L8 instead of M8, but
even when I changed that, it still gave me the error.

 

[Pete_UK]

Have you missed one of the quotes off? Have you typed TODAY correctly,
and ensured there is no space after it and the open bracket?

Hope this helps.

Pete

 

[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.

 

[David Biddulph]

You've presumably got a typo in the formula.
Don't try to retype the formula; copy it from here & paste into your
formula bar.

 

[David Biddulph]

You may wish to look in Excel help and remind yourself of the syntax of the
AND function.