Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
I have a date field in my pivot table, for which source data not only has the
year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
If your dates are real dates, you could rightclick on the Date field (In the
pivottable) and choose "Group and show detail". Then you could group my month (or month and year). Another option might be to use another column of formulas: =text(a2,"yyyy-mm") And use that in the pivottable. BorisS wrote: I have a date field in my pivot table, for which source data not only has the year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
Dave, the second idea is certainly something that would work (is my guess),
but I like to avoid adding columns, especially given I'm already pushing 8k records with lots of data (the file is getting monstrous on its own without additional columns, and I'd have to do this several times to fix other dates if I were to do it that way). On your first point, I am not quite clear...I am assuming the litmus test for "real dates" is whether the grouping dialogue comes up (because it doesn't even do that if you have a blank, much less something that is not a date). I do get that dialogue. As for "group and show detail" I am not sure where that is. I have group and I have show detail, but given that the date is the only field I have in the row area, "show detail" would require me to pick a "detail" field to show, which I don't really need to do. I guess I am miffed by why Excel chooses - with all the date data points I have in the source data and formatting (day, month, year, time) - to only show me the options of day and month on the pivot (without my having chosen for it to do that in the first place). Am I making sense as to why I am not understanding your first idea? -- Boris "Dave Peterson" wrote: If your dates are real dates, you could rightclick on the Date field (In the pivottable) and choose "Group and show detail". Then you could group my month (or month and year). Another option might be to use another column of formulas: =text(a2,"yyyy-mm") And use that in the pivottable. BorisS wrote: I have a date field in my pivot table, for which source data not only has the year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
"Group and show detail" is an option when I click on that field in xl2003.
I'm not sure if they changed this caption from earlier versions (but they have changed a bunch of captions through various versions). Try Group and see where that leads. ps. Post the version of excel, then someone with that same version can jump in if you have trouble. BorisS wrote: Dave, the second idea is certainly something that would work (is my guess), but I like to avoid adding columns, especially given I'm already pushing 8k records with lots of data (the file is getting monstrous on its own without additional columns, and I'd have to do this several times to fix other dates if I were to do it that way). On your first point, I am not quite clear...I am assuming the litmus test for "real dates" is whether the grouping dialogue comes up (because it doesn't even do that if you have a blank, much less something that is not a date). I do get that dialogue. As for "group and show detail" I am not sure where that is. I have group and I have show detail, but given that the date is the only field I have in the row area, "show detail" would require me to pick a "detail" field to show, which I don't really need to do. I guess I am miffed by why Excel chooses - with all the date data points I have in the source data and formatting (day, month, year, time) - to only show me the options of day and month on the pivot (without my having chosen for it to do that in the first place). Am I making sense as to why I am not understanding your first idea? -- Boris "Dave Peterson" wrote: If your dates are real dates, you could rightclick on the Date field (In the pivottable) and choose "Group and show detail". Then you could group my month (or month and year). Another option might be to use another column of formulas: =text(a2,"yyyy-mm") And use that in the pivottable. BorisS wrote: I have a date field in my pivot table, for which source data not only has the year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
well I am also on 2003, SP2.
I see what you're saying now. The level before actual useful dialogues is called "group and show details". I know the dialogue you're referring to where you can group by the details of the dates, and I know it can only do that because what I said originally is true (in that they are all dates). My problem is that I don't want to have to have three fields (year, month, day) or even two (mmm-dd and year) to show what's combined within one cell. I guess what I'm asking is whether this is one of those quirks of pvts, in that despite the data being dates, and the grouping being able to group the dates into labels, there is still some inability to treat the labels as date formats as well. -- Boris "Dave Peterson" wrote: "Group and show detail" is an option when I click on that field in xl2003. I'm not sure if they changed this caption from earlier versions (but they have changed a bunch of captions through various versions). Try Group and see where that leads. ps. Post the version of excel, then someone with that same version can jump in if you have trouble. BorisS wrote: Dave, the second idea is certainly something that would work (is my guess), but I like to avoid adding columns, especially given I'm already pushing 8k records with lots of data (the file is getting monstrous on its own without additional columns, and I'd have to do this several times to fix other dates if I were to do it that way). On your first point, I am not quite clear...I am assuming the litmus test for "real dates" is whether the grouping dialogue comes up (because it doesn't even do that if you have a blank, much less something that is not a date). I do get that dialogue. As for "group and show detail" I am not sure where that is. I have group and I have show detail, but given that the date is the only field I have in the row area, "show detail" would require me to pick a "detail" field to show, which I don't really need to do. I guess I am miffed by why Excel chooses - with all the date data points I have in the source data and formatting (day, month, year, time) - to only show me the options of day and month on the pivot (without my having chosen for it to do that in the first place). Am I making sense as to why I am not understanding your first idea? -- Boris "Dave Peterson" wrote: If your dates are real dates, you could rightclick on the Date field (In the pivottable) and choose "Group and show detail". Then you could group my month (or month and year). Another option might be to use another column of formulas: =text(a2,"yyyy-mm") And use that in the pivottable. BorisS wrote: I have a date field in my pivot table, for which source data not only has the year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
I thought that if you kept the date/times in your pivot table, then you could
group that by year and month. Aren't the dates/times just in one column in the raw data? But it sounds like I don't understand the problem. BorisS wrote: well I am also on 2003, SP2. I see what you're saying now. The level before actual useful dialogues is called "group and show details". I know the dialogue you're referring to where you can group by the details of the dates, and I know it can only do that because what I said originally is true (in that they are all dates). My problem is that I don't want to have to have three fields (year, month, day) or even two (mmm-dd and year) to show what's combined within one cell. I guess what I'm asking is whether this is one of those quirks of pvts, in that despite the data being dates, and the grouping being able to group the dates into labels, there is still some inability to treat the labels as date formats as well. -- Boris "Dave Peterson" wrote: "Group and show detail" is an option when I click on that field in xl2003. I'm not sure if they changed this caption from earlier versions (but they have changed a bunch of captions through various versions). Try Group and see where that leads. ps. Post the version of excel, then someone with that same version can jump in if you have trouble. BorisS wrote: Dave, the second idea is certainly something that would work (is my guess), but I like to avoid adding columns, especially given I'm already pushing 8k records with lots of data (the file is getting monstrous on its own without additional columns, and I'd have to do this several times to fix other dates if I were to do it that way). On your first point, I am not quite clear...I am assuming the litmus test for "real dates" is whether the grouping dialogue comes up (because it doesn't even do that if you have a blank, much less something that is not a date). I do get that dialogue. As for "group and show detail" I am not sure where that is. I have group and I have show detail, but given that the date is the only field I have in the row area, "show detail" would require me to pick a "detail" field to show, which I don't really need to do. I guess I am miffed by why Excel chooses - with all the date data points I have in the source data and formatting (day, month, year, time) - to only show me the options of day and month on the pivot (without my having chosen for it to do that in the first place). Am I making sense as to why I am not understanding your first idea? -- Boris "Dave Peterson" wrote: If your dates are real dates, you could rightclick on the Date field (In the pivottable) and choose "Group and show detail". Then you could group my month (or month and year). Another option might be to use another column of formulas: =text(a2,"yyyy-mm") And use that in the pivottable. BorisS wrote: I have a date field in my pivot table, for which source data not only has the year, but also the time. When I get it into the pivot table, however, there is no way I can figure out to get it to show me (both in the dropdown for selection of range or in display of the labels) the date with the year. As such, because the data spans several years, it's hard to guess which dates to include and eliminate in order to capture any particular month of a particular year. Any clues? I know I have no blanks in there, which sometimes throws date fields off. I know my source is formatted to date format. I don't have any other ideas of what to try. Thanks for any help. -- Boris -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
Hi Boris
If you group by Year and Month as Dave suggests, you will get a column for Year (which you can narrow down to 4 chars wide), then Month then your other data. The dropdown on Year would allow you to select only a given year, then select Month within that. Regards Roger Govier BorisS wrote: well I am also on 2003, SP2. I see what you're saying now. The level before actual useful dialogues is called "group and show details". I know the dialogue you're referring to where you can group by the details of the dates, and I know it can only do that because what I said originally is true (in that they are all dates). My problem is that I don't want to have to have three fields (year, month, day) or even two (mmm-dd and year) to show what's combined within one cell. I guess what I'm asking is whether this is one of those quirks of pvts, in that despite the data being dates, and the grouping being able to group the dates into labels, there is still some inability to treat the labels as date formats as well. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates in pivot not coming up with years
The part of splitting the date out into years and months and all the grouping
I get. I was trying to avoid having to have two columns. I guess the answer I'm hearing is that MS was not smart enough to think that if they are going to give a dropdown of data that in its raw form has MORE pieces to the data (the year in this case), they should have given the option to see that extra data. Just weird, given any other field, including numbers with decimals and text will show you the entirety of what's in the raw data, whereas it sounds like dates only show up as mmm-dd. -- Boris "Roger Govier" wrote: Hi Boris If you group by Year and Month as Dave suggests, you will get a column for Year (which you can narrow down to 4 chars wide), then Month then your other data. The dropdown on Year would allow you to select only a given year, then select Month within that. Regards Roger Govier BorisS wrote: well I am also on 2003, SP2. I see what you're saying now. The level before actual useful dialogues is called "group and show details". I know the dialogue you're referring to where you can group by the details of the dates, and I know it can only do that because what I said originally is true (in that they are all dates). My problem is that I don't want to have to have three fields (year, month, day) or even two (mmm-dd and year) to show what's combined within one cell. I guess what I'm asking is whether this is one of those quirks of pvts, in that despite the data being dates, and the grouping being able to group the dates into labels, there is still some inability to treat the labels as date formats as well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping Dates in a Pivot table for years that are not calendar ye | Excel Worksheet Functions | |||
Graphics coming from a Pivot Table. PLEASE HELP!! | Excel Discussion (Misc queries) | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
can anyone help with times not coming across correctly in pivot t. | Excel Worksheet Functions | |||
can anyone help with times not coming across correctly in pivot t. | Excel Worksheet Functions |