How to return value if date falls between two date

[gaiter]

Hello Excel Gurus,

I'm hoping someone can help with a specific formula.

I have a sizeable database of entries that include the date at which a specific transaction was conducted. I'm trying to write a formula that will look at a given date and return a value based on the date range under which it falls.

For example, a transaction occurred on 04/22/2012 (in cell B2). In cell C2, I want to populate the corresponding Fiscal Year in which the transaction occurred. All Fiscal Years run 07/01/20xx to 06/30/20xx.

I have attached a sample file to help illustrate my problem, this is similar to the data I will be working with.

I tried to run an IF Statement but it did not function as I wanted it to. I tried

=if(and(C2>=F2,C2<=G2),H2,IF(…..)

Where ... denotes nested if statements repeating the initial AND() check.

If I need to elaborate further please let me know.

Thanks!

 

[Smallman]

Gaiter

Welcome to Chandoo forum

First of all your data in Column C is text not a date. Please convert the data in Col C to a date as no if statement will work the way you intend using text (dates are really numbers in discusie).

Put a table in another sheet with your fiscal years and the following formula;

=VLOOKUP(C2,List!$A$2:$B$17,2,1)

This should get you over the line.

Sample attached to show workings.

Take care

Smallman

 

[Somendra Misra]

Hi,

Here's my bit, as @Smallman indicated the dates in your column C, F & G are text, if you convert them to proper dates, than use below formula to get fiscal year.

=LOOKUP(99,1/((C2>=$F$2:$F$12)*(C2<=$G$2:$G$12)),$H$2:$H$12) in C2 and copy down.

Use can use Text to Column feature under Data tab to convert the text to date quickly.

Just select column (one at a time) go to Text to column , press Next two times and than select Date (MDY) and press Finish.

Alternatively, if you just convert column C to date you can use below formula with no tables required:

=IF(MONTH(C2)<7,"FY"&YEAR(C2),"FY"&YEAR(C2)+1) in C2 and copy down.

Regards,