| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date in Pivot table
I have hundreds rows of data with different record dates and want to
summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date in Pivot table
You could put in a few hidden columns next to the dates,
using =month(date cell) =year(date cell) this will show for example =month(c1), with c1 being "01/05/04", will show a result of 5. You can then have these in your pivot table selection. You could even run a vlookup using the month result against it's actual month name 1 January 2 February =vlookup(month(date cell),(lookup table range),2,0) Hope this helps Nath. -----Original Message----- I have hundreds rows of data with different record dates and want to summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date in Pivot table
You can also right click on the date column (in the pivot Table) and choose
group, then group on year and month. -- Regards, Tom Ogilvy "booshi" wrote in message ... I have hundreds rows of data with different record dates and want to summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date in Pivot table
booshi wrote:
I have hundreds rows of data with different record dates and want to summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. booshi, The way I do it is to add another date column in the source data. This column has a date with the same month and year as the date in the date column but the day = 1 (i.e. the first day of the month). e.g. Date Month StdMonth 07/09/2004 01/09/2004 01/09/3000 18/09/2004 01/09/2004 01/09/3000 03/10/2004 01/10/2004 01/10/3000 16/10/2003 01/10/2003 01/10/3000 you can then group on the Month column (format it as mmm-yy). Because the Month column contains a date you'll find that it also sorts correctly. The Excel formula you would use to create the month date where the date is in cell A3 would be: =DATE(YEAR(A3),MONTH(A3),1) although I actually include the month in the source data I extract from my database so that I don't need to muck around with formulas in Excel. As you can see I also include a 'StdMonth' column where the year is always 3000 regardless of the actual year that I format as mmm. This allows me to create a cross-tab table that compares monthly data from different years. Eg. StdMonth Year Jan Feb Mar Apr Etc. 2001 1 5 2002 3 2 2003 2 3 Once again, because the StdMonth column is a date it sorts correctly. The stuff I do is actually a little more complex as I report data by financial year (which for Australian public hospitals is Jul - Jun) so, to ensure that the months sort in financial period order, my StdMonths a 01/07/3000 ... 01/12/3000 01/01/3001 ... 01/06/3001 It took me a lot banging my head against the wall to work this stuff out so I hope it helps. cheers peterDavey Austin Health Melbourne |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date in Pivot table
booshi wrote: I have hundreds rows of data with different record dates and want to summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. booshi, The way I do it is to add another date column in the source data. This column has a date with the same month and year as the date in the date column but the day = 1 (i.e. the first day of the month). e.g. Date Month StdMonth 07/09/2004 01/09/2004 01/09/3000 18/09/2004 01/09/2004 01/09/3000 03/10/2004 01/10/2004 01/10/3000 16/10/2003 01/10/2003 01/10/3000 you can then group on the Month column (format it as mmm-yy). Because the Month column contains a date you'll find that it also sorts correctly. The Excel formula you would use to create the month date where the date is in cell A3 would be: =DATE(YEAR(A3),MONTH(A3),1) although I actually include the month in the source data I extract from my database so that I don't need to muck around with formulas in Excel. As you can see I also include a 'StdMonth' column where the year is always 3000 regardless of the actual year that I format as mmm. This allows me to create a cross-tab table that compares monthly data from different years. Eg. StdMonth Year Jan Feb Mar Apr Etc. 2001 1 5 2002 3 2 2003 2 3 Once again, because the StdMonth column is a date it sorts correctly. The stuff I do is actually a little more complex as I report data by financial year (which for Australian public hospitals is Jul - Jun) so, to ensure that the months sort in financial period order, my StdMonths a 01/07/3000 .. 01/12/3000 01/01/3001 .. 01/06/3001 It took me a lot banging my head against the wall to work this stuff out so I hope it helps. cheers peterDavey Austin Health Melbourne ----------------------------------- Thanks Peter, I created another column =TEXT(C2, "yyyy-mm") but I was hoping that there should be something more precise in pivot table. boo |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date in Pivot table
There is, but apparently you want to ignore it.
-- Regards, Tom Ogilvy "booshi" wrote in message ... booshi wrote: I have hundreds rows of data with different record dates and want to summarise data by month/year. But the pivot table returns records by day/month/year. Pls help. booshi, The way I do it is to add another date column in the source data. This column has a date with the same month and year as the date in the date column but the day = 1 (i.e. the first day of the month). e.g. Date Month StdMonth 07/09/2004 01/09/2004 01/09/3000 18/09/2004 01/09/2004 01/09/3000 03/10/2004 01/10/2004 01/10/3000 16/10/2003 01/10/2003 01/10/3000 you can then group on the Month column (format it as mmm-yy). Because the Month column contains a date you'll find that it also sorts correctly. The Excel formula you would use to create the month date where the date is in cell A3 would be: =DATE(YEAR(A3),MONTH(A3),1) although I actually include the month in the source data I extract from my database so that I don't need to muck around with formulas in Excel. As you can see I also include a 'StdMonth' column where the year is always 3000 regardless of the actual year that I format as mmm. This allows me to create a cross-tab table that compares monthly data from different years. Eg. StdMonth Year Jan Feb Mar Apr Etc. 2001 1 5 2002 3 2 2003 2 3 Once again, because the StdMonth column is a date it sorts correctly. The stuff I do is actually a little more complex as I report data by financial year (which for Australian public hospitals is Jul - Jun) so, to ensure that the months sort in financial period order, my StdMonths a 01/07/3000 .. 01/12/3000 01/01/3001 .. 01/06/3001 It took me a lot banging my head against the wall to work this stuff out so I hope it helps. cheers peterDavey Austin Health Melbourne ----------------------------------- Thanks Peter, I created another column =TEXT(C2, "yyyy-mm") but I was hoping that there should be something more precise in pivot table. boo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table date format | Excel Discussion (Misc queries) | |||
| Pivot Table Groups by Date | Excel Discussion (Misc queries) | |||
| Pivot Table sorts date as Alpha, not as Date | Excel Worksheet Functions | |||
| Date in Pivot table question | Setting up and Configuration of Excel | |||
| PIVOT TABLE DATE | Excel Discussion (Misc queries) | |||
Linear Mode
