## It’s in the TRUNC!

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

### 2 Responses to “It’s in the TRUNC!”

1. Linda L Sayerwas Howorth Says:

That’s a bit complicated for me Duncan! My formula is 20 minutes per pound and 20 minutes extra.

• excel2007master Says:

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!

This site uses Akismet to reduce spam. Learn how your comment data is processed.