Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fiscal year function (Excel2003)

    Edited by HansV to provide link to post in the form <!t>[post#=406650]<!/t> - see <!help=19>Help 19<!/help>

    Last year, in <post#=406650>post 406650</post#> I got information on converting a date to a quarterly number. Our company has a 3/31 fiscal year end, and I'm looking for a way to take any date and convert it to a quarter-fiscal year number, so that 3/15/05 would be 04-05 and 12/15/04 would be 03-05 and 2/28/04 would be 04-04 and so on. Is there a practical way to do this in a formula? I could not find any search results on it, yet the problem must have been addressed in some way, since many companies are not calendar-year. Thanks for any thoughts.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fiscal year function (Excel2003)

    With a date in A1, you could use
    <code>
    =TEXT(INT((MONTH(EOMONTH(A1,9))+2)/3),"00")&"-"&TEXT(EOMONTH(A1,9),"yy")
    </code>
    You must have installed the Analysis Toolpak for this (in Tools | Add-Ins)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiscal year function (Excel2003)

    If you don't want to install the analysis tool pack, you can use a formula like this if the date is in A1:

    <pre>=IF(MONTH(A1)<4,"Q4",IF(MONTH(A1)<7,"Q1",IF(M ONTH(A1)<10,"Q2","Q3")))&"-"&IF(MONTH(A1)<4,TEXT(A1,"yy"),TEXT(DATE(YEAR(A1)+ 1,1,1),"yy"))
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiscal year function (Excel2003)

    That's great!. Thanks much. Woud either solution be usable in Access? I will probably have to migrate the concept into Access due to the number of records involved.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fiscal year function (Excel2003)

    You'd use the DateAdd function in Access instead of the EOMONTH function, and the Format function instead of TEXT.
    And the expression can probably be simplified in Access because Format can display the quarter.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiscal year function (Excel2003)

    This may be a new post item, but let me ask it here. I took the EOMONTH version, put it in my 15,000 row source, ran it. The Pivot table report was sorted by the quarter-number first. I then realized I should get the result the other way, as 95-04, so I successfully reversed the formula itself on the source. But the PT refuses to recognize the new field format, even if I drag the field off, refresh the table, and drag it back on, as the Contextures website suggested. Is there another way to purge the cache, or do I have to recreate the table?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fiscal year function (Excel2003)

    Doesn't refreshing the pivot table (using the ! button on the toolbar) work? If not, you may have to recreate the pivot table from scratch.

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiscal year function (Excel2003)

    Nope, I even redefined the table to exclude that column, re-ran the Wizard, then re-added the column and re-ran the Wizard again. Perhaps it is a text-thing: how can it sort if the cell value is a text formula? Can that formula become something like 95.01, 95.02 etc in a value format, so that the numeric sort will give the correct layers by year/quarter, maybe using the column next to it?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fiscal year function (Excel2003)

    Excel is perfectly capable of sorting text values that are the result of a formula. If you have text values of the form 95-03, they should be sorted in the correct order.

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fiscal year function (Excel2003)

    OK. Let me keep trying. I'll post tomorrow with results.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fiscal year function (Excel2003)

    Oh wait, if you have both years in this century and in last century, using a 2 digit year will sort correctly: 95 > 05, you should use the full four digit year ("yyyy" instead of "yy")

Posting Permissions

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