Identifying fiscal year by date input

[YJGUY]

Here is my issue:

- I have two date fields "to" and "from". I want the formula to recognize the fiscal year in the fields and have a specific output. Example

from = 1990-05-03 to= 1991-02-29

output = $319

I'm not counting the number of days, just the fiscal year. Different fiscal years will have different outputs. For 1991-1992 the output will be $732

The to - from dates entered will always be in the same fiscal year so the formula will not have to try and split years.

I guess it would be an IF statement to read if date is more than 1990-03-31 AND less than 1991-04-01 output = $301 and I would do one for every year up to 2005.

Any insight would be great! I am using excel V10 (2002) if it matters.

Thanks,
Aaron

 

[Oaktree]

Assuming your FY goes from 4/1 to 3/31, try:

=LOOKUP(IF(MONTH(A2)<4,YEAR(A2),YEAR(A2)+1),{1991,319;1992,732})

Where A2 stores an Excel date. Adjust the numbers in the vlookup range (between the { }'s) to account for other years if necessary (format {year, value; next year, value; etc.} where each year is the ending year of the FY (etc. 1991-1992's "year" here would be 1992))

 

[barry houdini]

....or you could simplify Oaktree's suggestion to..

=LOOKUP(YEAR(A2)+(MONTH(A2)>3),{1991,319;1992,732})