Google Answers Logo
View Question
Q: How do I group by month in OpenOffice Calc? ( Answered,   2 Comments )
Subject: How do I group by month in OpenOffice Calc?
Category: Computers > Software
Asked by: brunnock-ga
List Price: $2.50
Posted: 29 Nov 2005 08:59 PST
Expires: 29 Dec 2005 08:59 PST
Question ID: 599014
In my spreadhseet, each row contains a date value. I would like to
group the rows by month. I tried selecting all of the cells and then
selecting the "Data->Subtotals" menu item and selecting "Group by"
date on the following dialog box, but that doesn't work.

Request for Question Clarification by efn-ga on 29 Nov 2005 22:15 PST
Do you want to group the rows so you can get subtotals of some column
or columns other than the date column?  (It's not clear if you tried
subtotals because you wanted subtotals or as a means of grouping for
some other purpose.)  If not, what do you want the grouping to do? 
Just sort rows with the same month together?

You could add another column containing the months extracted from the
dates.  Then you could get subtotals by month using this column with
the subtotal feature.  This would ignore years, though--May 2004 and
May 2005 would be in the same group.  But years could be included with
a bit more work.  Does this sound like it's on the right track?

Clarification of Question by brunnock-ga on 30 Nov 2005 03:48 PST
I would like to group the rows by month and year and then I would like
to do subtotals on various columns in each group (sums, averages,

Clarification of Question by brunnock-ga on 30 Nov 2005 03:50 PST
So yes, it sounds like you're on the right track.
Subject: Re: How do I group by month in OpenOffice Calc?
Answered By: efn-ga on 30 Nov 2005 20:35 PST
Hi brunnock,

You are on the right track with the Subtotals function.  You just need
to create a column with a representation of the month and year from
the date.

I'd suggest filling a column with formulas like this:


In this example, A is the column with the date and 10 is the row where
the formula is.  As you may know, if you copy the formula to multiple
rows, Calc will automatically adjust the row numbers in the formula to
formula's row.

This formula will generate numbers like 200511 for November 2005.  You
can then subtotal by these numbers.  It will be easiest if you have a
heading row above your data.  Otherwise, Calc will use the top value
in the column to refer to the column.  When you run the Subtotals
function, select the column with the year-month formula values in the
"Group by" list and check the headings for which you want subtotals in
the "Calculate subtotals for" box.  This will give you subtotals and a
grand total.

I hope this information is helpful.  If you need more help, please ask
for a clarification.


Request for Answer Clarification by brunnock-ga on 01 Dec 2005 07:56 PST
I did some investigating on my own. I found that I could create
another column whose cells are equal to the date cells. I could then
format the new column to display just the month and year and then I
could do subtotal while grouping by the new column.

Is there an advantage to your approach?

Clarification of Answer by efn-ga on 01 Dec 2005 20:39 PST
I don't think so.
Subject: Re: How do I group by month in OpenOffice Calc?
From: nelson-ga on 29 Nov 2005 21:25 PST
I don't know about OpenOffice, but in Excel you need to sort before
you can get meaningful subtotals.
Subject: Re: How do I group by month in OpenOffice Calc?
From: brunnock-ga on 30 Nov 2005 03:48 PST
Sorting the data is not a problem.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy