Formula to count time in MS Excel 2010

[55iis]

Hello I'm new there so straight to the point of my problem. I have to make an table like this one:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]exercise_nr
[/TD]
[TD]time
[/TD]
[/TR]
[TR]
[TD]E1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]E2
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]E3
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]E4
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]E5
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]E6
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]E7
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]total time:
[/TD]
[TD]???
[/TD]
[/TR]
</tbody>[/TABLE]

So, in time section my numbers should be in HH:MM format, but when I do so via custom & choose such type (format cells -> number -> custom -> hh:mm), then type any number & it brings date in front of this & shows 00:00 (f.e. I type 7 and ENTER - I get the result of 1900.01.07 00:00:00 instead). I don't understand what's the problem since i've chose HH:MM format.

Other part, I need to create formula to count total time I spend doing exercises. But it's not the regular one - if sum of hours is over 24 it should show up as days & if it's over 30 days - as months (if less than 24 hours then as hours tho). I come up with something like this - IF(SUM(C3:C9)>720;SUM(C3:C9)/720;IF(SUM(C3:C9)<24;SUM(C3:C9);SUM(C3:C9)/24)) but it's not anywhere close to be a good one. Can someone help on this, is it possible to change format once it goes over 24 hours and make it regular number (not HH:MM) & so on.

I'm using Excel 2010, although I would need to put same formula into google docs excel.

 

[FormR]

but what about zero, what does it give me and why it's doin' nothing for me? (in 0;1;30) :D

If the sum of your times is less than 1 (i.e. less than a day) then the largest value in the lookup_vector {0,1,30} that is less than or equal to the lookup_value (the sum of times) is zero - so "hh:mm" is passed to TEXT() function to display the results as you wanted for times less than a day.

Hope this helps