Excel If Function over a range of dates
Solved/Closed
Related:
- Excel If Function over a range of dates
- Excel mod apk for pc - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- Find function on mac - Guide
- Kernel for excel - Download - Backup and recovery
3 responses
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
166
Feb 16, 2010 at 05:20 AM
Feb 16, 2010 at 05:20 AM
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"))))
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 16, 2010 at 04:32 AM
Feb 16, 2010 at 04:32 AM
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
>
Olan
Feb 16, 2010 at 04:52 AM
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
Feb 16, 2010 at 04:57 AM
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
>
Olan
Feb 16, 2010 at 05:11 AM
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
=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 January 28, 2010
Status
Contributor
Last seen
May 5, 2022
Feb 16, 2010 at 05:23 AM
Feb 16, 2010 at 05:23 AM
Thanks for your help and patience really appreciated. Night.
