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

Lead	<7	Active
Lead	Between 7 and 14	Expires this week
Lead	>14	expired
Potential	<83	Active
Potential	Between 83 and 90	Expires this week
Potential	>90	Expired
Imminent deal	<83	Active
Imminent deal	Between 83 and 90	Expires this week
Imminent deal	>90	Expired
On Hold	<83	Active
On Hold	Between 83 and 90	Expires this week
On Hold	>90	Expired
Opportunity	<83	Active
Opportunity	Between 83 and 90	Expires this week
Opportunity	>90	Expired

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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