How to convert Time to number

[Master Foo]

I can't figure out how to convert Time in format (dd:hh:mm) into number (01:11:50 = 35.83333) ~ Time is in cell "G3" and number needs to be in cell "G5".
I also can't figure out the formula to sum the time if time starts 11:00 PM and ends 7:00 AM (Times are manually entered into two different cells and formula for sum total is in third in format hh:mm).

Thanks,
Master Foo

[shg]

Please Login or Register  to view this content.

[FDibbins]

For the 2nd part, try this...
=(IF(A2>B2,1,0)+B2)-A2

[Master Foo]

I don't use 'Seconds' so I converted it to (=24*HOUR(G3) + MINUTE(G3)/60) and it returns 264.83333 instead of 35.83333

[FDibbins]

Leave the seconds on, if you dont have any, it wont make any difference

[shg]

I don't use 'Seconds' so I converted it to (=24*HOUR(G3) + MINUTE(G3)/60) and it returns 264.83333 instead of 35.83333

I don't know what that means. Excel interprets the value as hh:mm:ss regardless of what you intended, unless it's stored as text. My formula returns the correct result, no?

[Master Foo]

I put (=24*HOUR(G3) + MINUTE(G3) + SECOND(G3)/60) in cell G5 and it returns 314.00000 **(G3 is in format dd:hh:mm and shows 01:11:50 which equals 35hrs 50min)
This is the formula in G3: (=IF(D2="","",SUM(D2,D5,D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41)))

[Master Foo]

Second part works Great.

Thank you,
Master Foo

[shg]

I put (=24*HOUR(G3) + MINUTE(G3) + SECOND(G3)/60) in cell G5 and it returns 314.00000 **(G3 is in format dd:hh:mm and shows 01:11:50 which equals 35hrs 50min)

Ah, I get it.

Please Login or Register  to view this content.

[Master Foo]

That works perfect. So simple it just eluded me. Duh (Smacks self on forehead)

Thank you,
Master Foo