Adding/Averaging Times in Excel

I'm trying to work with time. I'll be adding and averaging many different
process times for a business project. How do I format these times and write
a formula. The tricky part is the times are all the way to milliseconds.
The format is 00:00:00:00 in hh:mm:ss:ms. Say I have 00:05:55:78 and
00:04:34:98. How do I add these and then also get an average of the two??
I've been stuck with this problem for a while. Version 2003 Excel.

Are you sure you're last digits are milliseconds (thousandths a second)
or are they hundredths of a second?

If the latter:

B1: =LEFT(A1,8)+LEFT(A2,8)+(RIGHT(A1,2)+RIGHT(A2,2))/8640000

if you format the result as hh:mm:ss.00 will return 00:10:30.76.

The average:

B2: =B1/2


In article <[email protected]>,
"JD" <[email protected]> wrote:

> I'm trying to work with time. I'll be adding and averaging many different
> process times for a business project. How do I format these times and write
> a formula. The tricky part is the times are all the way to milliseconds.
> The format is 00:00:00:00 in hh:mm:ss:ms. Say I have 00:05:55:78 and
> 00:04:34:98. How do I add these and then also get an average of the two??
> I've been stuck with this problem for a while. Version 2003 Excel.

Thanks--one more thing. If I am adding more times in, how do I drag the
formula to include the new cells. I tried it, and it just keeps two cells
instead of adding the third one. I don't want to have to type in ...Left
A1,A2,A3, etc. for every new cell. Sorry I'm a novice.

"JE McGimpsey" wrote:

> Are you sure you're last digits are milliseconds (thousandths a second)
> or are they hundredths of a second?
>
> If the latter:
>
> B1: =LEFT(A1,8)+LEFT(A2,8)+(RIGHT(A1,2)+RIGHT(A2,2))/8640000
>
> if you format the result as hh:mm:ss.00 will return 00:10:30.76.
>
> The average:
>
> B2: =B1/2
>
>
> In article <[email protected]>,
> "JD" <[email protected]> wrote:
>
> > I'm trying to work with time. I'll be adding and averaging many different
> > process times for a business project. How do I format these times and write
> > a formula. The tricky part is the times are all the way to milliseconds.
> > The format is 00:00:00:00 in hh:mm:ss:ms. Say I have 00:05:55:78 and
> > 00:04:34:98. How do I add these and then also get an average of the two??
> > I've been stuck with this problem for a while. Version 2003 Excel.
>

Hi!

Alternatively you could convert the times (entered and formatted as text) into pure numbers on which you can use the usual functions including =sum(D1:D20) or whatever.

One way to do this is to convert to milliseconds (check: as JE McGimpsey said: is it milli or are they hundredths? I'll assume milliseconds)

If D1 holds the string for a time, e.g. 12:44:32:163 then put this formula in E1:

=1000*(3600*(LEFT(D1,2))+60*(MID(D1,4,2))+MID(D1,7,2))+RIGHT(D1,3)

It gives you the number of milliseconds in the time in D1 (45872163 in this case)

You can do your arithmetic on such numbers and dividing by 24*60*60*1000 (which comes to 86400000) will return it to a fraction of a day. This you can format using Excel's time or custom formats.

(PS I don't know why I didn't have to put in VALUE(LEFT(D1,2)) but maybe multiplying by 3600 etc coerced the text into a value?)

Alf