Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Calculate Financial Year From Date

  1. #1
    Join Date
    2nd July 2004
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd July 2004
    Posts
    6

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  4. #4
    Join Date
    2nd July 2004
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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))

  7. #7
    Join Date
    21st August 2013
    Posts
    5

    Re: Calculate Financial Year Format From Date

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th August 2013
    Posts
    1

    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")

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    21st August 2013
    Posts
    5

    Re: Calculate Financial Year From Date

    Thanks RobertHM

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculate Year-to-date Percentage
    By Ediese in forum Excel General
    Replies: 4
    Last Post: October 12th, 2007, 12:26
  2. Calculate Date With Variable Year & Month
    By AAE in forum Excel General
    Replies: 6
    Last Post: August 18th, 2007, 09:44
  3. convert date field year xx to year xxxx
    By sophism in forum Excel General
    Replies: 4
    Last Post: January 17th, 2005, 17:34

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
  •  
porno