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. That’s a bit complicated for me Duncan! My formula is 20 minutes per pound and 20 minutes extra.

    • 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: