# Beyond frustrated with this dates,lookups?

#### cam87

##### New Member
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

The rules

 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>

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

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

#### vds1

##### Well-known Member
Try,

B2=Date Value

Except for Lead rest all conditions remains same. If its not then my formula is not reliable..

#### cam87

##### New Member
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

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

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

#### cam87

##### New Member
thank you for posting, however i do not follow your logic, could you please try to explain?

#### oldbrewer

##### Board Regular
look at my reply again - the principle works - expand it with

#### cam87

##### New Member
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

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

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

#### ace19852

##### Active Member
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"))
<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

90))),"Expired","Expires this week"))

</today()-
</today()-14),(and(a1<>

Last edited: