Of course, in England we say it’s in the boot (of the car) … but that’s not the kind of trunk I mean!!
I was just working on a simple formula that helps us to calculate the oven cooking time for a Turkey. It doesn’t matter what the formula is because what I wanted to do, having found the time, was to show the result in hours and minutes and not as a decimal.
For example, the formula says if you want to cook a Turkey at sea level that weighs about 6.8 kilogrammes, you should cook it for 3.464783 hours. So, what’s that in clock hours? The answer is 3.464783 = 3 hours 27.89 minutes. Using CONCATENATION and the =TRUNC() function, here is the formula I put together to convert from decimals to hours and minutes, assuming that my decimal result is in cell A1:
=TRUNC(A1)&” hr “&(A1-TRUNC(A1))*60&” mins”
Clearly, =TRUNC() is the key to this conversion and what =TRUNC() does is to chop off any decimals from a number or result. =TRUNC(A1) = 3, which is 3 hours.
That being so, all I needed to do to find the decimals so that I could convert, in this case, 0.464783 hours into minutes was to tell Excel to do this =(A1-TRUNC(A1))*60 notice how I added enough brackets to do this properly! That gave me 27.8869723 minutes; but that looks ugly, though, so I used the =ROUND() function to round the minutes off to 2 decimal places. So my final formula is
=TRUNC(A1)&” hr “&ROUND((A1-TRUNC(A1))*60,2)&” mins”
Try it! Copy and paste my results into your own worksheet. It doesn’t have to be a time based formula: =TRUNC() will work with all sorts of calculations. Whatever you want to do, it can do it.
There you are, no need for a specimen file here, but if you just want to test this in a blank worksheet, with your time in any cell other than A1, your conversion can be in any other blank cell by using this amended formula … copy and paste:
CellXX = 3.464783
Cell YY =TRUNC(XX)&” hr “&ROUND((XX-TRUNC(XX))*60,2)&” mins”
I know, you could even create a larger formula to do the entire calculation in cell A1 or XX or YY … again, this is entirely up to you!
Duncan Williamson
Excel with Excel Master 
19 02 2014 at 3:10 am
That’s a bit complicated for me Duncan! My formula is 20 minutes per pound and 20 minutes extra.
19 02 2014 at 8:16 am
I agree Linda but from an Excel point of view it was a bit interesting. BUT, don’t forget that bones v no bones makes a difference. Secondly, in the formula there is a variable that the authors don’t define but I didn’t pursue that because, like you, I wasn’t really interested in the answer … partly because I’ve been a veggie for over 20 years so cooking joints of meat is not the top of my priority list!