Results 1 to 5 of 5

Thread: Find Given Date Between Fiscal Years and Return Year

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post 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. #2
    Board Regular
    Join Date
    Jan 2012
    Location
    CHENNAI , India
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Nov 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Given Date Between Fiscal Years and Return Year

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

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default 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. #5
    New Member
    Join Date
    Nov 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Given Date Between Fiscal Years and Return Year

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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