Excel INT, TRUNC & MOD — Drop the Decimal and Find the Remainder

TL;DR — INT and TRUNC both remove the fractional part of a number, but they disagree on negatives: INT rounds toward minus-infinity (so INT(-2.5) is -3), while TRUNC simply chops toward zero (so TRUNC(-2.5) is -2). For positive numbers they're identical. MOD returns the remainder after division — and the Excel gotcha is that the remainder takes the sign of the divisor, not the number, so =MOD(-1, 3) is 2, not -1. That single fact differs from most programming languages and is the top MOD surprise. All three work in every version.

=INT(-2.5)        ' -> -3   floor: toward minus-infinity
=TRUNC(-2.5)      ' -> -2   chop: toward zero
=MOD(-1, 3)       ' -> 2    remainder takes the divisor's sign

These three are the workhorses of "get me the whole part" and "get me what's left over." They feel trivial — until a negative number turns up, or you port a formula from another tool, and the answer is off by one or flips sign for no visible reason. Both surprises come from which direction each function leans. This guide leads with that mental model, then the traps that follow from it.

What you'll learn

The mental model: chop toward zero vs floor toward minus-infinity
Why INT and TRUNC agree on positives and split on negatives
TRUNC's second argument — chopping to a set number of decimals
MOD as the remainder, and the divisor-sign rule that trips everyone
Two everyday wins: splitting date from time, and zebra striping
The judgment call: floor, chop, or remainder

The mental model: chop vs floor

Both INT and TRUNC "remove the decimals," but they answer a different question when the number is negative:

TRUNC chops — it deletes the digits after the point and keeps the sign. Always toward zero. TRUNC(2.9) → 2, TRUNC(-2.9) → -2.
INT floors — it returns the largest integer less than or equal to the number. Always toward minus-infinity. INT(2.9) → 2, INT(-2.9) → -3.

' positive: identical
=INT(2.9)    ' -> 2
=TRUNC(2.9)  ' -> 2
' negative: they diverge
=INT(-2.9)   ' -> -3   (the integer BELOW -2.9)
=TRUNC(-2.9) ' -> -2   (just delete the .9)

For positive numbers you can use either and never notice. The moment a negative appears — a loss, a temperature, a coordinate — they part ways, and picking the wrong one is the silent "off by one on negatives" bug.

The rule: choose by what you mean on negatives

The decision is simple once you name it. If you mean "always round down the number line" — a true floor, the way a calendar age or a stair count works — use INT. If you mean "just keep the integer portion, sign and all" — strip the cents off an amount that could be a credit or a debit — use TRUNC.

=INT(-1.2)     ' -> -2   "one full step below" (floor)
=TRUNC(-1.2)   ' -> -1   "the whole-number part" (chop)

A good tell: if you'd be unhappy that a -1.2 result became more negative, you wanted TRUNC. If you'd be unhappy that it became less negative, you wanted INT. This is the same toward-zero-vs-toward-infinity split that separates ROUNDDOWN from a true floor — see Excel ROUND, ROUNDUP & ROUNDDOWN.

TRUNC's hidden second argument

Unlike INT, TRUNC takes an optional second argument: how many decimal places to keep before chopping. It does not round — it cuts.

=TRUNC(3.14159, 2)   ' -> 3.14    keep 2 decimals, no rounding
=TRUNC(3.149, 2)     ' -> 3.14    NOT 3.15 — it chops, never rounds
=TRUNC(1234.5, -2)   ' -> 1200    negative: chop to the hundreds

For positive numbers TRUNC(x, 2) matches ROUNDDOWN(x, 2), but the intent is different: TRUNC says "cut here," ROUNDDOWN says "round toward zero." When you specifically want truncation — taking the first few decimals of an ID, or deliberately discarding precision — TRUNC states it clearly.

MOD: the remainder, and the sign trap

MOD(number, divisor) returns what's left after dividing — the remainder. =MOD(7, 3) is 1 because 7 = 2×3 + 1. Simple, until a negative enters.

=MOD(7, 3)     ' -> 1
=MOD(-1, 3)    ' -> 2     NOT -1
=MOD(1, -3)    ' -> -2    sign follows the DIVISOR

In Excel, the remainder takes the sign of the divisor, because MOD is defined as number - divisor * INT(number / divisor) — and that INT floors toward minus-infinity. This is different from C, Java, and VBA's Mod operator, where the remainder follows the dividend (so VBA's -1 Mod 3 is -1). If you've ported a formula or a macro and the modulo results flipped sign, this is why. When you need the language-style behaviour, compute it explicitly rather than assuming MOD matches.

Two everyday wins

Split a date from a time. Excel stores a date-time as a number whose integer part is the date and whose fraction is the time. INT and MOD pull them apart:

=INT(A2)       ' -> the date (drops the time)
=MOD(A2, 1)    ' -> the time of day (drops the date)

Zebra-stripe or group every Nth row. MOD against a row number gives a repeating cycle, perfect for conditional formatting or grouping:

=MOD(ROW(), 2) = 0          ' TRUE on every even row -> banded rows
=MOD(ROW() - 2, 3) = 0      ' TRUE every 3rd row from the top
=MOD(value, 2) = 0          ' is value even?

These two patterns — date/time splitting and cyclic grouping — are where INT and MOD earn their keep far beyond "drop the decimal."

The judgment call: floor, chop, or remainder

Three clean choices. Want the whole-number part, sign preserved? TRUNC (and use its second argument to chop to set decimals). Want a true floor toward minus-infinity, or to extract a date from a date-time? INT. Want what's left over — periodic patterns, even/odd, time-of-day, "every Nth"? MOD, while remembering the remainder follows the divisor's sign. The only real trap is using INT where you meant TRUNC (or vice-versa) on data that can go negative, so let the sign of your data, not habit, pick the function.

How ExcelMaster helps

The intent behind these is usually concrete — strip the time off this timestamp, flag every third row, take the whole units and handle the remainder separately — and the bugs are negative-number direction and MOD's sign rule. ExcelMaster maps the sentence to the right function: INT for a floor or a date, TRUNC to keep the integer part of a signed value, MOD for cycles and remainders with the sign behaviour accounted for. You say what you want from the number; it writes the version that survives negatives.

Frequently asked questions

What's the difference between INT and TRUNC in Excel?

Both drop the decimals. INT rounds toward minus-infinity (a true floor), so INT(-2.5) is -3. TRUNC chops toward zero, so TRUNC(-2.5) is -2. They're identical for positive numbers and differ only on negatives.

Why does MOD return a positive number for a negative input?

Excel's MOD gives the remainder the sign of the divisor, so =MOD(-1, 3) is 2. This differs from VBA and most programming languages, where the remainder follows the dividend.

How do I get the time portion out of a date-time cell?

Use =MOD(A2, 1) for the time of day and =INT(A2) for the date. Excel stores the date in the integer part of the number and the time in the fraction.

How do I truncate to 2 decimal places without rounding?

Use =TRUNC(number, 2). Unlike ROUND, it cuts the extra digits rather than rounding them, so =TRUNC(3.149, 2) is 3.14.

How do I check if a number is even or odd?

Use MOD with a divisor of 2: =MOD(A2, 2) = 0 is TRUE for even numbers and FALSE for odd. (Excel also has the dedicated ISEVEN and ISODD functions.)

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-27.