Formatting Average Time

[Monica]

Given a time in and a time out, I can calculate time-spent-waiting. Then, I calculate the average time-spent-waiting by running the Average function across all the previously calculated time-spent-waiting cells. The problem comes with the way the results are displayed. I want to display the results in an h:mm format, but I can only get it to show military time or the decimal equivalent. For example, if the average time-spent-waiting is an hour and a half, I want to see 1:30, but I get 15:50 or 4.6667, depending on how I format it. I'm sure the answer lies in a custom format, but I can't get it to work and I've tried a hundred ways

thanks
Monica

 

[Frank Kabel]

Hi Monica
are you sure your values entered are not larger (maybe also contain a
date). This looks like your average is to high.
Please post some example data and the excat formula you're using

--
Regards
Frank Kabel
Frankfurt, Germany

Given a time in and a time out, I can calculate time-spent-waiting.

Then, I calculate the average time-spent-waiting by running the Average
function across all the previously calculated time-spent-waiting cells.
The problem comes with the way the results are displayed. I want to
display the results in an h:mm format, but I can only get it to show
military time or the decimal equivalent. For example, if the average
time-spent-waiting is an hour and a half, I want to see 1:30, but I get
15:50 or 4.6667, depending on how I format it. I'm sure the answer
lies in a custom format, but I can't get it to work and I've tried a
hundred ways!

 

[Frank Kabel]

Hi
don't add 24 but only 1 (as 24 is the equivalent to 24 DAYS and not 24
hours.):
=(G7<F7,(G7+1)-F7, G7-F7).

or a shorter version
=G7-B7+(G7<B7)


--
Regards
Frank Kabel
Frankfurt, Germany

Time in Time Out Wait Time

7:42 8:15 0:33
13:22 15:16 1:54
23:38 1:04 1:26
10:04 11:04 1:00
13:28 20:20 6:52

There is a formula for in the wait-time field to determine if the

wait time crossed midnight: =IF(G7<F7,(G7+24)-F7, G7-F7).