INT vs TRUNC in Excel: Different With Negatives

The short answer to INT vs TRUNC in Excel: for positive numbers they are identical, because both remove the decimals. The difference appears with negative numbers. INT rounds down to the next lower whole number, while TRUNC cuts off the decimals and moves toward zero. This comparison is for anyone who works with refunds, temperature changes, time values or test questions that ask "what does this formula return?".

INT vs TRUNC at a glance#

Both remove the fraction from a number; they only disagree when the number is negative.

Why the results differ with negatives#

Think of a number line. Rounding down means moving left, toward smaller numbers. Cutting toward zero means moving toward 0, whatever the sign.

For 7.8, left and toward zero are the same direction, so both functions return 7. For -7.8, they point in opposite directions. Moving left from -7.8 reaches -8, which is smaller. Moving toward zero reaches -7. So =INT(-7.8) returns -8 and =TRUNC(-7.8) returns -7.

This is why the difference matters: in a sheet with refunds, losses or negative changes, swapping one function for the other changes your totals.

How INT works#

INT takes one number and returns the largest whole number that is less than or equal to it.

=INT(7.8)
=INT(-7.8)
=INT(A2)

The first two return 7 and -8. INT has no second argument, so it always returns a whole number.

INT is also the standard way to split a date-time value. Excel stores dates as whole numbers and times as the decimal part, so if A2 holds 15 March 2024 at 14:30, =INT(A2) returns just the date. Format the result as a date. The time part is =A2-INT(A2) or =MOD(A2,1). The guide to MOD and INT shows how the two work together to split hours, weeks and boxes.

How TRUNC works#

TRUNC, short for "truncate", cuts a number to a set number of decimal places without rounding.

=TRUNC(7.89)
=TRUNC(-7.89)
=TRUNC(7.89,1)
=TRUNC(1234.5,-2)

These return 7, -7, 7.8 and 1200. The second argument, num_digits, is optional and defaults to 0. A positive value keeps that many decimals, and a negative value zeroes out digits to the left of the decimal point.

Notice that =TRUNC(7.89,1) returns 7.8, not 7.9. TRUNC never rounds up; it only drops digits. For rounding to the nearest value, use ROUND. The ROUND function guide and the comparison of ROUND vs ROUNDUP vs ROUNDDOWN cover the rounding family.

INT and TRUNC side by side on the same data#

Here are daily price changes for five stocks:

The positive rows agree. Every negative row with decimals differs by exactly 1. Row 4 is a useful test question: INT turns -0.6 into -1, while TRUNC turns it into 0.

A typical test question#

Screening tests like to ask what a formula returns, and INT and TRUNC with negatives are a favourite. Try this one:

=INT(-0.5)+TRUNC(-0.5)

The answer is -1. INT rounds -0.5 down to -1, and TRUNC cuts it to 0, so the sum is -1 + 0. If you can explain each half, you understand the difference. Two more facts help with these questions: =TRUNC(B2) always matches =ROUNDDOWN(B2,0), and =INT(B2) always matches =FLOOR.MATH(B2), which also rounds toward negative infinity by default.

Totals on the same data#

Now add up each column. =SUM(C2:C6) returns -1, and =SUM(D2:D6) returns 2. The original changes add up to 0.95. Neither is "wrong", but they answer different questions, so pick the function deliberately.

When to use INT#

Use INT when you want the next lower whole number, or when you work with dates and times. It fits:

• Splitting date-time values: =INT(A2) for the date, =A2-INT(A2) for the time.
• Whole units that can't go negative, such as full boxes from a count of items: =INT(B2/12) for full dozens.
• Age or tenure in whole years from a decimal, such as =INT(YEARFRAC(B2,C2)).
• Whole weeks between two dates: =INT((C2-B2)/7) returns 3 for a 25-day gap, because only three full weeks fit.
• Floor-style bucketing, such as grouping scores into bands of 10: =INT(B2/10)*10 turns 67 into 60.
• Older formulas and test answers, where INT is the traditional choice.

When to use TRUNC#

Use TRUNC when you want to drop digits without rounding, or keep a set number of decimals. It fits:

• Negative values where you want the whole part only, such as -3.75 becoming -3.
• Cutting to decimals without rounding, such as rates that must show 2 decimals exactly as they start: =TRUNC(B2,2).
• Removing small digits from large numbers, such as =TRUNC(B2,-3) to show thousands.
• Matching a rule written as "ignore the decimals", which usually means cut, not round down.
• Splitting a signed number into parts. =TRUNC(B2) gives the whole part and =B2-TRUNC(B2) gives the fractional part with the same sign, so -3.75 splits cleanly into -3 and -0.75. With INT, the parts would be -4 and 0.25, which is correct maths but rarely what a report needs.

Invoices are a common place where the choice matters, because tax and discounts can be negative; see building an invoice with formulas.

Common mistakes with INT and TRUNC#

1. Assuming they are always the same. They are for positives, so the bug only shows up once a negative number arrives.
2. Using INT to "remove decimals" from refunds. INT turns a -12.50 refund into -13. Use TRUNC if you only want to drop the decimals.
3. Expecting TRUNC to round. =TRUNC(4.99) returns 4, not 5. Use ROUND for normal rounding.
4. Giving INT a second argument. =INT(A2,2) returns an error message about too many arguments. Use TRUNC or ROUNDDOWN to keep decimals.
5. Hiding decimals with formatting instead. Clicking Decrease Decimal changes what you see, not the value. Calculations still use the full number.
6. Using INT on small rates. =INT(0.075) returns 0, so a 7.5% rate disappears. To drop extra decimals from a rate, use =TRUNC(B2,3), which keeps 0.075.

For the wider family, including CEILING and FLOOR, see CEILING vs FLOOR and the overview of Excel's rounding functions.

Our recommendation#

If your numbers are never negative, INT and TRUNC are interchangeable, and INT is the more familiar choice. If negatives can appear, decide what you mean. "Round down" means INT. "Drop the decimals" or "keep the whole part" means TRUNC. Use INT for dates and times, and TRUNC when you need to keep a set number of decimal places.

In short: INT rounds down, TRUNC cuts toward zero, and they only differ for negatives.

Practise it#

Try the exercise on this page: cut -3.75 to its whole part with TRUNC and check that you get -3, not -4. Then practise rounding in a live spreadsheet: you type each formula, and it checks your answer instantly.