Home
Tips & Tricks
Excel Functions
Excel Formulas
Applied Formulas
Applied Functions
Excel Macros
Business Uses
Downloads
Excel Tests
Latest Updates
Recent Posts
About us
Q&A
3 different ways to calculate financial quarter
If your company financial quarters are not the same as the calendar quarters, there is a high chance that you are spending hours trying to classify the data into the relevant financial quarters. I know, because most of the participants who came to my courses do not know how. You are reading this, probably because you also do not know how. Let me share with you 3 different ways you could do this.
Method One
The first method is to use nested IF i.e. IF within another IF, together with the Month formula to find the different finanical quarters. Since there are only 12 months, using the IF formula, we can break up the numbers into the 4 different quarters. Let’s assume that the financia year starts on 1 April of every calendar year. In this case, Jan, Feb, Mar of every year is the financia; quarter 4. Using the MONTH formula, we can convert any dates into the relevant month numbers 1 to 12. Then using the IF formula, we can isolate the the first 3 months of each year (Jan, Feb and Mar) from the other 9 months. The first half of the formula (assuming that the date we have is in CEll A2) is
=IF(MONTH(A2)<=3,”FQ4″,”NOT FQ4″)
Using the above formula, Excel will separate the first months from the rest. In this simple usage of the IF formula, the month number 4 to 12 are classified as “Not Q4”. But instead of doing that, we can start another IF formula to further separate the numbers 4 to 12. This is known as NESTED IF. Replacing the part “NOT Q4 with the IF formula, we can separate the month number 4 to 6 from the remaining 6 months. The formula becomes
=IF(MONTH(A2)<=3,”FQ4″,
IF(MONTH(A2)<=6,”FQ1″,”left with 7 to 12″))
Remember to close the formula with 2 brackets, one for the first IF and another for the second IF. Using the same concept, we can replace the part “left with 7 to 12” with another IF formula. The formula becomes
=IF(MONTH(A2)<=3,”FQ4″,
IF(MONTH(A2)<=6,”FQ1″,
IF(MONTH(A2)<=9, “FQ2″,”Left with 10 to 12”)))
Remember to add another close bracket for the third IF formula. With the third IF, we are left with month number 10 to 12. This 3 months is actually the financial quarter “FQ3”. So instead of putting the text “left with 10 to 12”, we can straight away put int “FQ3” into the formula. The final formula becomes
=IF(MONTH(A2)<=3,”FQ4″,
IF(MONTH(A2)<=6,”FQ1″,
IF(MONTH(A2)<=9, “FQ2″,”FQ3”)))
Method 2
The formula in the second method is shorter but you would need to another formula in place of NESTED IF. Still starting with the IF formula, we have to segregate the first 3 months (Jan, Feb and Mar) from the rest.
=IF(MONTH(A2)<=3,4,”NOT 4″)
Instead of using NESTED IF for the subsequent part, we can divide the month formula by 3, i.e. MONTH(A2)/3. Doing so will yield 1.33, 1.67, 2, 2.33, …4. for the the month 4, 5, 6, 7, ….12. If we are to round up the results using the ROUNDUP formula, we will get 2,2,2,3,3,3,4,4,4 for the months 4,5,6,7,8,9,10,11,12.
ROUNDUP(MONTH(A2)/3)
But as we know, the FQ should be 1,1,1,2,2,2,3,3,3. To arrive at this result we can minus one from our division.
ROUNDUP(MONTH(A2)/3)-1
We can then replace the part “NOT FQ4” with this formula and it should become
=IF(MONTH(A2)<=3,4,ROUNDUP(MONTH(A2)/3)-1)
To get the text FQ before the quarter number, simply use the “&” key
=”FQ” & IF(MONTH(A2)<=3,4,ROUNDUP(MONTH(A2)/3)-1)
Method 3
The third method to find financial quarter involve the use of CHOOSE formula. When we use the MONTH formula to find the month number, there are 12 possible outcomes, from number 1 to 12. Using the CHOOSE formula, we can input 12 entries (one quarter number for each month) to be returned based on the result from the MONTH formula. Just like the earlier case, you can use the “&” key to join the text FQ to the CHOOSE formula result.
=”FQ” & CHOOOSE((MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
