Forum Discussion
If date is within a table of multiple date ranges return a value also in that table
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?
6 Replies
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)
- 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
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))- This works, but I was hoping to find a way to use the table and not hard code the formula.
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.
