miercuri, aprilie 25, 2007

Calculated Field: Quarters

The Company has a fiscal year that starts on June 1. I need a field in my list that shows what quarter and year this date falls in, so that I can sort/filter by that data instead. The Company Fiscal Quarters can be calculated.

Create a new field, calculated, and put this formula in:

=IF(MONTH([TheDate])<=6,YEAR([TheDate]),YEAR([TheDate])+1)&" Q 0"&MOD(CEILING(22+MONTH([TheDate])-5-1,3)/3,4)+1

If you are trying to do a different fiscal, change the 5 to whatever month is the last month of the quarter, and change the 6 to the first month of the quarter (november in this case):

=IF(MONTH([TheDate])<=11,YEAR([TheDate]),YEAR([TheDate])+1)&" Q 0"&MOD(CEILING(22+MONTH([TheDate])-10-1,3)/3,4)+1

You can modify it by removing the year, which will just give the quarter number (november in this case):
=MOD(CEILING(22+MONTH([TheDate])-10-1,3)/3,4)+1

And of course, you can create another field that will just give you the fiscal year (november in this case):
=IF(MONTH([TheDate])<=11,YEAR([TheDate]),YEAR([TheDate])+1)

Have Fun with this new information!

Niciun comentariu:

Despre mine

Timi-Ostravia, Republic of Czedvia, Bulgaria
I make web applications. The tools I use are many and far between. I have been maining to learn Ruby and Python, and Perl. These tools are all like Lego to me. They can all be make to build things and have their strengths and weakest.