# Thread: Find Given Date Between Fiscal Years and Return Year Thanks:  2 Post #4942075 (1)Post #4942054 (1) Likes:  1 Post #4942054 (1)

1. ## Find Given Date Between Fiscal Years and Return Year

I have a Training Spreadsheet which houses all training courses held for a company throughout the year.
I want to be able to filter (slicer) by year, so I have created a table of fiscal years (dates) and the year that I would like to return as below:

Fiscal Start Fiscal End Return this Value
Column X Column Y
Column Z
01-Oct-14 30-Sep-15 "2014 - 15"
01-Oct-15 30-Sep-16 "2015 - 16"
01-Oct-16 30-Sep-17 "2016 - 17"
01-Oct-17 30-Sep-18 "2017 - 18"
01-Oct-18 30-Sep-19 "2018 - 19"
01-Oct-19 30-Sep-20 "2019 - 20"

The training date is in column B and I wish to return the value in column A

​I have tried a variety of IF AND combinations but cannot find the correct formula

2. ## Re: Find Given Date Between Fiscal Years and Return Year

Hi ,

If you have already set up your table the way you have posted it , then a simple VLOOKUP can return the fiscal year text string ; for instance , I copied your data into the range \$E\$5:\$G\$10 ; I removed the quotes from the fiscal year text strings.

Now , if you have any date in the period 01-Oct-2014 through 30-Sep-2020 , using the following formula will return the corresponding fiscal year text string :

=VLOOKUP(date value,\$E\$5:\$G\$10,3,TRUE)

where date value would refer to the worksheet cell which has the data value for which you want the fiscal year.

3. ## Re: Find Given Date Between Fiscal Years and Return Year

WOW I didn't even think about a VLOOKUP formula, thank you NARAYANK991

4. ## Re: Find Given Date Between Fiscal Years and Return Year

try this

Excel 2013/2016
ABXYZ
1yeardate
22014 - 1509-Jan-1501-Oct-1430-Sep-152014 - 15
32014 - 1508-Jun-1501-Oct-1530-Sep-162015 - 16
42015 - 1605-Nov-1501-Oct-1630-Sep-172016 - 17
52015 - 1603-Apr-1601-Oct-1730-Sep-182017 - 18
62015 - 1631-Aug-1601-Oct-1830-Sep-192018 - 19
72016 - 1728-Jan-1701-Oct-1930-Sep-202019 - 20

Sheet2

Worksheet Formulas
CellFormula
A2=INDEX(\$Z\$1:\$Z\$7,SUMPRODUCT(ROW(\$A\$1:\$A\$7),(B2>=\$X\$1:\$X\$7)*(B2<=\$Y\$1:\$Y\$7)))

5. ## Re: Find Given Date Between Fiscal Years and Return Year

Thanks AlanY....all sorted now.Brilliant responses thank you all

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•