If Statement for Date between Date Ranges

[emorris]

I am working with a spreadsheet that has 3 date ranges. One workbook contains income data based on dates from 3/1/2008 through 3/31/2009; another contains income data between 4/1/2009 and 5/31/2010; and another contains income data based after 6/1/2010. The main workbook has dates ranging over the span of 3/1/2008 through 7/31/2010. Beside each date, I need to insert the correct income data based on . Can anyone please help in creating an IF, Then statement that would look at each date and returns the proper Income for that date range.

For example, if the date show 7/12/2008, the formula would go to the correct workbook, and return the correct income data.

=IF( DATE is between A and B, go to Workbook A to retrieve income data, IF Date is between C and D, go to Workbook B to retrieve income data, and if Date is between E and F, go to Workbook C to retrieve income data.)

Thanks!

 

[Hohn]

WorkBOOKS or work sheets? If the data ranges are in the same workbook on three separate worksheets, the job is marginally simpler.

JH

 

[emorris]

The data ranges are in the same workbook on three separate worksheets.

Thanks,
Ed

 

[Hohn]

You could use a nested IF statement, I'd think.

Something like:
=IF(3/1/2008<DATE<3/31/2009, "Sheet1"!lookupfunction,IF(4/1/2009<DATE<5/31/2010,"Sheet2"!lookupfunctionofchoice,IF(DATE>6/1/2010,"Sheet3"!yetanotherlookupfunction,))))

Just a rough suggestion.

 

[emorris]

To clarify, I am trying to determine how to write the IF statement to incorporate a date between 2 other dates:

Let's say Cell A1 is "July, 31, 2009"

=IF(A1 is in-between 3/1/2008 through 3/31/2009, then use Sheet3, else
=IF(A1 is in-between 4/1/2009 and 5/31/2010, then use Sheet4, else
=IF(A1 is in-between 6/1/2010 through 7/31/2010, then use Sheet5)))

Thus, it would know to go to Sheet4 to obtain the data. Question is, how do you write the IF statement to actually do this?

Thanks,
Ed

 

[Hohn]

You put the reference to the worksheet into the "value if true" part of the IF statement.

If you want to refer to another sheet, just put the worksheet in front of the reference.

let's say your income data for sheet 3 is in col A.

Then you'd do something like:
=IF(3/1/2008<date<3/31/2008,Sheet3!A, so forth.

If you're struggling to get the "between" date because Excel doesn't seem to like a logi test like 3/1/2008<date<3/31/2008, then use an AND statment.

An AND statement returns true if all conditions are true.

Then it would look like this:

=IF(AND(Date>3/1/2008,Date<3/31/2008),value if true,value if false.


In effect, this is saying: "If the "Date" is both greater than 3/1/2008 AND less than 3/31/2008, then this is TRUE, and you can return the "value if true".

The "value if true" part of the expression is where you'd put your reference to the worksheet 3 or such.

JH

 

[emorris]

Fantastic! This is what I was looking for.

Thanks very much!
Ed