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,
etc).

---

Clarification of Question by brunnock-ga on 30 Nov 2005 03:50 PST

So yes, it sounds like you're on the right track.

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:

=(YEAR(A10)*100)+MONTH(A10)

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.

--efn

---

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.

I don't know about OpenOffice, but in Excel you need to sort before
you can get meaningful subtotals.

Sorting the data is not a problem.