Beyond frustrated with this dates,lookups?

C

cam87

New Member
Joined
Jun 12, 2014
Messages
5
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]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
[TABLE="width: 820"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lead[/TD]
[TD="align: right"]1[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lead[/TD]
[TD="align: right"]7[/TD]
[TD="colspan: 2"]exp this week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lead[/TD]
[TD="align: right"]15[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lead[/TD]
[TD="align: right"]999[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]today's date =[/TD]
[TD="align: right"]12/06/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lead[/TD]
[TD="align: right"]02/05/2014[/TD]
[TD]41[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]formula is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=VLOOKUP(F13,leadtable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try,

=LOOKUP(DATEDIF(B2,TODAY(),"d"),IF(A2="Lead",{0,8,15},{0,84,91}),{"Active","Expires This week","Expired"})

Where A2=Lead/Potential/....
B2=Date Value

Except for Lead rest all conditions remains same. If its not then my formula is not reliable..
 
Upvote 0
thank you for your assistance, unfortunetly that did not work. anything else you could think of? i have tried to make a seprate table to use as a vlookup, but i cant get it to find both the days and the "lead" for instance to give me the comment. see example below

[TABLE="width: 351"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]day Difference[/TD]
[TD] Comment[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]1[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]2[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]3[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]4[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]5[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]6[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD]Lead (5%)[/TD]
[TD="align: right"]7[/TD]
[TD] Active[/TD]
[/TR]
</tbody>[/TABLE]


And for whatever reason, index match wont allow me to do anything either
 
Upvote 0
thank you for posting, however i do not follow your logic, could you please try to explain?
 
Upvote 0
look at my reply again - the principle works - expand it with

=if(A1="lead", lookup....... , if(A1="potential", lookup....

use leadtable, potentialtable etc etc
 
Upvote 0
okay, i have tried to understand what you mean, but either i dont or im doing it incorrectly. because i am just getting errors none the less
 
Upvote 0
=IF(A1="Lead";VLOOKUP(L3;Lead[[#All];[Column2]:[Column3]];2;IF(B1="Potential";VLOOKUP(B1;Potential[[#All];[Column2]:[Column3]];2))))

this is what i get, i tried the plain lookup, doesnt work. tried vlookup but it only works regarding with the Lead put not the rest.

B1 is the number of dates difference between the start date and end date
 
Upvote 0
[TABLE="width: 1025"]
<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Lead[/TD]
[TD]<7[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD="align: right"]12/06/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]leadtable[/TD]
[TD="align: right"]1[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]Between 7 and 14[/TD]
[TD="colspan: 2"]Expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="colspan: 2"]expires this week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]>14[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]999[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]Between 83 and 90[/TD]
[TD="colspan: 2"]Expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Imminent deal[/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Imminent deal[/TD]
[TD]Between 83 and 90[/TD]
[TD="colspan: 2"]Expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]gentable[/TD]
[TD="align: right"]1[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Imminent deal[/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83[/TD]
[TD="colspan: 2"]expires this week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]91[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]Between 83 and 90[/TD]
[TD="colspan: 2"]Expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]999[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]On Hold[/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opportunity[/TD]
[TD]<83[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opportunity[/TD]
[TD]Between 83 and 90[/TD]
[TD="colspan: 2"]Expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opportunity[/TD]
[TD]>90[/TD]
[TD]Expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]category[/TD]
[TD]date[/TD]
[TD]today-date[/TD]
[TD]status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]20/05/2014[/TD]
[TD]23[/TD]
[TD]expired[/TD]
[TD]########[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]23/05/2014[/TD]
[TD]20[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]26/05/2014[/TD]
[TD]17[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]29/05/2014[/TD]
[TD]14[/TD]
[TD]expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]01/06/2014[/TD]
[TD]11[/TD]
[TD]expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]04/06/2014[/TD]
[TD]8[/TD]
[TD]expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]07/06/2014[/TD]
[TD]5[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]08/06/2014[/TD]
[TD]4[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]09/06/2014[/TD]
[TD]3[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lead[/TD]
[TD]10/06/2014[/TD]
[TD]2[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]01/02/2014[/TD]
[TD]131[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]16/02/2014[/TD]
[TD]116[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]03/03/2014[/TD]
[TD]101[/TD]
[TD]expired[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]18/03/2014[/TD]
[TD]86[/TD]
[TD]expires this week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]02/04/2014[/TD]
[TD]71[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]17/04/2014[/TD]
[TD]56[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]02/05/2014[/TD]
[TD]41[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]17/05/2014[/TD]
[TD]26[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]01/06/2014[/TD]
[TD]11[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Potential[/TD]
[TD]02/06/2014[/TD]
[TD]10[/TD]
[TD]active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]formula in cell marked ######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]=IF(A20="lead",VLOOKUP(C20,leadtable,2),VLOOKUP(C20,gentable,2))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi and welcome to the board.

In C1

Try
<today()-14),(and(a1<><today()-90))),"expired","expires this="" week"))
<today()-14),(and(a1<><today()-90))),"expired","expires this="" week"))
=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()-14),(and(a1<><today()-90))),

</today()-90))),
</today()-14),(and(a1<></today()-90))),"expired","expires></today()-14),(and(a1<></today()-90))),"expired","expires></today()-14),(and(a1<>
and copy down

Cheers

=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"))

=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<>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J
K
  • Solved
Conditional formatting - highlight entire column when one cell contains date between today and 7 days prior
Replies
2
Views
905
Kurtin
K
V
  • Question Question
Calculate days between date or directly days comparison
Replies
2
Views
261
vishu
V
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
688
portews
P
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
354
Tetra201
Tetra201

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top