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:

exercise_nr	time
E1	2
E2	3
E3	4
E4	5
E5	6
E6	7
E7	8
total time:	???

<tbody>
</tbody>

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