PivotTable grouping by Time Elapsed problem

[Guest]

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.

 

[Roger Govier]

Hi

Try making your elapsed time
=MOD(D1-B1,1)
Format>Cells>Number>Custom>[hh:]:mm:ss

 

[Guest]

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.

Hi

Try making your elapsed time
=MOD(D1-B1,1)
Format>Cells>Number>Custom>[hh:]:mm:ss

--
Regards
Roger Govier

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.