Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula to display year based on the financial year

    Hi Excel Experts,

    Our financial year is Oct-Sept. I have been consolidating and reporting data based on this year on year. Now I would like to have a seperate worksheet that I could use to do a quick comparison each year. What is the best way of doing this without merging the three files.

    I would like to just have the report and graphs.

    One of the immediate formula i am looking is : for Oct-09 to Sept-10 i would like to have a formula that will display year as 2009-2010
    So that I could use the Month and use a drop down for the years 2009-2010, 2010-2011 and 2011-2012.

    Can someone help me with a formula for this.

    Regards
    Baiju

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Baiju,

    This code will generate an array that you can use to generate the list for your dropdown.
    Code:
    Option Explicit
    
       Public zFYs(20) As String   '*** Use this array to initialize your dropdown ***
    
    Sub CalcFYDates()
    
       Dim dteMinDate  As Date
       Dim dteMaxDate  As Date
       Dim iFirstFY    As Integer
       Dim iLastFY     As Integer
       Dim iCntr       As Integer
       
       dteMinDate = WorksheetFunction.Min(Range("FYDates"))
       dteMaxDate = WorksheetFunction.Max(Range("FYDates"))
       
       iFirstFY = IIf(Month(dteMinDate) < 10, Year(dteMinDate) - 1, Year(dteMinDate))
       iLastFY = IIf(Month(dteMaxDate) > 9, Year(dteMaxDate) + 1, Year(dteMaxDate))
       
       For iCntr = iFirstFY To iLastFY - 1
          zFYs(iCntr - iFirstFY) = Format(iCntr, "####") & "-" & Format(iCntr + 1, "####")
    '      Debug.Print zFYs(iCntr - iFirstFY)
       Next iCntr
       
    '   MsgBox "Mim Date: " & Format(dteMinDate, "mm/dd/yy") & vbCrLf & _
    '          "Max Date: " & Format(dteMaxDate, "mm/dd/yy") & vbCrLf & _
    '          "Start First FY: " & iFirstFY & vbCrLf & _
    '          "End   Last FY: " & iLastFY, _
    '          vbOKOnly + vbInformation, "Date Range"
              
    End Sub    '*** CalcFYDates ***
    You can uncommet the Debug & MsgBox code for debugging and delete them when you are satisfied that it works.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the date is in A1, you could use a formula like:
    =YEAR(A1)-(MONTH(A1)<10)&"-"&(YEAR(A1)+(MONTH(A1)>9))

    Steve

Posting Permissions

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