What quarter is it?

When you’re working with financial data (forecasts, revenue or sales targets, financials), you are certainly used to working at the annual and monthly level.  And doing so, whether with a spreadsheet or accounting software, is straightforward.  Grouping by quarter, though, is not as simple, at least when working in Excel.  But, as we accounting and finance people learn, it can be the most useful.  Monthly information often represents too small a slice of data to offer any useful insight- at least in its own context.  On the other hand, while annual information gives you a much better feel for what’s going on with the business, it happens too infrequently to allow management or ownership to do anyting about it.

It makes sense, then, to organize and analyze important information by quarter.  After all, there’s a reason big companies report these results to all who are listening. And there are even bigger reasons- millions of them (dollars), in fact- why Wall Street, and investors everywhere, respond accordingly-and often instantly and irrationally I might add.

To do this, you obviously need your information to include dates (of transactions, of sales, of expenses, of orders, whatever).  You can then add a column to calculate the quarter.  If you are working with data across multiple years, you also might want to make it year specific.  Instead of “Qtr-4”, for example, “Q4-2005” would be more clear if your data covers 2005-2007.  You can actually determine the quarter and sum (or average) the data in one step (without adding a helper column) using SUMPRODUCT, but I’ll leave that for another discussion.  For now, we’ll stick to the simple way(s).

As is almost always the case, Excel provides several ways to accomplish what we are looking for.  I think that’s why I like it so much.  Working with it is as much art as it is science.  And while I’m a self-confessed nerd, I consider myself a creative nerd.  Anyway, back to the task at hand.  Following are the three ways I can think of (there are probably more) to determine in which quarter any date falls.  They are also listed in the order that I learned them, representative I suppose of my progression in skill level.

1. Using nested if statements– Checking for and acting upon conditions is one of the fundamental skills any Excel user worth his weight in gold should know.  The basic structure of the If Statement is: IF (test if some condition is true, if it is true do something, if it’s not true then do something else).  The trick when you have several conditions to check for (in our case, if the month is less than or equal to 3, if the month is less than or equal to 6, and so on) is to replace the else (if not true) part of the function with yet another IF function.  This nesting can get pretty long and confusing, however, and if you have more than three or four, will drive you crazy. (See Column E on attached worksheet)
2. Creating a UDF (User Defined Function)– If you looked at a spreadsheet I built even as recently as a year or two ago, you’d need an aspirin when you were done.  Although I’d heard about User Defined Functions and VBA (Visual Basic for Applications) and even created a macro or two, I was reluctant to go into the deep end, preferring instead to kick my feet around in the kiddie pool.  As a result, I spent hours trying to fit any task I was working on into the built-in functions that Excel provides.  And while there are many, many, many of these functions– I still haven’t used the majority of them– sometimes you just can’t accomplish what you are trying to with them.  And if you do happen to figure out a way to use one of the ready-made functions, the formula that results is quite often so messy  that it creates more trouble and confusion than it’s worth.  By understanding even just a few basics about VBA and UDF’s– in our case the Select Case Statement, specifically– you can save yourself A LOT of time, energy, and frustration.  Plus, you’ll impress your colleagues and boss when they see that there is actually a formula called BobsCoolDateFunction (you can call UDFs whatever you want and use them as you would any other function) that you use to get the quarter for any given date. (See Column F on attached worksheet; to view UDF in VBA, click ALt-F11)
3. ROUNDUP function and a little middle school math– One method that is not as cumbersome or messy as using nested IF statements, but doesn’t require the usage of sometimes intimidating VBA, is to use some creative thinking and the ROUNDUP function, which rounds any number you feed it up to the nearest decimal place that you designate.  First, because there are three months in every quarter, we divide the month number by three.  For all months whose number is a factor of three (3,6,9,12), the result is precise and accurate.  For example, December (month 12) is in quarter number four (12/3).  But so is November (11/3) and October (10/3).  For months whose number is not a factor of 3, the result is a mixed number (whole number plus a fraction).  But because each fraction of the next quarter is considered the next quarter nonetheless, we simply round up to the next whole number.  So where 1/3 (January) is 0.33, rounding up to zero decimal places results in (quarter number) 1.  The same is true for February (2/3).   And while June (month 6), is clearly in the second quarter (6/3=2), rounding up shows us that April (4/3=1.33) and May (5/3=1.67) are as well.  To get our answer in this method, then, we simply use: ROUNDUP(MONTH(date we are checking)/3,0) (See Column G on attached worksheet)

Again, using these three methods all produce the same result, a helper column that tells you what quarter in which a month falls.  It’s just a matter of preference and skill level.  I hope you’ll at least try them all, especially the VBA version, to gain a better understanding of the possibilities within Excel.

Note: I produced the data randomly using the RANDBETWEEN function, which  I covered in a previous post.  I then copied and pasted the values so the sheet wouldn’t recalculate so much data every time it was opened or updated.  As a reference and learning tool, however, I kept the formula in row 1.  I’ve also included the CONCATENATE function (also previously covered .. twice), which makes the quarter year-specific.

File Attachment: GettingTheQtr