# Identifying fiscal year by date input

#### YJGUY

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Oaktree

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

Code:
=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

##### MrExcel MVP
....or you could simplify Oaktree's suggestion to..

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

Replies
0
Views
33
Replies
7
Views
411
Replies
1
Views
188
Replies
1
Views
273
Replies
7
Views
165