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

Olan - Feb 16, 2010 at 04:23 AM - 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 

8 replies

Best answer
mubashir aziz 191 Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Feb 16, 2010 at 05:20 AM
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

Something to say? Add comment

CCM has helped 1818 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 16, 2010 at 04:32 AM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > Olan - Feb 16, 2010 at 04:52 AM
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 ?
Olan > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 16, 2010 at 04:57 AM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > Olan - Feb 16, 2010 at 05:11 AM
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
Olan > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 16, 2010 at 05:23 AM
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()