Averaging time
 

I have columns with data in the following formats:
Column A Column B Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00 PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS using time value.

I then want an average of the time values in all of ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

What happens, if the hours go over 24 then you must use a custom format

[hh]:mm:ss

otherwise average(range)

should and will work

Regards,

Peo Sjoblom

"katgolightly" wrote:

I have columns with data in the following formats:
Column A Column B Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00 PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS using time value.

I then want an average of the time values in all of ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

Looking at my data I think the problem is because in some cases the date/time
in ColC is more than 24 hours later. How do I get it to come up with a time
value for that (i.e. 36:01:01 for 36 hours/3days, 1 minute, 1 second)

Kat

"katgolightly" wrote:

I have columns with data in the following formats:
Column A Column B Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00 PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS using time value.

I then want an average of the time values in all of ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

How do I do a custom format? (new to me)

Kat

"Peo Sjoblom" wrote:

What happens, if the hours go over 24 then you must use a custom format

[hh]:mm:ss

otherwise average(range)

should and will work

Regards,

Peo Sjoblom

"katgolightly" wrote:

I have columns with data in the following formats:
Column A Column B Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00 PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS using time value.

I then want an average of the time values in all of ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

It's just that it won't display more than 24 and then it starts over again,
however the underlying value is correct, just change the firmat to custom
format [hh]:mm:ss

Regards,

Peo Sjoblom

"katgolightly" wrote:

Looking at my data I think the problem is because in some cases the date/time
in ColC is more than 24 hours later. How do I get it to come up with a time
value for that (i.e. 36:01:01 for 36 hours/3days, 1 minute, 1 second)

Kat

"katgolightly" wrote:

I have columns with data in the following formats:
Column A Column B Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00 PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS using time value.

I then want an average of the time values in all of ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

Go to Format Cells Number tab, click the
word "Custom" and fill in Peo's suggestion on the right
under "Type:".

HTH
Jason
Atlanta, GA

-----Original Message-----
How do I do a custom format? (new to me)

Kat

"Peo Sjoblom" wrote:

What happens, if the hours go over 24 then you must
use a custom format

[hh]:mm:ss

otherwise average(range)

should and will work

Regards,

Peo Sjoblom

"katgolightly" wrote:

I have columns with data in the following formats:
Column A Column B
Column C
12/7/2004 2:00:00 PM 3:51:00 12/7/2004 5:51:00
PM

B1 being =C1-A1 and ColB being formatted to HH:MM:SS
using time value.

I then want an average of the time values in all of
ColB. Somewhere the
logic breaks if I try to do =AVG(B1:B255) though.

Kat

.