+ Reply to Thread
Results 1 to 5 of 5

if a date is within a range, provide specified text

  1. #1
    Registered User
    Join Date
    04-13-2010
    Location
    Fort Lauderdale, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    if a date is within a range, provide specified text

    Hello!

    I have a spreadsheet that includes the dates for specific events. I need to take the dates and translate them to the fiscal year in which they occurred. I have thousands of rows.

    Example:

    Cells: A2, B2

    Desired Result: A2 = Date (Given) then B2 = FY##

    If A2 is on or after 7/1/2013 and on or before 6/30/2014 then I want B2 to display FY14
    If A2 is on or after 7/1/2012 and on or before6/30/2013 then I want B2 to display FY13
    If A2 is on or after 7/1/2011 and on or before6/30/2012 then I want B2 to display FY12
    If A2 is on or after 7/1/2010 and on or before6/30/2011 then I want B2 to display FY11...

    I've tried various combinations of:
    =IF(AND(A2>=7/1/2010,A2<=6/30/11),"FY11",IF(AND(A2>=7/1/2011,A2<=6/30/12),"FY12",...IF(....,IF(...),"FY14","FALSE")))) but no matter what the date is in "A2", the result is FALSE

    I'm building this to analyze past events, but also to capture future fiscal year events for at least one year. Can anyone help?

    Thank you!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: if a date is within a range, provide specified text

    The term 7/1/2010 will be taken by Excel as meaning 7 divided by 1 divided by 2010. To prevent this happening you will need to put all your dates in the formula within quotes ("). However, that will turn them into text values, so you need then to convert the text values into proper dates - you can do this using the double unary minus (--), which essentially multiplies by -1 twice.

    So, your formula will become:

    =IF(AND(A2>=--"7/1/2010",A2<=--"6/30/2011"),"FY11",IF(AND(A2>=--"7/1/2011",A2<=--"6/30/2012"),"FY12",...IF(....,IF(...),"FY14","FALSE"))))

    You can simplify this considerably, though, by setting up a list of starting dates in one column, arranged in increasing order (let's say in column M), and then you can use a simple match formula. So, put these dates in M1, M2 etc:

    7/1/2010
    7/1/2011
    7/1/2012
    7/1/2013
    and so on ...

    Then you can use this formula in B2:

    =IF(A2="","","FY"&MATCH(A2,M:M)+10)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-13-2010
    Location
    Fort Lauderdale, FL
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: if a date is within a range, provide specified text

    Pete,

    Wow...just... WOW! Thanks so much. The first long formula definitely worked. I'm playing with the short version now. I never would have figured it out without the tips. Much appreciated.

    Marissa

  4. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: if a date is within a range, provide specified text

    Try this one without helper column

    =IF(A2="","","FY"&RIGHT(YEAR(A2)+(A2>=DATE(YEAR(A2),7,1)),2))

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: if a date is within a range, provide specified text

    hi Marissa, welcome to the forum. my personal preference would be to type dates using numbers indicating days, 3 letters for months, & numbers for years like this:
    1jul2010
    that's because --"7/1/2010" is translated as 1-Jul-10 in your computer region settings. if you send it to me, it will be 7-Jan-2010 as my region settings is DMY.

    an alternative solution would be:
    ="FY"&TEXT(EDATE(A2,6),"yy")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1