Calculating average durations expressed as hh:mm:ss

[metalpoker]

I am having trouble dividing a duration/length of time expressed in hh:mm:ss by a normal number - any help much appreciated as my project has been stopped in its tracks!!

I have a number of people accessing a podcast, and the stats from the server give me the total number of connections in a month and the total hours of streamed content. I want to calculate the average duration each user is using the service and express the result as hh:mm:ss.

I seem unable to get the normal cells formatted as general to work in formulas alongside values formatted as hh:mm:ss...

For instance...

A1 - 20 (my whole number - always whole, never any decimal places)
A2 - 205:54:39 (my duration - always hh:mm:ss)
A3 - Where I want the result to go if I can divide the total duration by number or users to get an average

I have tried the obvious =A2/A1 with each cell formatted appropriately as either general or custom hh:mm:ss but nothing works.

Some things to note would be that usually these numbers are big, like 507642:35:25 (duration) divided by 200304 users. Also the longer term plan would be add to the formula to not only display the average duration per month but also then display a weekly average duration as well.

Its easy when using mins or seconds and not changing the cell format, but I need the result in time format (even though it is actually a duration and not a time of day!!!)

Any help would be greatly appreciated as I really need to be able to do this!!


Susie x

[ChemistB]

1. Check your duration cell to make sure it's a number (Let's say it's A2, in a blank cell put =ISNUMBER(A2) and see if it's false).

2. If it comes out true, a simple division should work. Make sure your cell is formatted to [hh]:mm:ss

[metalpoker]

Hi ChemistB,

I have formatted the duration cell as [hh]:mm:ss but when I use the formula =ISNUMBER(A2) it returns the answer FALSE. Is it because of the size of the numbers I am trying to use? The actual calculation I need to perform is 509817:34:50 (duration) / 270076 (regular number)...

With everything in place all I get is #VALUE! error...

Any more ideas?


Susie x

[daddylonglegs]

Hello Susie,

Excel can output larger time values from calculations....but it doesn't recognise times in cells greater than or equal to 10,000 hours (such "times" will be treated as text)......so to make this work you somehow have to use a calculation to convert, e.g. if you have

509817:34:50

in cell A2 then you can convert that to a valid time value (that looks exactly the same!) by using this formula in another cell [note - assumes that you always include minutes and seconds]

=LEFT(A2,LEN(A2)-6)/24+("0:"&RIGHT(A2,5))

format that cell as [h]:mm:ss

Now you can use that for your calculation

If A2 happens to be less than 10,000 hours (and is therefore a valid time) the above isn't required (and won't work) so you can introduce an IF to deal with that, i.e.

=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-6)/24+("0:"&RIGHT(A2,5)))

....now you can do everything in one formula, if you want, and just divide by A1, i.e.

=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-6)/24+("0:"&RIGHT(A2,5)))/A1

again format as [h]:mm:ss

for the example in your last post I get 1:53:16, is that right?

[arthurbr]

If total time is in A1 and the divider in A2 try

Please Login or Register  to view this content.

which gives the number of seconds in decimal.

Format as needed

[metalpoker]

Hi daddylonglegs...

....now you can do everything in one formula, if you want, and just divide by A1, i.e.

=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-6)/24+("0:"&RIGHT(A2,5)))/A1

again format as [h]:mm:ss

for the example in your last post I get 1:53:16, is that right?

This works perfectly!! I understand the principles of whats going on but Excel knowledge would never stretch to being able to work this out myself so thankyou very much!!

1:53:16 was correct and it works for the others I have tried also

I do have a couple of extra questions though...

1. Is the original number inputted by myself of 509817:34:50 left as General format, with only the result cell needing the formatting of [h]:mm:ss?
   
2. Excel auto-formatted my first result as [hh]:mm:ss (with 2 hh) but the 2nd one I had to format myself... Will it make a difference whether I use [h] as you mentioned or [hh] as Excel produced automatically?
   
3. Lastly, with my average results as durations formatted in hours:mins:secs will the values still work with me creating charts etc?

Many thanks!!


Susie x

[daddylonglegs]

Hello Susie

1) Yes, that's right. If the hours are over 10,000 then you won't be able to use any number formats anyway - if you just input what's required, e.g. 12345:12:34 or 9876:54:32 then Excel should automatically treat the first one as text and the second one as a time - you shouldn't need to change any formats.

2) It doesn't make much difference, except that if you use [hh] you will get a leading zero for single hours, e.g. 01:53:16 instead of 1:53:16 but it has no effect on the underlying value - just the display

3) That shouldn't be a problem - internally valid times are simply numbers in Excel (1 = 1 day), so you can chart with them like any other numbers

[metalpoker]

Thankyou for your assistance, you have been incredibly helpful!!
And thankyou to everyone else who offered contibutions too, I shall definitely remain here for any Excel questions I might have going forward

Susie x