if statement with date range

T

Theresa

Hi,

I have a column with varying dates, and I want to create a new column that
looks at the date column and decides what season code (CA,EW,SP,SU) it should
return.
i.e. EW=oct23-jan15, LW=jan16-mar31, etc.

Can someone please let me know what the IF statement should look like?

Thanks in advance,
Theresa
 
R

Ray A

Hi Theresa,
The solution is not the IF function as much as a VLOOKUP function.
Create a table of data in an empty section of the worksheet an define the
range name. Make sure the dates are sorted ascending
Example
10/23/2005 EW
1/16/2006 LW
4/1/2006 etc
Assuming the dates are in column A use =vlookup(a2,range_name,2,true)
HTH
 
J

JE McGimpsey

Your "etc." doesn't give enough information, since "LW" isn't one of
your listed season codes, so it's impossible to know what dates should
be used for CA, SP and SU.

One potential way:

Assume your dates are in column A.

Create a table with the season code start dates for an arbitrary year
(note that since one season overlaps year-end, you should have two
entries):

J K
1 Date Season
2 01/01/2005 EW
3 01/16/2005 LW
4 04/01/2005 SP
5 07/01/2005 SU
6 10/23/2005 EW

Then use the formula

B1: =VLOOKUP(DATE(YEAR($J$2),MONTH(A1),DAY(A1)),$J$2:$K$6,2, TRUE)
 
T

Theresa

You're right, I omitted a code from the original list, my mistake.
The 5 categories are: EW=oct23-jan15, LW=jan16-mar31, SP=Apr1-may23,
CA=may24-jun15, SU=jun16-oct22

I've used vlookup before, and for some reason never even thought of it as a
solution to this query...but of course, it's an elegant fix.

Thanks to you both for the solution.
Theresa
 
Top