if statement with date range
 

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

if statement with date range
 

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

"Theresa" wrote:

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

if statement with date range
 

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)

In article ,
"Theresa" wrote:

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

if statement with date range
 

You're right, I omitted a code from the original list, my mistake.
The 5 categories a 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

"JE McGimpsey" wrote:

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)

In article ,
"Theresa" wrote:

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