[Tom Olgilvy = I really appreciate your help. The answer you gave to my last
question about date/time calculation really helped me out from both a
learning perspective and from a solutions perspective. Many Thanks.]

To extend my problem - I have 2 columns that have date and time stamps in
them. How do I find the Maximum Time, Minimum Time, and the Average Time
given a time format of 2:11:00 PM.

Background - the report counts the number of trouble tickets by priority,
provides a count by Age of the tickets over a range, and it provides the min,
max, and avg handle times.

I dont know how to Sum the Times and find the average? Can anyone help?


Thanks again.

Hi cehrenre -

The following solution doesn't rely on VBA code, but it's simple and fast;
if you need a programmatic solution, we could follow up if necessary.

As a first step, I'd suggest a formula that subtracts the two date_time
values (I assume a StartTime in Col A and a StopTime in Col B). For example,
in column C, enter "=B2-A2" and then use Max, Min, and Average functions to
summarize the resulting column.

It's best if you convert the result directly into minutes to avoid
formatting issues that can give you a result like "1/0/1900 0:19". To do the
subtraction and such a conversion all in one step, modify your subtraction
formula to calculate the number of seconds between Start and Stop times, then
convert that number into minutes as:

=(b2-a2)*86400)/60

"(b2-a2)" provides the portion of a 24-hour period between the Start and
Stop times. Because there are 86400 seconds in a 24-hour period and 60
seconds in a minute, the whole formula provides the minutes (and portions of
a minute) between the two times. Use the round function around this entire
formula to suit your needs.

--
Jay


"cehrenre" wrote:

[Tom Olgilvy = I really appreciate your help. The answer you gave to my last
question about date/time calculation really helped me out from both a
learning perspective and from a solutions perspective. Many Thanks.]

To extend my problem - I have 2 columns that have date and time stamps in
them. How do I find the Maximum Time, Minimum Time, and the Average Time
given a time format of 2:11:00 PM.

Background - the report counts the number of trouble tickets by priority,
provides a count by Age of the tickets over a range, and it provides the min,
max, and avg handle times.

I dont know how to Sum the Times and find the average? Can anyone help?


Thanks again.