Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable grouping by Time Elapsed problem
Hi All,
Ok, ok, I submit. I spent an hour on this and could NOT get it to resolve. I'm working with about 6800+ rows which have oodles of columns; however, the only two (ok, three) that are relevant to the problem are the Time Created (column B), Time Edited (column D) and resultant Time Elapsed (column E). The first two columns are provided by the system in mm/dd/yyyy hh:mm:ss format, and the third is an auxiliary column I created to show the difference, using n(D2)-n(B2), with a cell format of dd hh:mm:ss. The PivotTable I create uses the count of records as the data, with Time Elapsed as the rows. When I try to slim down the rows by grouping, it perceives the values as times in a day instead of elapsed time periods. So when I group, instead of doing "1:00:00", it does "1am". I've tried modifying the format of both the fields in the PivotTable and just the columns for the sheet the PivotTable is in, and nothing seems to convert it properly. To further complicate the issue, it doesn't recognize that there's also days into this--so when it rolls past 24, it just starts over. A real, royal pain. Any ideas? I *could* just break the time elapsed out to number format, but it's sort of frowned upon to have to explain to a VP why the frequencies are measured in % of a day. Thanks in advance for any help on this pain, Jamie W. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable grouping by Time Elapsed problem
Hi
Try making your elapsed time =MOD(D1-B1,1) FormatCellsNumberCustom[hh:]:mm:ss -- Regards Roger Govier "MJW" wrote in message ... Hi All, Ok, ok, I submit. I spent an hour on this and could NOT get it to resolve. I'm working with about 6800+ rows which have oodles of columns; however, the only two (ok, three) that are relevant to the problem are the Time Created (column B), Time Edited (column D) and resultant Time Elapsed (column E). The first two columns are provided by the system in mm/dd/yyyy hh:mm:ss format, and the third is an auxiliary column I created to show the difference, using n(D2)-n(B2), with a cell format of dd hh:mm:ss. The PivotTable I create uses the count of records as the data, with Time Elapsed as the rows. When I try to slim down the rows by grouping, it perceives the values as times in a day instead of elapsed time periods. So when I group, instead of doing "1:00:00", it does "1am". I've tried modifying the format of both the fields in the PivotTable and just the columns for the sheet the PivotTable is in, and nothing seems to convert it properly. To further complicate the issue, it doesn't recognize that there's also days into this--so when it rolls past 24, it just starts over. A real, royal pain. Any ideas? I *could* just break the time elapsed out to number format, but it's sort of frowned upon to have to explain to a VP why the frequencies are measured in % of a day. Thanks in advance for any help on this pain, Jamie W. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable grouping by Time Elapsed problem
Thanks Roger--only problem is it doesn't seem to capture the days elapsed.
Just as in other attempts, it rolls over any time elapsed past 23:59:59 to 00:00:00. I ended up just breaking it out as a percentage of a day and manually calcuating/converting each row in the PivotTable to hours. (IE: Since the highest value was 13.xx, I set the increments to .04167 [1 hr as % of a day], then manually edited the rows from the multiples of .04167 to the equivalent hours.) It'd be nice to see Excel add an option to change it from automatically determining a time field to be in a range of 12-hr Latin Time to giving the user an option to simply determine a range based on the actual value range returned... but I'm guessing that since their QA team is now missing some fairly obvious bugs in actual releases (such as '07's bug of rounding any cell values of 65,535-65536 to 100,000), there's little hope for an inconsequential mod such as this. Thanks Jamie W. "Roger Govier" wrote: Hi Try making your elapsed time =MOD(D1-B1,1) FormatCellsNumberCustom[hh:]:mm:ss -- Regards Roger Govier "MJW" wrote in message ... Hi All, Ok, ok, I submit. I spent an hour on this and could NOT get it to resolve. I'm working with about 6800+ rows which have oodles of columns; however, the only two (ok, three) that are relevant to the problem are the Time Created (column B), Time Edited (column D) and resultant Time Elapsed (column E). The first two columns are provided by the system in mm/dd/yyyy hh:mm:ss format, and the third is an auxiliary column I created to show the difference, using n(D2)-n(B2), with a cell format of dd hh:mm:ss. The PivotTable I create uses the count of records as the data, with Time Elapsed as the rows. When I try to slim down the rows by grouping, it perceives the values as times in a day instead of elapsed time periods. So when I group, instead of doing "1:00:00", it does "1am". I've tried modifying the format of both the fields in the PivotTable and just the columns for the sheet the PivotTable is in, and nothing seems to convert it properly. To further complicate the issue, it doesn't recognize that there's also days into this--so when it rolls past 24, it just starts over. A real, royal pain. Any ideas? I *could* just break the time elapsed out to number format, but it's sort of frowned upon to have to explain to a VP why the frequencies are measured in % of a day. Thanks in advance for any help on this pain, Jamie W. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable question about grouping numbers in it | Excel Worksheet Functions | |||
Elapsed time problem....again! | Excel Worksheet Functions | |||
PivotTable grouping problem | Excel Discussion (Misc queries) | |||
Grouping dates in PivotTable/Chart | Charts and Charting in Excel | |||
elapsed time | Excel Worksheet Functions |