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>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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>
 
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

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

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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