Olan - - Latest reply: Useful - Feb 16, 2010 at 09:02 AM

Related:

- Excel if function over a range of dates
- Excel If Function over a range of dates - How-To - Excel
- Apply an IF Function Using Dates and Text - How-To - Excel
- Excel if function dates greater than ✓ - Forum - Office Software
- Excel "IF" function w/ date in test cell ✓ - Forum - Excel
- Excel - Copy row if a range of column matches a value - How-To - Excel

Best answer

mubashir aziz

- Posts
- 191
- Registration date
- Sunday April 12, 2009
- Last seen
- February 16, 2010

Suppose your values are in Cell A2 then put this formula in Cell B2 and drag it down

B2==IF(AND(YEAR(A2)>=1994,YEAR(A2)<=1995),"SCb",IF(AND(YEAR(A2)>=1996,YEAR(A2)<=1997),"J",IF(AND(YEAR(A2)>=1998,YEAR(A2)<=1999),"Y",IF(YEAR(A2)>1999,"E"))))

A few words of thanks would be greatly appreciated. Add comment

has helped 2585 users this month

Sorry, in one column I have year of birth (not dates only the year). I would like to add another column which refers to the year of birth and then gives a true value of ScB or J or E etc. My problem is that ScB etc are over a 2 year period, so I can't put > because it will pick up other divisions. Sorry not explaining this very well.

Olan
>
rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

I have a data base of amateur boxers. If their year of birth is 1994&1995 then they are Schoolboys (ScB). If their year of birth is 1996&1997 they are Juniors (J). If their year of birth is 1998-1998 they are Youth (Y). Over 1999 they are Elite (E). I would like to add a column which refers to the column with their year of birth and then gives me their divisions. This worked beautifully when I could just refer to their age, but now that years only are used, I can't get the formula correct. Any help appreciated.

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

Assuming date is in column A and first row is a header. So if the date is in A2 cell, in B2 you can write this

=IF(A2>=1999, "E", IF(A2>1997, "Y", IF(A2>1995, "J", IF(A2>1993, "ScB",""))))

As a side note, hard coding year is perhaps not a good idea, coz next year you have to fix the formula again

=IF(A2>=1999, "E", IF(A2>1997, "Y", IF(A2>1995, "J", IF(A2>1993, "ScB",""))))

As a side note, hard coding year is perhaps not a good idea, coz next year you have to fix the formula again