Note: if you just need to calculate a “normal” quarter based on a quarter system that starts in January, you can use this simpler formula. In the example shown, the formula in cell D5 is: This formula returns a number, 1-4 that corresponds to a quarter system that begins in April and ends in March. In the case of fiscal quarters, we can use this same idea to map any incoming month (1-12) to one of 4 quarter values. We just need to use the MONTH function to get the month number as the first argument, then provide 12 numbers (one for each month of the year) that are carefully ordered to reflect the fiscal year desired:
Adding a Q and year
If you want the quarter number to include a “Q” with a year value, you can concatenate: Will return values like: “Q1 - 2016”, “Q2 - 2016”, etc. This works for fiscal years with a January start. If the starting month is different from January, you can use an expression like this to calculate the fiscal year: This formula is explained in more detail here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
