Microsoft Excel is a productivity spreadsheet application that supports data entry and formula calculations. It can be useful to perform calculations on dates and their respective quarters. Using quarters allows data to be condensed for trending or summary purposes. Some businesses may need to perform calculations on fiscal quarters rather than calendar quarters. For instance, if you wanted to trend profits in a year-over-year graph, it would be too cluttered to include all 12 months for each year, but it might be useful to see which quarters were more productive.
- Skill level:
Other People Are Reading
Open a new workbook in Excel and enter a list of random dates that can be used for testing.
You can copy these example dates to your sheet beginning in cell A1:
1/4/2010 2/11/2010 3/21/2010 4/28/2010 6/5/2010 7/13/2010 8/20/2010 9/27/2010 11/4/2010 12/12/2010
Determine the number value of the month in which your "year" starts (e.g. January is 1 and November is 11). For this example, the year will start in October (month 8).
Subtract one from your month value. In this example, the result would equal seven (7).
Substitute your new value into the following equation:
=MOD(CEILING(22+MONTH(<date cell>) - <month value minus one> - 1,3)/3,4)+1
For this example that would be: =MOD(CEILING(22+MONTH(A1)-7-1,3)/3,4)+1
Copy and paste the equation into cell B1 in your Excel worksheet.
Click and hold the mouse button down on cell B1 and drag your mouse cursor down to highlight all the cells from there to B10.
Hit the keys "Control" ("Ctrl") and D at the same time to copy the formula down to all the cells.
Your worksheet should now look like the following data:
01/04/2010 2 02/11/2010 3 03/21/2010 3 04/28/2010 3 06/05/2010 4 07/13/2010 4 08/20/2010 1 09/27/2010 1 11/04/2010 2 12/12/2010 2
Tips and warnings
- To calculate quarters based on the calendar year, you can use the same formula and substitute a zero for the year value.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for