+ Reply to Thread
Results 1 to 6 of 6

Calculate Fiscal Year FY of a single date

  1. #1
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Calculate Fiscal Year FY of a single date

    Good afternoon! I have a s/s listing jobs and completion dates over a period of several years. I need to find the FY for the date each job was completed. The FY period is from 01 Oct 20xx to 30 Sept 20yy. So, I'm looking for a formula that will return the FY of oh, lets say, 28 Oct 2012 as FY13. Or simply 13. The FY part would be a bonus but is not required.
    Last edited by Steve N.; 10-29-2012 at 12:42 PM. Reason: Solved

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Calculate Fiscal Year FY of a single date

    With
    A1: a date

    This regular formula returns the fiscal year (Oct through Sep) of that date
    B1: ="FY"&YEAR(EDATE(A1,3))

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: Calculate Fiscal Year FY of a single date

    Excellent! Thank you. How do I make it return FY13 rather than FY2013?

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Calculate Fiscal Year FY of a single date

    ="fy"&year(edate(a1,3))-2000
    Ben Van Johnson

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate Fiscal Year FY of a single date

    Try this formula

    ="FY"&TEXT(A1+92,"y")
    Audere est facere

  6. #6
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: Calculate Fiscal Year FY of a single date

    All formulas work well. Thank you all. I particularly like ="FY"&TEXT(A1+92,"y") as it returns 2008 as 'FY08' vice 'FY8'. Any insight on the syntax?

    Thanks again!
    Last edited by Steve N.; 10-29-2012 at 12:46 PM. Reason: Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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