 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Average the difference between 2 times
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.  SignatureKind regards, Niek Otten Microsoft MVP - Excel |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 [quoted text clipped - 7 lines] | | 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 > <about mulitplying by 24, etc, but this doesn't get it to a flat number.> > [quoted text clipped - 12 lines] > | > | 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. SignatureKind regards, Niek Otten Microsoft MVP - Excel | 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 [quoted text clipped - 29 lines] | > | | > | 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! > 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. [quoted text clipped - 34 lines] > | > | > | > | Thanks!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.