Beyond frustrated with this dates,lookups?

[cam87]

Good day all.
I am having a difficult time with getting a formula that would provide me with my answer for the following. I have the following in the columns mentioned. basically i need a formula that will calculated the days between today and the date within column b, when it has that number it has to return text into column C according to the last column in the rules mentioned below.

basically, if it says Lead( for instance) and the days difference between column B and today's date is less than 7, it has to return " Active"

column A Column B Column C
Lead Date( for Example 2nd of May 2014) answer




The rules

Category Day Difference Answer/comment

[TABLE="width: 667"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Lead [/TD]
[TD]<7[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Lead [/TD]
[TD]Between 7 and 14[/TD]
[TD]Expires this week[/TD]
[/TR]
[TR]
[TD]Lead [/TD]
[TD]>14[/TD]
[TD]expired[/TD]
[/TR]
[TR]
[TD]Potential [/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]Between 83 and 90[/TD]
[TD]Expires this week[/TD]
[/TR]
[TR]
[TD]Potential [/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[/TR]
[TR]
[TD]Imminent deal [/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Imminent deal [/TD]
[TD]Between 83 and 90[/TD]
[TD]Expires this week[/TD]
[/TR]
[TR]
[TD]Imminent deal [/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]Between 83 and 90[/TD]
[TD]Expires this week[/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[/TR]
[TR]
[TD]Opportunity [/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Opportunity [/TD]
[TD]Between 83 and 90[/TD]
[TD]Expires this week[/TD]
[/TR]
[TR]
[TD]Opportunity [/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[/TR]
</tbody>[/TABLE]

 

[ace19852]

Ok it's not pasting like it should so I'll type it in direct

=IF(OR(AND(A1="Lead",B1>TODAY()-7,AND(A1<>"Lead",B1>TODAY()-83))),"Active",IF(OR(AND(A1="Lead",B1<today()-14),(and(a1<>"Lead",B1<today9)-90))),"expired","expires this="" week"))


<TODAY()-14),(AND(A1<>"Lead",B1<TODAY()-90<today()-14),(and(a1<><today()-
))),"Expired","Expires this week"))</today()-
</today()-14),(and(a1<></today9)-90))),"expired","expires></today()-14),(and(a1<>

 

[ace19852]

Ok I have no idea why my formula will not post correctly I will try to send it via private

=IF(OR(AND(A1="Lead",B1>TODAY()-7),(AND(A1<>"Lead",B1>TODAY()-
83))),"Active",IF(OR(AND(A1="Lead",B1<today()-14),(and(a1<>"Lead",B1<today()-
90))),"Expired","Expires this week"))</today()-
</today()-14),(and(a1<>

Cheers

 

[ace19852]

Ok have now sent you the formula via private mail. If anyone can tell me why I can paste a longish formula please let me know

Cheers