Excel If Function over a range of dates [Solved/Closed]

Report
-
 Useful -
Hello,
Can anyone help please? I am trying to add a divisions column, which are taken from a date range in another column. i.e. I would like to add "Scb", years 1994-1995, "J" 1996-1997, "Y" 1998-1999 and "E" over 1999. Is there anyway I can do this? Any help would be appreciated. thank you.
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
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"))))



3
Thank you

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

CCM 5782 users have said thank you to us this month

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
May be I am thickheaded, just having a hard time understanding what you seek. I do not get what you mean by "add SCB, and years ? May be a sample data and what you expect as result of that sample data might help
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.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755 > Olan
Sorry Olan, but I am not getting what is "ScB" This term is not ringing bell and when you say give true value of ScB or J or E. what are these acronyms ?
> rizvisa1
Posts
4475
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
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755 > Olan
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
> rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

Thanks for your help and patience really appreciated. Night.
And also if you need to make more if() functions than 8 you may use the function value() or Choose()