How to Use the TRUNC Function in Microsoft Excel to Truncate Decimals Without Rounding

Rounding numbers in financial models, invoice schedules, and measurement spreadsheets is standard practice, but standard rounding functions—such as ROUND, ROUNDUP, or ROUNDDOWN—alter numeric values based on mathematical threshold rules. In many accounting, engineering, and data preparation scenarios, analysts need to simply truncate or discard fractional digits beyond a specific precision without performing any rounding whatsoever. For example, converting currency values into whole base units, stripping milliseconds from timestamps, or calculating floor inventory counts.

To perform non-rounding truncation, Microsoft Excel provides the native TRUNC (Truncate) function. Unlike formatting cells to show fewer decimal places (which only masks the underlying full-precision floating-point number), TRUNC directly alters the stored calculation value. Furthermore, TRUNC behaves distinctly from the INT function when dealing with negative values. If you regularly build formulas in spreadsheets, you may also find our tutorials on using the MODE.MULT function in Excel and evaluating mutual exclusivity with the XOR function helpful.

Syntax and Parameters of the TRUNC Function

The syntax for the TRUNC function in Microsoft Excel is straightforward:

=TRUNC(number, [num_digits])

The function accepts two arguments:

• number: The numeric value, cell reference, or calculated expression you wish to truncate.
• num_digits (Optional): An integer specifying the precision of the truncation.
  • If num_digits is omitted or set to 0, Excel truncates all decimal places, returning a whole integer.
  • If num_digits is positive (e.g., 2), Excel truncates digits to the right of the decimal point.
  • If num_digits is negative (e.g., -1 or -2), Excel truncates numbers to the left of the decimal point, replacing them with zeros (e.g., truncating to tens or hundreds).

TRUNC vs ROUNDDOWN vs INT: Critical Differences

Understanding the distinction between TRUNC and other rounding functions prevents subtle accounting discrepancies:

Initial Value	Formula	Result	Explanation
8.79	=TRUNC(8.79, 1)	8.7	Discards the second decimal digit without rounding up.
8.79	=ROUND(8.79, 1)	8.8	Rounds up because the following digit is >= 5.
-4.8	=TRUNC(-4.8)	-4	Strips the decimal; truncates towards zero.
-4.8	=INT(-4.8)	-5	Rounds down to the next lowest integer away from zero.

The comparison with INT is crucial: for positive numbers, TRUNC(number) and INT(number) produce identical integers. However, for negative numbers, INT rounds down to the more negative integer, while TRUNC always moves towards zero.

Practical Application: Extracting Time and Date Components

In Excel, dates and times are stored as serial numbers where the integer represents the date and the decimal fraction represents the time of day (for instance, 45200.75 represents 6:00 PM on that specific date).

To extract only the pure date integer and strip out time stamps:

# Extract pure date from a DateTime serial in cell A2
=TRUNC(A2)

# Extract only the decimal time portion by subtracting the truncated integer
=A2 - TRUNC(A2)

Truncating to Tens, Hundreds, or Thousands

By supplying a negative num_digits argument, you can truncate large figures without rounding up to the nearest multiple:

# Truncate £1,489 to the nearest hundred without rounding
=TRUNC(1489, -2)   # Returns 1400

# Truncate £1,489 to the nearest ten
=TRUNC(1489, -1)   # Returns 1480

Using TRUNC ensures rigorous mathematical precision, clean currency conversions, and predictable decimal trimming across corporate reporting models.