Identifying fiscal year by date input

YJGUY

New Member
Joined
Jul 8, 2005
Messages
3
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
 

Some videos you may like

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
Joined
Jun 20, 2002
Messages
7,988
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
Joined
Mar 23, 2005
Messages
20,825
....or you could simplify Oaktree's suggestion to..

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

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,594
Members
412,537
Latest member
Mohamed_5966
Top