Convert Number in to Hours....

[DR]

Hi,

I have to fill the time sheet, in that i have to give activity name and
hours i worked on the activity.when I sum the total of hours i worked ,it is
taking as normail No..Can i get any formula to covert it in to hours and
minuts

Ex : A1 activity i worked for 30mins (.30) , A2 activity I work... 1h.20 min
(1.20) , A3 - 1h .50m (1.50)

if I sum the total it had to show 3h.40min (but it is showing as 3 )

Can I get any formula for this ...?

 

[Fred Smith]

It's unclear how you are entering your data. 30 minutes would normally be
entered as either 0:30 (30 minutes) or 0.5 (half an hour). 1h20min would be
either 1:20 (h:mm) or 1.33 (1 and one-third hours). But it looks like you
are entering this as 1.20.

Entering 1 hour and 20 minutes as 1.20 makes it impossible to add up, as you
have found. My best recommendation is to enter either times (1:20) or hours
(1.33). Then they will add properly.

Regards,
Fred.

 

[Ron Coderre]

With
A1:A10 containing pseudo-times in the format hours-decimal_point-minutes
Example:
1.20 = 1 hour and 20 minutes
1.02 = 1 hours and 2 minutes

This formula sums those "times" and returns the result as proper time
B1 (formatted as time):
=SUMPRODUCT(--SUBSTITUTE(TEXT(A1:A10,"0.00"),".",":"))

Is that something you can work with?

 

[Ron Coderre]

Another alternative...
B1: =SUMPRODUCT(TIME(INT(A1:A10),MOD(A1:A10,1)*100,))

Regards,

Ron Coderre
Microsoft MVP (Excel)