Excel 2007

[HeatherJ]

I recently updated to Excel 2007 and can't figure out how to perform a
function I used to on my old version of excel.

I have a column of dates: 01/02/2009
01/13/2009
02/07/2009
02/19/2009
I want to sort by month, not by dates within a month. I used to format the
cell to show the date as "January" or "February."

Then I would use the Data-Text to Columns function and click column type
"Text." That would change the contents of the cell to January, February,
etc.

This allowed me to easily subtotal by month.

How can I do this in Excel 2007?

Thank you,
Heather

 

[Mike H]

Heather,

I don't understand. If you simply sort the dates then all January (etc) will
end up together. The only time this wouldn't be the case is for (say)
January's in different years but your sample data doesn't show that. Are you
saying you want all the same month together irrespective of the year?

If so use a helper column with the month in and sort on that

=MONTH(A1)

Mike

Mike

 

[Joe User]

I recently updated to Excel 2007 and can't figure out how to
perform a function I used to on my old version of excel.

What old version of Excel was that?

I have a column of dates: 01/02/2009 [....]
I used to format the cell to show the date as "January" or "February."
Then I would use the Data-Text to Columns function and click column type
"Text." That would change the contents of the cell to January, February,

It doesn't work that way in Excel 2003 SP3.

I'm not surprised. Formatting does not alter the value of the cell. No
matter how it appears, the cell value is still the date 1/2/2009 et al. And
Text-to-Columns uses the cell value, not its appearance.

Perhaps what you actually did, but forgot, is.... If the dates are in
A1:A20, put the following formula into C1: =TEXT(C1,"mmmm"). Copy C1 into
C2:C20, then copy C1:C20 and paste-special-value into A1:A20 or C1:C20,
depending on your intention.

Note: I am assuming that B1:B20 contains the corresponding data. See below.

I want to sort by month, not by dates within a month.

Do you truly want the months in the following order: April, August,
December, February, January, etc?(!) That would be unusual.

If all the dates are in the same year, sorting the dates will group the data
by month in calendar order.

If the dates are in different years, put the formula =MONTH(A1) into C1,
copy C1 into C2:C20, then sort A1:C20 by column C. Again, that will group
the data by month in calendar order, but not necessarily by year. If you
want the latter, too, we can help you. It is not much more effort.

This allowed me to easily subtotal by month.

So why sort the data at all? Does the following accomplish what you really
want?

Put the following dates into C1:C12 and format with Custom "mmmm" without
the quotes:

1/1/2009
2/1/2009
.....etc....
12/1/2009

So C1:C12 will display the month names in calendar order.

Now, put the following formula in D1, then copy D1 into D212:

=SUMPRODUCT(--(MONTH(A1:A20)=MONTH(C1)), B1:B20)


----- original message -----

 

[HeatherJ]

yes, all January would be together, but i want to subtotal by the month of
January. If I use the subtotal function as I used to it will subtotal for
each individual date in January.

jan 1
jan 1
jan 2
jan 2
jan 2
....
jan 31

would show as
jan 1 count 2
jan 2 count 3
jan 31 count 1

i want it to show the total number for jan

jan count 6

 

[HeatherJ]

It was OpenOffice 3.1

I recently updated to Excel 2007 and can't figure out how to
perform a function I used to on my old version of excel.

What old version of Excel was that?

I have a column of dates: 01/02/2009 [....]
I used to format the cell to show the date as "January" or "February."
Then I would use the Data-Text to Columns function and click column type
"Text." That would change the contents of the cell to January, February,

It doesn't work that way in Excel 2003 SP3.

I'm not surprised. Formatting does not alter the value of the cell. No
matter how it appears, the cell value is still the date 1/2/2009 et al. And
Text-to-Columns uses the cell value, not its appearance.

Perhaps what you actually did, but forgot, is.... If the dates are in
A1:A20, put the following formula into C1: =TEXT(C1,"mmmm"). Copy C1 into
C2:C20, then copy C1:C20 and paste-special-value into A1:A20 or C1:C20,
depending on your intention.

Note: I am assuming that B1:B20 contains the corresponding data. See below.

I want to sort by month, not by dates within a month.

Do you truly want the months in the following order: April, August,
December, February, January, etc?(!) That would be unusual.

If all the dates are in the same year, sorting the dates will group the data
by month in calendar order.

If the dates are in different years, put the formula =MONTH(A1) into C1,
copy C1 into C2:C20, then sort A1:C20 by column C. Again, that will group
the data by month in calendar order, but not necessarily by year. If you
want the latter, too, we can help you. It is not much more effort.

This allowed me to easily subtotal by month.

So why sort the data at all? Does the following accomplish what you really
want?

Put the following dates into C1:C12 and format with Custom "mmmm" without
the quotes:

1/1/2009
2/1/2009
....etc....
12/1/2009

So C1:C12 will display the month names in calendar order.

Now, put the following formula in D1, then copy D1 into D212:

=SUMPRODUCT(--(MONTH(A1:A20)=MONTH(C1)), B1:B20)


----- original message -----

I recently updated to Excel 2007 and can't figure out how to perform a
function I used to on my old version of excel.

I have a column of dates: 01/02/2009
01/13/2009
02/07/2009
02/19/2009
I want to sort by month, not by dates within a month. I used to format the
cell to show the date as "January" or "February."

Then I would use the Data-Text to Columns function and click column type
"Text." That would change the contents of the cell to January, February,
etc.

This allowed me to easily subtotal by month.

How can I do this in Excel 2007?

Thank you,
Heather

 

[David Biddulph]

OpenOffice isn't Excel.
--
David Biddulph

It was OpenOffice 3.1

I recently updated to Excel 2007 and can't figure out how to
perform a function I used to on my old version of excel.

What old version of Excel was that?

I have a column of dates: 01/02/2009 [....]
I used to format the cell to show the date as "January" or "February."
Then I would use the Data-Text to Columns function and click column
type
"Text." That would change the contents of the cell to January,
February,

It doesn't work that way in Excel 2003 SP3.

I'm not surprised. Formatting does not alter the value of the cell. No
matter how it appears, the cell value is still the date 1/2/2009 et al.
And
Text-to-Columns uses the cell value, not its appearance.

Perhaps what you actually did, but forgot, is.... If the dates are in
A1:A20, put the following formula into C1: =TEXT(C1,"mmmm"). Copy C1
into
C2:C20, then copy C1:C20 and paste-special-value into A1:A20 or C1:C20,
depending on your intention.

Note: I am assuming that B1:B20 contains the corresponding data. See
below.

I want to sort by month, not by dates within a month.

Do you truly want the months in the following order: April, August,
December, February, January, etc?(!) That would be unusual.

If all the dates are in the same year, sorting the dates will group the
data
by month in calendar order.

If the dates are in different years, put the formula =MONTH(A1) into C1,
copy C1 into C2:C20, then sort A1:C20 by column C. Again, that will
group
the data by month in calendar order, but not necessarily by year. If you
want the latter, too, we can help you. It is not much more effort.

This allowed me to easily subtotal by month.

So why sort the data at all? Does the following accomplish what you
really
want?

Put the following dates into C1:C12 and format with Custom "mmmm" without
the quotes:

1/1/2009
2/1/2009
....etc....
12/1/2009

So C1:C12 will display the month names in calendar order.

Now, put the following formula in D1, then copy D1 into D212:

=SUMPRODUCT(--(MONTH(A1:A20)=MONTH(C1)), B1:B20)


----- original message -----

I recently updated to Excel 2007 and can't figure out how to perform a
function I used to on my old version of excel.

I have a column of dates: 01/02/2009
01/13/2009
02/07/2009
02/19/2009
I want to sort by month, not by dates within a month. I used to format
the
cell to show the date as "January" or "February."

Then I would use the Data-Text to Columns function and click column
type
"Text." That would change the contents of the cell to January,
February,
etc.

This allowed me to easily subtotal by month.

How can I do this in Excel 2007?

Thank you,
Heather