# Thread: Fiscal year function (Excel2003)

1. ## 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. ## 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. ## 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>

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Fiscal year function (Excel2003)

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

11. ## 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
•