Bucketing data based on DATE Range criteria
 

Hi all. Here's the situation... I have a list of numbers that I need to place
in one of four columns based on on the expiration date of the numbers. The
date ranges a

I. WIthin 1 Year
II. 1 to 3 years
III. 3 - 5 Years
IV. Over 5 Years

So basically if the exp. date for the number is within 1 year, it should be
in Category A, if its between 1-3 years then category B and so on..

The numbers are in col. A and exp. dates are in col. B. The buckets
(I,II,III,IV) are in col. C thru E.

I have already calculated what the actual dates should be for buckets I -
IV. I had to make that the formula since the date ranges are calculated based
on the current day (as of when the analysis is done).

Would appreciate any suggestions.

Thanks in advance.

sk

Bucketing data based on DATE Range criteria
 

Are you saying that you have a column of numbers, and an adjacent column
with their associated expiration dates that could range from 0 to five
years from the current date?
______________________________
Assume:
A1: NNNNNN
B1: Expiration Date
C1: "I" class numbers
D1: "II" class numbers
E1: "III" class numbers, &
F1: "IV" class numbers
_________________________________
Insert equations such that:
G:G =IF(INT(YEAR(B1)-YEAR(Now()))=0,A1,"")
H:H
=IF(AND(INT(YEAR(B1)-YEAR(Now()))0,INT(YEAR(B1)-YEAR(Now()))<3),A1,"")
I:I
=IF(AND(INT(YEAR(B1)-YEAR(Now()))2,INT(YEAR(B1)-YEAR(Now()))<6),A1,"")
J:J =IF(INT(YEAR(b1)-YEAR($B3))5,A1,"")
____________________________________
Ben


--
protonLeah
------------------------------------------------------------------------
protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097
View this thread: http://www.excelforum.com/showthread...hreadid=543843