When doing any work with dates in Excel, often times you'll want to be able to split up your date into separate quarters. There is no built-in way of doing this (outside of PivotTabels), so let's go over a few different methods to calculate the correct quarter from any date.
Contents:
Method 1 - Standard Calendar Quarters
Method 2 - Irregular or Fiscal Calendar (2 Examples)
Methods
1. How to Pull Quarter From a Date on a Standard Calendar Year
In this example, let's go over a few different methods showing how to pull a formatted and unformatted quarter number out of any date.
Here are the formulas you'll need:
Return just quarter from date:
= ROUNDUP(MONTH(date)/3,0)This formula will return the specific quarter number to which each date belongs.
Return quarter with a Q in front:
= "Q" & ROUNDUP(MONTH(date)/3,0)If instead we want to format our quarters to look a bit more standard, we can concatenate a Q in front of each quarter using the "&" sign to join two separate pieces. The first being a "Q" and the second being the quarter.
Return Quarter and include the year:
= "Q" & ROUNDUP(MONTH(date)/3,0) & " - " & YEAR(date)Using the same concatenate process as above, we can further edit our formula to also display the date after a hyphen.
2. How to Pull Quarters Out From a Date with an Irregular or Fiscal Calendar Year
If you're working with an irregular or fiscal calendar, it can cause some extra headaches. The formula used above will not work in any non-standard case.
Instead, we can either use the CHOOSE function to pull the correct date, or create a lookup table (which will be slower across large data sets).
CHOOSE Function
For this method, we'll be using the CHOOSE function.
Here is the formula you would use assuming your fiscal year begins in July:
=CHOOSE(MONTH(date),3,3,3,4,4,4,1,1,1,2,2,2)The CHOOSE function works by taking a number input (the index_num) argument, and then returning the corresponding value argument.
Because there are only 12 months, the MONTH function will return a number 1-12 depending on the date being used.
This number (1-12) is then fed into the CHOOSE function, which returns the corresponding value. For example, January would return 1, which = value1 = 3. December would return a 12 = value12 = 2.
By manually writing out how our quarters are mapped to the months of the year, we can adjust the order of the value numbers to match up with whatever fiscal year we are using.
Lookup Table
In this example, we'll use the XLOOKUP to pull in the correct quarter numbers. This method may be a bit slower than the CHOOSE formula in large data sets.
Here is a sample Lookup table. This is what we will be checking each date across to see what quarter it should technically fall under.
What matters here is the "Month #" and "Quarter" columns. The month # is the lookup value, and Quarter is the return value.
By manually changing the Quarter values, we can change where the fiscal year begins.
In this example, the fiscal year and the first quarter begins on July 1st.
= XLOOKUP(MONTH(date), month_range, quarter_range)We can use this formula to lookup the month of each date across our "Month #" range, and return the correct quarter.
Ideally the lookup table would be held in a separate sheet, but it's placed side by side here for simplicity.
In this specific example this is the formula (with "$"s locking down our lookup table so we can drag the formula down).
= XLOOKUP(MONTH(B3),$F$3:$F$14,$G$3:$G$14)