Converting decimals to minutes

[markman235]

Hi everyone,

I track employee time as a cumulative number, so at the end of the week, they could end up with 40.25 hours for 40 hours and 25 minutes.

Is there a formula to get this to convert the .25 to minutes without messing up the 40?

Thanks in advance!

Mark

 

[markman235]

I actually found a solution to this using =SUM((K103-LEFT(K103,2))/1.666)+LEFT(K103,3). This assumes that the 40.25 is in cell K103.

Hope this helps anyone else out!

Thanks,

Mark

 

[JoeMo]

If the employee works 40.25 hours, why is that not 40 hours and 15 minutes?

 

[jtakw]

I actually found a solution to this using =SUM((K103-LEFT(K103,2))/1.666)+LEFT(K103,3). This assumes that the 40.25 is in cell K103.

Hope this helps anyone else out!

Thanks,

Mark

Hi,

The formula you have is going to be a hit and miss depending on your data, check my samples below comparing what you have (Column C) and what I suggest (Column D):

Book1
	A	B	C	D
1	40.25		40.15006	40.15
2	40.25			40 Hours 15 Minutes
3	8.5		8.80012	8.30
4	8.5			8 Hours 30 Minutes
5	0.9		1.440216	0.54
6	0.9			0 Hours 54 Minutes
Sheet1

Cell Formulas
Range	Formula
C3	=SUM((A3-LEFT(A3,2))/1.666)+LEFT(A3,3)
C5	=SUM((A5-LEFT(A5,2))/1.666)+LEFT(A5,3)
C1	=SUM((A1-LEFT(A1,2))/1.666)+LEFT(A1,3)
D1	=(INT(A1)&"."&(MOD(A1,1)*60))*1
D2	=INT(A2)&" Hours "&(MOD(A2,1)*60)&" Minutes"
D3	=(INT(A3)&"."&(MOD(A3,1)*60))*1
D4	=INT(A4)&" Hours "&(MOD(A4,1)*60)&" Minutes"
D5	=(INT(A5)&"."&(MOD(A5,1)*60))*1
D6	=INT(A6)&" Hours "&(MOD(A6,1)*60)&" Minutes"

 

[Tetra201]

@ jtakw:

Just to mention: your formula stumbles on, for example, 40.9 (because of the rounding issues).

Here is a shorter and more robust solution:

=DOLLARFR(A1,60)

 

[jtakw]

You're right Tetra, missed that.