Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |