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

- - Latest reply:  Useful - Feb 16, 2010 at 09:02 AM
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.
See more 

3 replies

Best answer
Posts
191
Registration date
Sunday April 12, 2009
Last seen
February 16, 2010
3
Thank you
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"))))



Thank you, mubashir aziz 3

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

CCM has helped 2585 users this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
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
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> 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
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
> 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
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Thanks for your help and patience really appreciated. Night.
0
Thank you
And also if you need to make more if() functions than 8 you may use the function value() or Choose()