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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,875
Office Version
  1. 2019
  2. 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
5,649
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
69
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
69
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,864
Messages
5,834,057
Members
430,260
Latest member
MANICX100

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