Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Help! Selecting data according to date range | Excel Discussion (Misc queries) | |||
Count cells based on date range in another column | New Users to Excel | |||
How do I set a date range for conditional formatting in a macro? | Excel Worksheet Functions |