Excel Custom Quarters

How to create Excel Custom Quarters

How to Create Custom Quarters in Excel – Overview

Excel custom quarters article aims to demonstrate how to create custom quarters in Excel. If you are planning to calculate regular quarters in Excel, there is a straight forward method that can be found in this article. However, if you are planning to have custom quarters based on your business needs, you will need to think of something different in order to create custom quarters in Excel.

We will be focusing on how to create custom quarters in Excel so that we can define our quarters starting from January of the current year. Assuming the Date as 01/Jan/2020 in Cell A2, we would like to have the custom Quarter as 2020Q1. We will also ensure that will fetch the custom quarter names irrespective of the year/date to make it useful for any date or year. Let us roll now!

How to Create Custom Quarters in Excel – Solution

To acquire the custom Quarters, we will be using the combination of following Excel functions:

  • IF Function [to confirm the running month and et the custom quarter name based on a condition]
  • Month Function [to extract the value of the running month and compare it with our custom/desired criteria]
  • AND Function [to perform test on multiple criteria and ensure all the given criteria are met]
  • Year Function [to extract the year value from the given date and prefix it to the custom quarter using the ‘&’]

Here is the formula we have used

=YEAR(A2)&IF(MONTH(A2)<4, "Q1", IF(AND(MONTH(A2)>3, MONTH(A2)<7), "Q2", IF(AND(MONTH(A2)>6, MONTH(A2)<10), "Q3", "Q4")))

Let us break it into small pieces to get a clear understanding of each section used in the formula mentioned above. We highly recommend you get familiar with the IF Function by visiting this article if you are not comfortable to use it right away.

YEAR(A2) &

The above function extracts the year value from the running date (assuming date value is in cell A2) and the ampersand symbol (&) at the end is used to append the extracted value to the output extracted using the function below.

Understanding the Nested IF used in the formula above

Since we are using Multiple IF statements, let us break them in small sections and name them (the name is mentioned in square brackets) to get a clear read.

  • IF(MONTH(A2)<4, “Q1”, [First IF Statement]
  • IF(AND(MONTH(A2)>3, MONTH(A2)<7), “Q2”, [Second IF Statement]
  • IF(AND(MONTH(A2)>6, MONTH(A2)<10), “Q3”, “Q4”))) [Third IF Statement]
First IF Statement

In the First IF Statement we are testing the running date (assuming the date value is in cell A2). To perform the test, we have placed the Month Function inside the IF Statement to extract the month value (month function always returns the month number from a given date) and test it whether it is less than number 4. If the test succeeds, we are instructing the First IF Statement to set the Quarter value as Q1 (this will set months 1 to 3 as Q1)

Second IF Statement

In the Second IF Statement we are testing the running date (assuming the date value is in cell A2). To perform the test, we have placed the Month Function inside AND Function. AND Function is testing the month number returned as a result of Month Function execution to meet two conditions i.e. whether the returned value is greater than 3 and less than 7. If both the conditions meet, we are instructing the Second IF Statement to set the quarter as Q2. (this will set the months 4 to 6 as Q2)

Third IF Statement

Moving on to the Third IF Statement, we are testing the running date (assuming the date value is in cell A2). To perform the test, we have placed the Month Function inside AND Function. AND Function is testing the month number returned as a result of Month Function execution to meet two conditions i.e. whether the returned value is greater than 6 and less than 10. If both the conditions meet, we are instructing the Third IF Statement to set the quarter as Q3. (this will set months 7 to 9 as Q3).

Finally, if none of the conditions Meet which makes it obvious that the month number is greater than or equal to 10 (10, 11 or 12), we are instructing the Third IF Statement to set the quarter value to Q4. (this will set the months 10 to 12 as Q4).

Assuming the Date as 01/Jan/2020 in Cell A2, the final output will be 2020Q1

Conclusion

We will soon produce a video demonstration of this article so that we can walk you through the steps.

We hope you find this article useful. In case you are stuck and or need help, feel free to drop a comment below or submit your question. Do share it further and add your feedback about the article in the comments section.

About Syed Hussaini

I am a Support Agent with 15+ Years of exposure in Customer and Technical Support, Team Management, Training, and Knowledge Management. My engagement in a corporate environment and freelance projects has taught me to be highly productive, proactive, and independent.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.