| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Dates by Month
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping Dates by Month
Could you use a helper column and enter =MONTH(cellref)
Copy that down then subtotal by the numbers returned. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 15:36:02 -0700, Need Letters in the Columns wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping Dates by Month
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 :( "Gord Dibben" wrote: Could you use a helper column and enter =MONTH(cellref) Copy that down then subtotal by the numbers returned. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 15:36:02 -0700, Need Letters in the Columns wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping Dates by Month
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 Need Letters in the Columns wrote: 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 :( "Gord Dibben" wrote: Could you use a helper column and enter =MONTH(cellref) Copy that down then subtotal by the numbers returned. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 15:36:02 -0700, Need Letters in the Columns wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping Dates by Month
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" skrev i melding ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Grouping dates in PT; need last 6 month data | Excel Worksheet Functions | |||
| Grouping by month in a Pivot Table | Excel Discussion (Misc queries) | |||
| Grouping totals together by month per customer | Excel Discussion (Misc queries) | |||
| Grouping Date By Month | Excel Discussion (Misc queries) | |||
| grouping dates by week/month/etc. on cat. axis | Charts and Charting in Excel | |||
Linear Mode
