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]

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

Can you give a few varied examples of the total times and what the expected result should be for each?

 

[55iis]

I've added comma's&zero's in formula, now it works fine. Thank you. The problem I was getting that at 36 hours it already showed 2 days although it was 1,5 days, same with months. Now, with comma's, it look ok.

 

[55iis]

To fully understand formula can you tell me why exactly are 30;30;1 & 0;1;30 numbers used? Thanks in advance.

 

[FormR]

OK great - thanks for letting us know - oh, and welcome to the forum

 

[FormR]

30;30;1

This is part of the IF() function - if the sum of time is greater than 30 days then divide by 30 (to get the months) else divide by 1 (to keep the result the same as the sum)

0;1;30

If the sum of time is between the is less than 1, format the result as "hh:mm", if it's less than 30 format as "0 Days" if its 30 or higher format as "0 Months"

 

[55iis]

If the sum of time is between the is less than 1, format the result as "hh:mm", if it's less than 30 format as "0 Days" if its 30 or higher format as "0 Months"

Between what? It's kinda advanced, tried to read what does LOOKUP function does, but hardly understand lots to learn, happy to join such forum.

 

[FormR]

Sorry, bad editing - it should have read:

If the sum of time <strike>is between the</strike> is less than 1, format the result as "hh:mm", if it's less than 30 format as "0 Days" if its 30 or higher format as "0 Months"

From the MS help for LOOKUP() - where the Lookup_value is the total time and the lookup_vector is {0;1;30}

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

 

[55iis]

ok. Can you check if I did understand well and help me to understand it right? I'm here to learn. First of all 30;30;1 - if sum is over 30 days (first 30 meaning), then we devide that sum by 30 (second 30 meaning) to get months, if it's less than 30 days then we just devide by 1 (1 meaning) and move on to the second phase of 0;1;30 (we look at this only if there was under 30 days in 1st phase (?)). With first IF we only check if theres more than 30 days or not, right? Nothing more?

There I have some difficulties understanding why it's a zero first if there is less than 1 day (second meaning in LOOKUP?) & then format of a result will be "hh:mm" (first meaning of {}), if there is less than 30 days (third in LOOKUP?) then format will be "0 days" (second meaning of {}) & if more than 30 days (third in LOOKUP?) then format will be "0 months" (third meaning of {}).

 

[FormR]

Hi, yes - that's about the gist of it.

 

[55iis]

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