If date is within a table of multiple date ranges return a value also in that table

[JBorner]

Example data

Start date       End date       Fiscal year

8/1/2009	7/31/2010	2010
8/1/2010	7/31/2011	2011
8/1/2011	7/31/2012	2012
8/1/2012	7/31/2013	2013

I want to be able to enter a date into a cell, such as 1/1/2012. I'm looking for a formula that will return 2012 (this is the fiscal year the date falls into).

How can I accomplish this?

[#2]

@JBorner -Please check this! 

=YEAR(End Date)

[#3]

@JBorner Let's say the date you want to 'calculate' the fiscal year for is in A1, they this:

=IF(MONTH(A1)>7,YEAR(A1)+1,YEAR(A1))

[#4]

@JBorner I may be a bit late to respond, but I'll still provide an answer.

To determine the fiscal year, you can easily extract the year from the END date using the YEAR function.

Formula = YEAR(End Date)

While there are various methods, simplicity often enhances understanding.

Please consider giving it a 'thumbs up' if the response was helpful for you. (external links removed by moderator)

[#5]

This doesn't work. As noted in the example, the year is fiscal and not calendar year so 9/1/2011 is part of year 2012

[#6]

This works, but I was hoping to find a way to use the table and not hard code the formula.

[#7]

@JBorner Normally I would agree to use lookup tables, but in this case it's not really necessary. But if you insist, here you go. In its simplest form use LOOKUP as shown in the picture.