Values in decimal, need result in hours and minutes

android1

Board Regular
Joined
Feb 12, 2016
Messages
69
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,324
Messages
6,118,892
Members
448,854
Latest member
Eduard_Stoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top