Tomorrow can't be today

[Lawrence Dodge]

Hi all -
I am working with times of job completion (not time of day) and have a column formatted hh:mm. When I sum it or average it, I only get a number < 24 hours. I have tried to over ride this by doing =if(mod(sum(range), 1), sum (range), sum (range)+24) with no luck. With the average (range), the number that shows up means nothing that I can figure out. Any help would be greatly appreciated.

04:20
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:44
00:07
00:00
06:38
00:00
00:10
12:00
00:00
03:09
00:15
00:00
01:00
00:00
04:23

 

[prasaddn]

Hi,

You can continue to use your sum / average function as usual. To see the hour in more than 24 hrs format, you just need to format the cell to [h]:mm:ss

Regards,
Prasad DN

 

[Ajesh]

Just format the cell as [h]:mm:ss. this is for elapsed time.

 

[bobhc]

Tomorrow can't be today

Tomorrow is today.......just not arrived yet....

 

[Lawrence Dodge]

Thank you for the [h] advice, but I tried it with this column of information and the average is not working out correctly. I manually figured out the sum and got 5 hour 30 minutes with an average of 1 hour 6 minutes, not 1 hour 22 minutes. Any thoughts on what I am doing wrong?
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
0:10:00
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
1:40:00
0:00:00
0:55:00
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
0:00:00
2:45:00
=sum (R31:R52)2:45:00
=averageif (R31:R52,">0")1:22:30

 

[Ajesh]

You need to apply [h]:mm:ss format to the cell which contains Sum and Average formula and not to the Values to be summed/Averaged

 

[Lawrence Dodge]

Hi Ajesh --
I tried this and still I am getting the 1:22 instead of 1:06. *SIGH* Any other thoughts?
thanks

 

[NARAYANK991]

Hi Lawrence ,

You need to check your manual calculation ; there are only 4 non-zero entries viz. 00:10:00 , 01:40:00 , 00:55:00 and 02:45:00 ; if these are totalling 5 hours and 30 minutes , which is correct , then dividing this by 4 will not give 1 hour 6 minutes ; it should be 330/4 = 82.5 minutes , which is 1 hour 22 minutes and 30 seconds.

Narayan

 

[Ajesh]

request you to upload a sample file and will have a look.

 

[Lawrence Dodge]

OK I feel a bit humbled, that's what I get for looking at the same numbers and expecting something different to pop out.
thanks
Lawrence

 

[Ajesh]

Its fine. We all do it sometime. Cheers.