Grouping Dates by Month

  • Thread starter Need Letters in the Columns
  • Start date
N

Need Letters in the Columns

I have several spreadsheet with tons of date listed. I need to group the
dates by month so i can subtotal each month. I got the subtotalling part
its the group by month that i need to do without going in and entering a
break between each month.
01/01/2008 Group as Jan
01/31/2008 Group as Jan
02/01/2008 Group as Feb
04/01/2008 Group as Apr
05/02/2008 Group as May
09/01/2008 Group as Sep
 
G

Gord Dibben

Could you use a helper column and enter =MONTH(cellref)

Copy that down then subtotal by the numbers returned.


Gord Dibben MS Excel MVP
 
N

Need Letters in the Columns

I tried that but i think it not working because there is a time stamp in the
cell that is not visable unless you are clicked on the cell

A1= 9/01/08 1:30PM
When I tried to copy the cell and paste value it but it converts to whole
numbers 3975.444. If i copy and paste regular it will copy the time stamp. I
also tried to manually retype the dates (which will take forever :( and use
the helper but I get the number returns 9

09/01/08 (A1) in B1 it put the formula =month(A1) returns 9

Please help :(
 
D

Dave Peterson

After you do that copy|paste, try formatting the range of cells the way you like
it:

mm/dd/yyyy
or
mm/dd/yyyy hh:mm:ss

And then try Gord's suggestion once more.

But if years are important, you may want to use a helper column like:

=text(a2,"yyyy-mm")
to get the year and month
 
H

Harald Staff

If your dates are recognized as dates by Excel, a pivot table will do this
and lots more in no time.

HTH. Best wishes Harald

"Need Letters in the Columns"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top