Lookup or whatever!

[Ronald Cayne]

Have a table with dates in a column, eg column D, depending on the the
number of days between dates I want to place in column E a comment such
as , "> 3 yrs", "2-3 yrs","1-2 yrs", ....,"151-180 da","<30 days" etc.
Best way SVP

 

[Bernard V Liengme]

Hi Ronald,
This should get you going - it will need fine tuning
In D1:E8 enter a table like this

0 < 30 days
30 1 month
60 2 month
90 3 month
180 6 month
360 1 year
720 2 year
1080 3 year


In A1 and A2 enter two dates (A1 the earlier date) such as 10-Jan-2003 or in
any valid Excel format
In B1 enter =VLOOKUP((A2-A1),$D1:$E$8,2)
This computes the difference in days, looks up that value in the first
column (D) of the table (locates closest match - read Help for details) and
returns text from corresponding cell in column E.

Best wishes
Bernard

 

[Ken Wright]

VLOOKUP will probably do it. Don't know how you are calculating the date difference, but ignoring
the possibility of being a day or two out at year cutoffs (How many days does a year have???), and
that you are calculating that difference in days, you can build a table along the lines of:-


A B
0 "<30 days"
30 "30 - 150 days"
150 "151 - 180 days"
180 "181 - 364 days"
364 "1 - 2 years"
729 "2 - 3 years"
1094 "Bit late - Hasn't paid for ages!!"

Then put your date difference calculation into the VLOOKUP formula

If you need more then post an example of your data and how you intend to calculate the date
difference (No attachments though).

 

[Jason Morin]

If your dates started in D1, put this in E2, and copy down:

=LOOKUP(D2-D1,{0,30,180,365,730},{"<30 days","30-180
days","6 mon-1yr","1yr-2yrs",">2 yrs"})

Adjust or expand accordingly.

HTH
Jason
Atlanta, GA