Values in decimal, need result in hours and minutes

[android1]

Hi,

I have a report which throws out a column (A) with projected hours and a column (B) with actual hours worked.
These figures are in decimal form. I need column C to display A minus B in hrs and mins.
My problem is that the values in A and B cannot easily be formatted to hrs and mins. In A1 for example
I have 26.5, in B1 21.4. The .5 and .4 are actually 50 mins & 40 mins (not .5 & .4 of an hour).(I will never have .6 or higher).
I'm stumped.

Gerry

 

[Rick Rothstein]

Use this formula to calculate the difference...

=SUBSTITUTE(TEXT(A1,"00.00"),".",":")-SUBSTITUTE(TEXT(B1,"00.00"),".",":")

and Custom Format the cell you place the formula in with this Type pattern...

[h]:mm

 

[etaf]

so are you only dealing with 10 minutes

or would .54 be 54 minutes
.12 be 12 minutes ?

if so then
=TIMEVALUE(INT(B2)&":"&ROUND(MOD(B2,1)*100,2)&":"&0)
should convert for you

This probably can be simplified

 

[android1]

Thanks a million Rick, does exactly what I needed. One small issue - if the result in C1 is a negative value (eg 4 - 5.3 would give me -1hr 30mins) how do I display
this? Currently I am getting the error message 'Dates & times that are negative are too large to display as ########'

Gerry

 

[android1]

Hi,

I needed to change to the 1904 date system to display the negative values. Thanks again Rick and thanks to you too etaf for taking time out to help me.

Gerry