Convert decimal value into time

[Minx_Minxy]

Hi guys is me again. I have another problem. I'm getting an average of some values and I need to convert them into time as in dd:hh:mm.

Here is an example:

Average	Days & Hours
0.56
4.76
1.94
0.06
0.26
2.52

<colgroup><col span="2"></colgroup><tbody>
</tbody>

In column Days and hours: 0.56 should show 13h 36min and 4.76 should show 4days 18h 24min and so on.

How do I do it?

Thanks in advance

 

[oldbrewer]

.56 x 24 = 13.44 int(13.44) = 13

60 x .44 = 26.44 minutes int (26.44) = 26

int(1.94) = 1
1.94 - int(1.94) = .94
.94 x 24 as before

 

[Minx_Minxy]

.56 x 24 = 13.44 int(13.44) = 13

60 x .44 = 26.44 minutes int (26.44) = 26

int(1.94) = 1
1.94 - int(1.94) = .94
.94 x 24 as before

Thanks oldbrewer, how do I enter it in the corresponding cell? Is this the formula?

 

[oldbrewer]

17.64	17 days 15 hours 21 minutes
	=INT(A1)&" days "&INT((A1-INT(A1))*24)&" hours "&INT(((A1-INT(A1))*24-INT((A1-INT(A1))*24))*60)&" minutes"

<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Your abbreviations were not consistent, so I spelled everything out (you can abbreviate whatever you want however you want it on your own)...

=IF(A2>1,INT(A2)&"days ","")&TEXT(MOD(A2,1),"h""hours"" m""minutes""")

 

[Minx_Minxy]

Your abbreviations were not consistent, so I spelled everything out (you can abbreviate whatever you want however you want it on your own)...

=IF(A2>1,INT(A2)&"days ","")&TEXT(MOD(A2,1),"h""hours"" m""minutes""")

Thank you so much! it worked a treat ?

 

[SpillerBD]

I was hoping you do it just with formatting.
Like format as " d "days" h "hours" m "minutes" "
Which works for less than 32 days
4.76 yields "4 days 18 hours 14 minutes"
After 32 days, though its a new month and days begins at zero again. Will not accept [d] for extended number of days like [h] for hours.
A tempting solution, but know its limitations.
Rick's is a better all-around solution, it looks like he was an a VBA mindset with all the extra quotes.

 

[oldbrewer]

1.78	01:18:43:11
	=OFFSET(M1,MATCH(G1,M2:M5001,0),11)
				this works up to 50 days in 0.01 decimal intrvals this is around 15 minutes yes - a table of pre-calculated times

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>