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.

3 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
213
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 2841 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > 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 ?
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > 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
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

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()