Announcement

Collapse
No announcement yet.

Calculate Financial Year From Date

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate Financial Year From Date

    How do I calculate/display the financial year for a given date? For instance if my source cell has 25/11/2006 how do I get the result cell to show 2006/07 (or 06/07, 2006/2007, etc).

    I've tried a Text() function but the best 'format_text' I could come up with was "yyyy/yyyy+1", which not only doesn't work, but would be wrong if the source date was in the second half of the financial year.

    Thank you

    mcalex

  • #2
    Re: Calculate Financial Year Format From Date

    does no answer mean:
    a) it's too hard & I've gotta muck around with VBA code to interrogate the date cell, work out if it's in the first or second half of the year and then produce two year values which I can concatenate to produce a financial year format, or
    b) it's too easy, there's a function for this, and I should go away and look in the manual/help somewhere?
    c) it's been asked before & I should search the posts? (which i did, so if this is it, I've used the wrong keywords)

    Comment


    • #3
      Re: Calculate Financial Year Format From Date

      Hi,

      Try,

      =IF(MONTH(A1)<4,YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"-"&RIGHT(YEAR(A1)+1,2))

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Calculate Financial Year Format From Date

        Hi Krishnakumar

        Thank you, that's got it. Minor fix to make June/July the crossover months (not March/April - were you thinking FBT financial year?), and change the "-" to "/" to give a slash between the years, but apart from that, just what I wanted.

        cheers

        mcalex

        Comment


        • #5
          Re: Calculate Financial Year Format From Date

          Hi,

          =IF(MONTH(A1)<7,YEAR(A1)-1&"/"&RIGHT(YEAR(A1),2),YEAR(A1)&"/"&RIGHT(YEAR(A1)+1,2))

          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Calculate Financial Year Format From Date

            Another way, not tested too much though.

            =YEAR(DATE(YEAR(A1),MONTH(A1)-6,1)) & "/" & YEAR(DATE(YEAR(A1),MONTH(A1)+6,1))

            Comment


            • #7
              Re: Calculate Financial Year Format From Date

              Originally posted by Dave Hawley View Post
              Another way, not tested too much though.

              =YEAR(DATE(YEAR(A1),MONTH(A1)-6,1)) & "/" & YEAR(DATE(YEAR(A1),MONTH(A1)+6,1))
              Hi Dave
              Your formula above works perfectly in an excel worksheet, but I'm struggling to get it to work in my VBA code
              Ive tried a few variations of this:

              strFYno ="FY" & Right(Year(Date(Year(Now),Month(Now)-3,1)),2)

              but keep getting a compile error, "Expected: )"

              What am I doing wrong?

              Thanks

              Comment


              • #8
                Re: Calculate Financial Year Format From Date

                hey swrider here's what you need...

                strFYno ="FY" & Format(DateSerial(Year(Now), Month(Now) - 3, 1), "yy")

                Comment


                • #9
                  Re: Calculate Financial Year From Date

                  Thanks RobertHM

                  Comment

                  Working...
                  X