It's easy to calculate fiscal years and quarters using the INT or ROUND functions. You'll also learn about the CHOOSE and MONTH functions which allow for even better documentation.
- [Instructor] Sometimes when you're working with dates you need to calculate the quarter, possibly the quarter's based chronologically, that is the first three months of the year for the first quarter, second three months second quarter. Or possibly your fiscal year begins at a different time, so first quarter for you might begin in October or maybe in July. On this worksheet called fiscal year quarter, there's a formula in cell C2. And it works just fine. I haven't copied it down the column just yet, but it's probably an example of a formula that requires too much explanation.
On the one hand, you could say, well, it works. I don't necessarily understand it. But at least it works fine. In a nutshell here, all this formula's doing is taking the month, extracting the month from cell B2, so in the first case case there it's March or three, adding two to it, in this case it'd be five. We divide that by three. The answer, momentarily there, is one and two thirds. But the INT says let's just keep the integer portion of it. Now, although that works, I would not say that's a great format because it doesn't really help others understand it.
And furthermore, it will not have much flexibility if, for example, we need to change our quarterly system here so that our fiscal year begins in October, for example. What would we do then? We'd have to rewrite this in a totally different way. And it'd take some time to get it right, too. So let's take an alternate approach here using a function called choose. And it will have this flexibility here, to allow us to first of all, by recognizing what the month is, then calculate the quarter. Equal choose.
Think of this as a kind of V look up function that doesn't contain an external table, it's got a table built in to the actual function. The choose function says I'm evaluating some value somewhere. For example, let's pick out the month from cell B2. And here's the function called month. What's the month of B2? In the example here, it's equal to three. But what we're going to provide after the comma is twelve different answers. If the answer here is one, two, or three, meaning if the month is one January, two February, or three March, we're in the first quarter.
So when the answer to month of B2 is one, we want a one, if it's a two, meaning February, we want a one, and if it's March, which is three, we also want a one. And then if it's April, it's in the second quarter. So I think you probably get the idea here. You need three twos, three threes, and three fours. Each of those twelve answers occurs when that month, in this case B2, is equal to any of the numbers one through twelve. And that's all it can be if it's a month. So right parenthesis here. I'll press control enter, so the active cell doesn't move downward.
And we see that it's working there of course. Drag down just a few to check it out. June is in the second quarter, September's in the third quarter, December's in the fourth quarter. This isn't fool proof, but it looks like it's working pretty well. I'll double click to copy down the remainder of the column. Just a spot check here and there would recognize that all of these are working properly. So that's what it looks like. A bit lengthy perhaps, but easy to work with. Now, the fiscal year presumably would just be the year. In other words, pick up the year out of cell B2.
And nothing too unusual about that, or terribly sophisticated. It works just fine. We're simply extracting the year from those dates over in column B. Remember it simply looks like this. What if we're saying, from now on our fiscal year begins in October. Perhaps like the federal government. We need to change all these entries here, but it's relatively straight forward. Now we're saying that October, November, December represents the first quarter of our fiscal year. And that means that January, February, March, in other words when this answer here is one, two, or three, meaning January, February, or March, we're in the second quarter.
So I'm going to take off the three ones that we see here at the beginning. And let's begin with three twos, and after the four at the end, we'll end that with the three ones. So remember, here are the twelve different answers that we could get. If the answers are one, two, or three, that's January, February, March, and they're in the second quarter. April, May, June, third quarter and so on and so on. So we'll complete that entry, double click this to copy down the column. And this is how the quarters will appear if our fiscal year begins in October.
Now, we have to do something a little different here. In this example here, the fiscal year is going to be the same as the year, except when we're in the first quarter. In other words, that should be fiscal year 2017. So here we'll use the if function. Equal if. Left parenthesis. If this C2 entry right here equals one, in those cases, we want to fiscal year to be whatever the year is from that date over in B2, plus one.
So we'll pick out the year from cell B2, add one to it. Now, if the new quarter is not one, we'll simply use the year that's already there in cell B2. So in those cases, we simply use the year from B2. So two results here. Anytime we're in quarter one, the year that we need to get for our fiscal year indicator is the chronological year plus one. In all other three quarters, we'll simply use the year that we see there.
So I'll complete that with control enter, double click to copy this down the column. So every time we see a 2017, we're seeing first quarter and over here we're seeing either October, November, or December every time we see those entries there. Of course, it will vary with the year. Like in this case here, that would be the same of course. So you see what's happening in these examples. Now there are all kinds of variations on this. In some companies, the fiscal year begins in July. You can make the appropriate adjustments based on what we've seen here. So the choose function, using along with it, the month function, as well as the year function as we see being used here, are two other date functions that come into play from time to time when we're calculating fiscal years and fiscal quarters.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- How Excel records and stores dates
- Using standard date/time entries and acceptable alternatives
- Formatting dates and times
- Creating standard date/time formats
- Working with dates and times in functions
- Calculating with dates and times
- Using dates/times with Excel commands