I had a recent request from a colleague who wanted to arrange a bunch of dates in to fiscal quarters. So, I went about creating an Excel workbook that would implement the same with the freedom of selecting the starting month of the fiscal year and decided to post it here along the lines of my previous tutorial. The following steps explain the logic behind my implementation.
Note that the steps below refer to the formula in cell C5. Hence B5 refers to the input date whereas the fiscal start month is captured in the cell C2.
Step 1: Input for FY Start Month
The ‘FY Start Month” is the only input to this sheet and enables adaptation to any fiscal year. To prevent errors, I used Data Validation to limit the inputs to whole numbers ranging from 1 to 12.
Step 2: Calculating Fiscal Year
To calculate the fiscal year I used the simple logic wherein if the month of the date is equal to or greater than the fiscal starting month then the fiscal year is incremented by one compared to the calendar year or else it remains the same.
IF(MONTH(B5)-$C$2>=0,YEAR(B5)+1,YEAR(B5))
This works for all scenarios apart from when the fiscal year is same as the calendar year since in that case we have to create an exception where the fiscal year is same as the calendar year. This is done with the help of the additional IF statement.
IF(MONTH(B5)-$C$2>=0,IF($C$2=1,YEAR(B5),YEAR(B5)+1),YEAR(B5))
Step 3: Calculating Fiscal Quarter
To identify the quarter, I decided to go with the CHOOSE function which makes it imperative that the calendar months are rearranged to fiscal months.
The difference in the numerical value between the calendar month and the fiscal month can range from -11 (1 minus 12) to +11 (12 minus 1). Hence the logic below offsets the value such that it lies between 1 to 12. This is done by adding 13 whenever the difference is negative and adding 1 whenever the difference is positive.
CHOOSE(IF(MONTH(B5)-$C$2<0,13+MONTH(B5)-$C$2,1+MONTH(B5)-$C$2),”Q1″,”Q1″,”Q1″,”Q2″,”Q2″,”Q2″,”Q3″,”Q3″,”Q3″,”Q4″,”Q4″,”Q4″)
Step 4: Combination of fiscal quarter and year
The final step is to join the two formulae with the fiscal quarter leading the fiscal year with suitable spacing.
CHOOSE(IF(MONTH(B5)-$C$2<0,13+MONTH(B5)-$C$2,1+MONTH(B5)-$C$2),”Q1″,”Q1″,”Q1″,”Q2″,”Q2″,”Q2″,”Q3″,”Q3″,”Q3″,”Q4″,”Q4″,”Q4″)
&” “&
IF(MONTH(B5)-$C$2>=0,IF($C$2=1,YEAR(B5),YEAR(B5)+1),YEAR(B5))
Since Google Sheets supports the same semantics as Microsoft Excel in this case, you can access the same using this link.
