Time-of-day Average

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi:
I have numerous Excel 2000 cells (some of which are blank) that have
time-of-day entries (military-time formatted as text).
I need a formula for the average time-of-day from these entries. Any
ideas??
Thanks in Advance!!
 
Try this as a starter
=TEXT(SUM(TIME(INT(E1:E100/100),E1:E100-INT(E1:E100/100)*100,0))/COUNTA(E1:E
100),"hhmm")
it's an array formula so enter with Ctrrl-Shift-Enter not just Enter

Change the range to suit
 
What is military time formated as text?
If you mean that the cells are formatted as text and then time
is entered as 0900 and 2100 you could try something like


=AVERAGE(IF(ISNUMBER((LEFT(A1:A10,2)&":"&RIGHT(A1:A10,2))*1),(LEFT(A1:A10,2)
&":"&RIGHT(A1:A10,2))*1))

entered with ctrl + shift & enter

If 09:00 is entered as 900 the above won't work

I would probably use a help column

and someting like

=IF(A1="","",IF(LEN(A1)=3,(LEFT(A1)&":"&RIGHT(A1,2))*1,(LEFT(A1:A1,2)&":"&RI
GHT(A1:A1,2))*1))

copy down as long as needed then do the average

=AVERAGE(A1:A10)
 
Hi,

I don't know what military time formatted as text exactly means.

But if it's somewhere around '0900, you could try the following ARRAY
formula (Ctrl-Shift-Enter):

=AVERAGE(IF(A1:A20<>"",A1:A20+0))/2400

And format your result as time (h:mm or compatible format).

Regards,

Daniel M.
 
Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe if I
try braking it down into sections. Will keep trying.
Best Regards,
Bruce
 
Did you spot that my formula spiller over 2 lines, be sure to put it all on
one line.

What are your cell designations?

If you want to send me the workbook, I will put it in for you.

--

HTH

Bob Phillips

GBL said:
Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe if I
try braking it down into sections. Will keep trying.
Best Regards,
Bruce
Click to expand...
 
Hi:
Cell designations are H8:H51.
I used your entire formula. But didn't understand where you got the "hhmm"
designation.
My military-time cell examples are: 17:55, 17:10, 18:00, 17:19, etc...

Bob Phillips said:
Did you spot that my formula spiller over 2 lines, be sure to put it all on
one line.

What are your cell designations?

If you want to send me the workbook, I will put it in for you.

--

HTH

Bob Phillips

GBL said:
Hi Bob:
Thanks for your reply Bob.
In the formula you've sent, I've tried substitution to my cell designations
and I'm getting an error. Trying to understand the logic. Hmmm - maybe
Click to expand...
if
Click to expand...
 
Back
Top