Previous Topic Next Topic

Excel If Function over a range of dates

Solved/Closed
Olan - 16 Feb 2010 à 04:23
 Useful - 16 Feb 2010 à 09:02
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.
Related:

3 responses

Réponse 1 / 3
mubashir aziz Posts 190 Registration date Sunday 12 April 2009 Status Member Last seen 16 February 2010 166
16 Feb 2010 à 05:20
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"))))
Réponse 2 / 3
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Feb 2010 à 04:32
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
Olan
16 Feb 2010 à 04:43
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 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766 > Olan
16 Feb 2010 à 04:52
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 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022
16 Feb 2010 à 04:57
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 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766 > Olan
16 Feb 2010 à 05:11
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 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022
16 Feb 2010 à 05:23
Thanks for your help and patience really appreciated. Night.
Réponse 3 / 3
Useful
16 Feb 2010 à 09:02
And also if you need to make more if() functions than 8 you may use the function value() or Choose()

Similar discussions

Using IF function with dates
dkayeh1 -
RayH -

18 responses