Fiscal Year and Fiscal Quarter

We end the fiscal year on May 31. Not only is it offset by some months compared to the calendar year, the number of months isn't divisible by 3 (a quarter), so calculating which quarter of the year a particular date is in becomes tiresome.

In this post, we will create a new calculated column in a list.

Calculating the Fiscal Year is kinda easy:

=IF(MONTH([Due Date])<6,YEAR([Due Date]),YEAR([Due Date])+1)

We are using an IF statement to decide whether or not the month number is less than 6 (June), and if not, add a year to it. My fiscal year begins on June 1st.

Calculating the Fiscal Month is a little more difficult:

="FQ 0"&MOD(CEILING(22+MONTH(date)-5-1,3)/3,4)+1

I can't really explain how that formula works, I found it online:

I Love Excel!: Calculate Calendar Quarter of Fiscal Quarter From Date

Put them together, and you can get a sortable field you can use in views:

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

This will give you the quarter number and the year of that quarter. Substitute the "[date]" in the formula above with whichever date column you want to calculate on.

So, "Due Date", a common date field in tasks and issues lists would be calculated as:

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

Please note that this formula is based on a fiscal year beginning in June. If you have a different fiscal starting month, you can replace the '5' in my formula with your fiscal month minus one. If your fiscal year begins in January, then what are you looking at this post for? Your fiscal year is the same as the calendar year!

Have fun!