Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
Am trying to group data that falls within the past 6 months.
Source data date ranges include dates from 1/1/1900 to 12/08/2008. The grouping dialogue box allows me to select a number of days, and I set to 190 days. The begin range, I set to start 6/01/2008, and ending today. The PT results still include breakouts such as: 2/28/2008 - 9/4/2008 9/5/2008 - 12/9/2008 What are the chances for a single breakout of the events that happen within the 6 month range? TIA for ideas. Pierre (For some strange reason, the start date, will usually migrate back to 01/01/1900, and I have to start all over again.) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
Add a helper column, and assuming your dates are in column A then populate
that helper column automatically, using a formula such as =IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"Hide","Show") copied down. Drag that field into your page fields and filter on "Show". Then choose whatever lower level of detail you want from the options available, eg months etc Regards Ken...................... "Pierre" wrote in message ... Am trying to group data that falls within the past 6 months. Source data date ranges include dates from 1/1/1900 to 12/08/2008. The grouping dialogue box allows me to select a number of days, and I set to 190 days. The begin range, I set to start 6/01/2008, and ending today. The PT results still include breakouts such as: 2/28/2008 - 9/4/2008 9/5/2008 - 12/9/2008 What are the chances for a single breakout of the events that happen within the 6 month range? TIA for ideas. Pierre (For some strange reason, the start date, will usually migrate back to 01/01/1900, and I have to start all over again.) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
On Dec 8, 4:32*pm, "Ken Wright" wrote:
Add a helper column, and assuming your dates are in column A then populate that helper column automatically, using a formula such as =IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"Hide","Show") copied down. Drag that field into your page fields and filter on "Show". Then choose whatever lower level of detail you want from the options available, eg months etc Regards * * * * * * * * * Ken...................... "Pierre" wrote in message ... Am trying to group data that falls within the past 6 months. Source data date ranges include dates from 1/1/1900 to 12/08/2008. The grouping dialogue box allows me to select a number of days, and I set to 190 days. The begin range, I set to start 6/01/2008, and ending today. The PT results still include breakouts such as: 2/28/2008 - 9/4/2008 9/5/2008 - 12/9/2008 What are the chances for a single breakout of the events that happen within the 6 month range? TIA for ideas. Pierre (For some strange reason, the start date, will usually migrate back to 01/01/1900, and I have to start all over again.)- Hide quoted text - - Show quoted text - Ken, I'll give it a whirl in the AM. Thanks for the help. Pierre |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
Hi,
It groups just fine for me. I set the Start date at 6/1/08 and left the End Date alone. I entered 182 or 183 in the number of days box, and took off all other options Months, Years,.... Finally, I opened the Date filter and turner off all of the dates except 6/1/08 - 11/30/08. What version of Excel are you using? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Pierre" wrote: Am trying to group data that falls within the past 6 months. Source data date ranges include dates from 1/1/1900 to 12/08/2008. The grouping dialogue box allows me to select a number of days, and I set to 190 days. The begin range, I set to start 6/01/2008, and ending today. The PT results still include breakouts such as: 2/28/2008 - 9/4/2008 9/5/2008 - 12/9/2008 What are the chances for a single breakout of the events that happen within the 6 month range? TIA for ideas. Pierre (For some strange reason, the start date, will usually migrate back to 01/01/1900, and I have to start all over again.) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
On Dec 8, 4:32*pm, "Ken Wright" wrote:
Add a helper column, and assuming your dates are in column A then populate that helper column automatically, using a formula such as =IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"Hide","Show") copied down. Drag that field into your page fields and filter on "Show". Then choose whatever lower level of detail you want from the options available, eg months etc Regards * * * * * * * * * Ken...................... "Pierre" wrote in message ... Am trying to group data that falls within the past 6 months. Source data date ranges include dates from 1/1/1900 to 12/08/2008. The grouping dialogue box allows me to select a number of days, and I set to 190 days. The begin range, I set to start 6/01/2008, and ending today. The PT results still include breakouts such as: 2/28/2008 - 9/4/2008 9/5/2008 - 12/9/2008 What are the chances for a single breakout of the events that happen within the 6 month range? TIA for ideas. Pierre (For some strange reason, the start date, will usually migrate back to 01/01/1900, and I have to start all over again.)- Hide quoted text - - Show quoted text - Ken, it worked beautifully. Great idea. Thank you! (Thank you Shane, also for your input.) Pierre |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grouping dates in PT; need last 6 month data
You're very welcome. There are all sorts of useful helper columns you can
create along the same sorts of lines. Regards Ken.......................... Ken, it worked beautifully. Great idea. Thank you! (Thank you Shane, also for your input.) Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping by month in a Pivot Table | Excel Discussion (Misc queries) | |||
Grouping Dates | 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 |