Convert minutes to hours

[Alex Sanchez]

Hello there,

Is there a way to convert minutes to hours and display the answer as decimal. For Example, in cell A1 I have 90 and in cell A2 I have the following formula =A1/60 that formula gives me 1.5 as the answer. The answer I would like to see is 1.3.

Any help would be appreciated.

Thanks,
Alex

 

[barry houdini]

Hello Alex, try this formula

=TEXT(A1/1440,"[h].mm")+0

 

[Alex Sanchez]

Thanks Barry,

It worked!!! If it is not too much to ask, why are you dividing it by 1440? and why the +0 at the end? I just would like to understand this formula.

thanks,
alex

 

[barry houdini]

In Excel 1 = 1 day so as you have minutes in A1 then you have to divide that by the number of minutes in a day to get a time value, so if you used just

=A1/1440

....then formatted that as a time value you'd see 1:30......to make that into a decimal 1.3 I used TEXT function which converts the 1:30 to a text value 1.3........using +0 converts the text value to numeric, if you don't use +0 you'll still see 1.3 as the result......which might be OK for you (1.30 in fact....)

 

[Alex Sanchez]

Thanks Barry,

Very well explain answer. Makes a lot more sense now.

If the value in the cell is a negative number, how come the formula gives a #VALUE! error? is there a way to have excel not do that?

Thanks,
Alex

 

[barry houdini]

Excel doesn't like negative time values......but you can get round that by amending the formula, i.e. use this version

=TEXT(ABS(A1)/1440,"[h].mm")*SIGN(A1)

and -90 will become -1.3