I am looking at timestamps on a server log and trying to average the
difference between the times in a pivot chart. The time is milliseconds and I
just want the flat number. Since the timestamp is based on the calculation
from a certain point in time, it wants to convert the date to a very small
fraction.

Can someone help me understand how to take the time and convert it to just a
flat number? I have seen a lot of posts about mulitplying by 24, etc, but
this doesn't get it to a flat number.

Thanks!

<about mulitplying by 24, etc, but this doesn't get it to a flat number.>

It does, but you'll have to format afterwards as General or Number to be able to see that. Excel keeps changing it to Time each
time you enter or edit the formula.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kaspr" <(E-Mail Removed)> wrote in message news:8F1CAA46-8012-439E-A7F3-(E-Mail Removed)...
|I am looking at timestamps on a server log and trying to average the
| difference between the times in a pivot chart. The time is milliseconds and I
| just want the flat number. Since the timestamp is based on the calculation
| from a certain point in time, it wants to convert the date to a very small
| fraction.
|
| Can someone help me understand how to take the time and convert it to just a
| flat number? I have seen a lot of posts about mulitplying by 24, etc, but
| this doesn't get it to a flat number.
|
| Thanks!

It is not doing what I need it to do. Ok, I have 200 entries that have a
start time and an end time. I subtract them into a new field. This leaves me
with a row of times looking like 00:00:05. Without doing any formatting of
the cells, I copy all the times into notepad, do a find replace on the 00:00:
with nothing, now, I have a list of seconds. I cut/paste these back into
Excel and do an average on them and get 8.13. So, I have an average response
time of 8.13 seconds per request. If I just average this row in Excel without
moving it to notepad, it comes back with 11 seconds. But, when I create a
pivot table on this same data, it gives me an average of 00:00 (looking at
the top it lists it as 0.00013144425832868. So why is the pivot table not
averaging it correctly? I must be doing something wrong!

Thanks again!

Just using Excel to do this so I don't have to do all the manual stuff, is
not working. The average on the bottom ends up being


"Niek Otten" wrote:

> <about mulitplying by 24, etc, but this doesn't get it to a flat number.>
>
> It does, but you'll have to format afterwards as General or Number to be able to see that. Excel keeps changing it to Time each
> time you enter or edit the formula.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Kaspr" <(E-Mail Removed)> wrote in message news:8F1CAA46-8012-439E-A7F3-(E-Mail Removed)...
> |I am looking at timestamps on a server log and trying to average the
> | difference between the times in a pivot chart. The time is milliseconds and I
> | just want the flat number. Since the timestamp is based on the calculation
> | from a certain point in time, it wants to convert the date to a very small
> | fraction.
> |
> | Can someone help me understand how to take the time and convert it to just a
> | flat number? I have seen a lot of posts about mulitplying by 24, etc, but
> | this doesn't get it to a flat number.
> |
> | Thanks!
>
>
>

0.00013144425832868 is 11 seconds. Just format it as h:mm:ss and you'll see.
Why use notepad or even a pivot table? Just average the times and format the result correctly and you're done.
Try it with just a few cells to get confidence.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Kaspr" <(E-Mail Removed)> wrote in message news:BB29073E-5F4A-40B4-98E9-(E-Mail Removed)...
| It is not doing what I need it to do. Ok, I have 200 entries that have a
| start time and an end time. I subtract them into a new field. This leaves me
| with a row of times looking like 00:00:05. Without doing any formatting of
| the cells, I copy all the times into notepad, do a find replace on the 00:00:
| with nothing, now, I have a list of seconds. I cut/paste these back into
| Excel and do an average on them and get 8.13. So, I have an average response
| time of 8.13 seconds per request. If I just average this row in Excel without
| moving it to notepad, it comes back with 11 seconds. But, when I create a
| pivot table on this same data, it gives me an average of 00:00 (looking at
| the top it lists it as 0.00013144425832868. So why is the pivot table not
| averaging it correctly? I must be doing something wrong!
|
| Thanks again!
|
| Just using Excel to do this so I don't have to do all the manual stuff, is
| not working. The average on the bottom ends up being
|
|
| "Niek Otten" wrote:
|
| > <about mulitplying by 24, etc, but this doesn't get it to a flat number.>
| >
| > It does, but you'll have to format afterwards as General or Number to be able to see that. Excel keeps changing it to Time
each
| > time you enter or edit the formula.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "Kaspr" <(E-Mail Removed)> wrote in message news:8F1CAA46-8012-439E-A7F3-(E-Mail Removed)...
| > |I am looking at timestamps on a server log and trying to average the
| > | difference between the times in a pivot chart. The time is milliseconds and I
| > | just want the flat number. Since the timestamp is based on the calculation
| > | from a certain point in time, it wants to convert the date to a very small
| > | fraction.
| > |
| > | Can someone help me understand how to take the time and convert it to just a
| > | flat number? I have seen a lot of posts about mulitplying by 24, etc, but
| > | this doesn't get it to a flat number.
| > |
| > | Thanks!
| >
| >
| >

Ok, I was making it more complicated than it needed to be. formating it
correctly made all the difference. I need the pivot table because there are
actually 1,000's of rows with different request types and I needed to
seperate them out and average them instead of manually trying to group them
all and average them individually.

Got the pivot table working and the results look correct now.

Thanks for the help!

"Niek Otten" wrote:

> 0.00013144425832868 is 11 seconds. Just format it as h:mm:ss and you'll see.
> Why use notepad or even a pivot table? Just average the times and format the result correctly and you're done.
> Try it with just a few cells to get confidence.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
>
> "Kaspr" <(E-Mail Removed)> wrote in message news:BB29073E-5F4A-40B4-98E9-(E-Mail Removed)...
> | It is not doing what I need it to do. Ok, I have 200 entries that have a
> | start time and an end time. I subtract them into a new field. This leaves me
> | with a row of times looking like 00:00:05. Without doing any formatting of
> | the cells, I copy all the times into notepad, do a find replace on the 00:00:
> | with nothing, now, I have a list of seconds. I cut/paste these back into
> | Excel and do an average on them and get 8.13. So, I have an average response
> | time of 8.13 seconds per request. If I just average this row in Excel without
> | moving it to notepad, it comes back with 11 seconds. But, when I create a
> | pivot table on this same data, it gives me an average of 00:00 (looking at
> | the top it lists it as 0.00013144425832868. So why is the pivot table not
> | averaging it correctly? I must be doing something wrong!
> |
> | Thanks again!
> |
> | Just using Excel to do this so I don't have to do all the manual stuff, is
> | not working. The average on the bottom ends up being
> |
> |
> | "Niek Otten" wrote:
> |
> | > <about mulitplying by 24, etc, but this doesn't get it to a flat number.>
> | >
> | > It does, but you'll have to format afterwards as General or Number to be able to see that. Excel keeps changing it to Time
> each
> | > time you enter or edit the formula.
> | >
> | > --
> | > Kind regards,
> | >
> | > Niek Otten
> | > Microsoft MVP - Excel
> | >
> | > "Kaspr" <(E-Mail Removed)> wrote in message news:8F1CAA46-8012-439E-A7F3-(E-Mail Removed)...
> | > |I am looking at timestamps on a server log and trying to average the
> | > | difference between the times in a pivot chart. The time is milliseconds and I
> | > | just want the flat number. Since the timestamp is based on the calculation
> | > | from a certain point in time, it wants to convert the date to a very small
> | > | fraction.
> | > |
> | > | Can someone help me understand how to take the time and convert it to just a
> | > | flat number? I have seen a lot of posts about mulitplying by 24, etc, but
> | > | this doesn't get it to a flat number.
> | > |
> | > | Thanks!
> | >
> | >
> | >
>
>
>