Timestamp ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

elroyerni - 31 May 2007 22:16 GMT

Hi -

I run a query in oracle and import the results into excel. What I'm
looking to do is create a column in excel with the avg of the
timestamps. For example:

CRAT_TSP    RESOL_DTE
05/27/2007 07:50:19    05/27/2007 07:50:34
05/27/2007 07:50:19    05/27/2007 07:50:38
05/27/2007 07:53:24    05/27/2007 07:53:38

I want to subtract the resolved minus created timestamp and create a
column with the average it took for it to complete. Can someone point
me in the right direction?

THANKS!!

Reply to this Message

Bernie Deitrick - 31 May 2007 23:46 GMT

=B2-A2

and format as time, then copy to match your values.

HTH,
Bernie
MS Excel MVP

> Hi -
>
[quoted text clipped - 12 lines]
>
> THANKS!!

Reply to this Message

Dave O - 01 Jun 2007 00:07 GMT

With creation time in column A and resolved in column B, enter a
formula in column C that looks like this:
=(C1-B1)*(24*60*60)
... where (24*60*60) is the number of seconds in a day. The reason for
this requires a discussion of how Excel treats dates and times: a date
is an integer and a time is a portion of a day. Multiplying (resolved
- created) by 86400 converts the difference in timestamps to seconds.
Copy and paste this formula for other rows.

At the bottom of the range, you can use the AVERAGE(range) function to
determine the simple average.

Dave O

Reply to this Message