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.

 

[55iis]

Seems like it does all the work without formulas & I was stupid enough not to try it before posting this thread. But I have another problem now, how can I add hours that are over 24? F.e. I worked a total of 33 hours on E1 exercise, but when I type such it doen't work and date in front appears. And if I do [h]:MM format it shows good but my total time counts wrong then.

 

[55iis]

Either no one helping on this forum or it's not even possible. Made my new formula because seems like I need it anyway, but the problems seems to stay - my total hours jumps over a day and starts to count from scratch once passes 24 hours (f.e. I put 52 hours in total but my total shows as 4 hours because 52 - 48 = 2 days & 4 hours). I need it to show 2 days tho, and if it's 1464 hours I need it to show as 2 months (because 1464 hours = 60 days & 1 day). And if it's under 24 hours my total should show time in format of HH:MM.

My new formula should be ok to make it work, but hours jump & I don't know how to make it happen. Formula in "???" column:

=IF(HOUR(SUM(D38:D44))>=720;HOUR(SUM(D38:D44))/720;IF(HOUR(SUM(D38:D44))<24;HOUR(SUM(D38:D44));HOUR(SUM(D38:D44))/24))

 

[ajman5252]

i am a newbie , so i could be wrong... why wont try in this simple step

go to format cells>custom> and type[hh]:mm

 

[55iis]

Visualy it works, but in formulas time jumps a day anyway and 52 hours counts as 4 hours. If it jumps over 24 hours I need "total time" to show up as days (f.e. if 52 hours total I need result of 2) & if it jumps over 30 days I need it to show up as months.

 

[FormR]

Hi give this a try:

=TEXT(SUM(D38:D44)/IF(SUM(D38:D44)>=30,30,1),LOOKUP(SUM(D38:D44),{0,1,30},{"hh:mm","0 ""Days""","0 ""Months"""}))

 

[55iis]

Formula contains error (marks 30,30,1 place)

 

[FormR]

Formula contains error (marks 30,30,1 place)

Hi, try replacing the commas with semi-colons, so..

=TEXT(SUM(D38:D44)/IF(SUM(D38:D44)>=30;30;1);LOOKUP(SUM(D38:D44);{0;1;30};{"hh:mm";"0 ""Days""";"0 ""Months"""}))

 

[55iis]

Nice! It's working great Thank you! This thread can be closed or deleted. Thank you once again

 

[55iis]

Looked deeper into formula, it seems like days and months are rounding when it's 0,5<. Is it possible not to?