Minutes to hrs calcultion

  • Thread starter Thread starter Mark Lewis
  • Start date Start date
M

Mark Lewis

I have a form where you enter minutes but the calculation needs to give the
result in Hr:min. I've tried
A2=180 A3=20=SUM(A2,A3)/60

The result being 3.3 but I need it to show 3hrs 20 min

Sounds daft I know!
 
you can try the following formulae

=MID(C3,1,FIND(".",C3)-1)&" Hrs"&" "&+MROUND(60*RIGHT(C3,2)/100,1)&"Mins"

provided you are getting the results (=SUM(A2,A3)/60) in cell C3.

Thanks.

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Here is another possibility...

=TEXT(TIME(0,A2+A3,0),"[h] ""hrs"" m ""min""")

Rick
 
Hi Mark,

You can also try the following:-

=MID(SUM(A2,A3)/60,1,FIND(".",SUM(A2,A3)/60)-1)&" Hrs"&"
"&ROUND(60*MID(SUM(A2,A3)/60,FIND(".",SUM(A2,A3)/60),3),0)&" "&"Mins"

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Here is another possibility...
=TEXT(TIME(0,A2+A3,0),"[h] ""hrs"" m ""min""")
Click to expand...

I guess I should mention that this formula assumes the summation of minutes
in A2 and A3 does not exceed 1440 (the number of minutes in a day). Also,
because of that, the square brackets are not necessary....

=TEXT(TIME(0,A2+A3,0),"h ""hrs"" m ""mins""")

Rick
 
Bonsour® Mark Lewis avec ferveur ;o))) vous nous disiez :
I have a form where you enter minutes but the calculation needs to
give the result in Hr:min. I've tried
A2=180 A3=20=SUM(A2,A3)/60

The result being 3.3 but I need it to show 3hrs 20 min
Click to expand...

=SUM(A2,A3)*"0:1"
cell format
[h]"hrs "m" min"
 
Hi Mais

I am sure following formula solve your problem 100 %. Please put the formula
in cell B1 :
=IF(ISERROR(QUOTIENT(SUM(A:A),60)&" hours and
"&MOD(SUM(A:A),60)&"mins"),"Invalid Input",QUOTIENT(SUM(A:A),60)&" hours and
"&MOD(SUM(A:A),60)&"mins")

Let me know how was it. :-)

Thanks
Pawan Kumar
Gurgaon India

Mais qui est Paul said:
Bonsour® Mark Lewis avec ferveur ;o))) vous nous disiez :
I have a form where you enter minutes but the calculation needs to
give the result in Hr:min. I've tried
A2=180 A3=20=SUM(A2,A3)/60

The result being 3.3 but I need it to show 3hrs 20 min
Click to expand...

=SUM(A2,A3)*"0:1"
cell format
[h]"hrs "m" min"
Click to expand...
 
Back
Top