Values in decimal, need result in hours and minutes

android1

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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

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
Top