Beyond frustrated with this dates,lookups?

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

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

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Lead1active
Lead7exp this week
Lead15expired
Lead999expired
today's date =12/06/2014
Lead02/05/201441expired
formula is
=VLOOKUP(F13,leadtable,2)

<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,187
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..
 

cam87

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

nameday Difference Comment
Lead (5%)1 Active
Lead (5%)2 Active
Lead (5%)3 Active
Lead (5%)4 Active
Lead (5%)5 Active
Lead (5%)6 Active
Lead (5%)7 Active

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


And for whatever reason, index match wont allow me to do anything either
 

cam87

New Member
Joined
Jun 12, 2014
Messages
5
thank you for posting, however i do not follow your logic, could you please try to explain?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
look at my reply again - the principle works - expand it with

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

use leadtable, potentialtable etc etc
 

cam87

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

cam87

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

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Lead<7Active12/06/2014leadtable1active
LeadBetween 7 and 14Expires this week7expires this week
Lead>14expired15expired
Potential<83Active999expired
PotentialBetween 83 and 90Expires this week
Potential>90Expired
Imminent deal<83Active
Imminent dealBetween 83 and 90Expires this weekgentable1active
Imminent deal>90Expired83expires this week
On Hold<83Active91expired
On HoldBetween 83 and 90Expires this week999expired
On Hold>90Expired
Opportunity<83Active
OpportunityBetween 83 and 90Expires this week
Opportunity>90Expired
categorydatetoday-datestatus
Lead20/05/201423expired########
Lead23/05/201420expired
Lead26/05/201417expired
Lead29/05/201414expires this week
Lead01/06/201411expires this week
Lead04/06/20148expires this week
Lead07/06/20145active
Lead08/06/20144active
Lead09/06/20143active
Lead10/06/20142active
Potential01/02/2014131expired
Potential16/02/2014116expired
Potential03/03/2014101expired
Potential18/03/201486expires this week
Potential02/04/201471active
Potential17/04/201456active
Potential02/05/201441active
Potential17/05/201426active
Potential01/06/201411active
Potential02/06/201410active
formula in cell marked ######
=IF(A20="lead",VLOOKUP(C20,leadtable,2),VLOOKUP(C20,gentable,2))

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>
 

ace19852

Active Member
Joined
Feb 26, 2010
Messages
471
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,371
Messages
5,444,064
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top