What it does
TRUNC cuts rather than rounds: =TRUNC(12.789) gives 12 and =TRUNC(12.789,2) gives 12.78. If you leave out the digits, 0 is used. A negative digits value cuts to the left of the decimal point: =TRUNC(4876,-2) is 4800.
With negative numbers it differs from INT: TRUNC cuts towards zero (-7 for -7.5), while INT goes down to the next lower whole number (-8). For positive numbers both give the same result. ROUNDDOWN also matches TRUNC; it simply requires the digits argument.
In Excel, the whole-number part of a date-time value is the date, so =TRUNC(A2) strips the time from a time stamp and leaves only the date. To round to the nearest value use ROUND; to round to a multiple use MROUND.
Syntax
=TRUNC(number, [num_digits])=NSAT(sayı; [basamak])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number | Required | The number to cut, or a formula that produces it. |
num_digits | Optional | The number of decimal digits to keep; 0 if omitted. A negative value cuts to the left of the decimal point. |
Examples
1Cut the price excluding VAT to two decimals
Dividing the price including VAT by 1.2 gives the price excluding 20% VAT, which is often a long decimal. Column D cuts it at two decimals; column E rounds it with ROUND for comparison.
=B2/1.2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price incl. VAT | Excl. VAT (Raw) | Truncated | Rounded |
| 2 | Flask | 299.90 | 249.9167 | 249.91 | 249.92 |
| 3 | Mug | 74.90 | 62.4167 | 62.41 | 62.42 |
| 4 | Bottle | 549.00 | 457.5000 | 457.50 | 457.50 |
| 5 | Tea Set | 1,199.00 | 999.1667 | 999.16 | 999.17 |
- C2 (filled down to C5)
=B2/1.2 - D2 (filled down to D5)
=TRUNC(C2,2) - E2 (filled down to E5)
=ROUND(C2,2)
The flask costs 249.9167 excluding VAT; TRUNC drops everything after the second decimal and gives 249.91, while ROUND gives 249.92. The bottle's result has nothing beyond two decimals, so both columns agree: 457.50.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2TRUNC versus INT with negative numbers
Warehouse count differences are recorded in kilograms; a negative value means a shortage. Column B takes the whole part with TRUNC and column C with INT.
=TRUNC(A2)| A | B | C | |
|---|---|---|---|
| 1 | Count Difference (kg) | TRUNC | INT |
| 2 | 7.50 | 7 | 7 |
| 3 | -7.50 | -7 | -8 |
| 4 | 12.99 | 12 | 12 |
| 5 | -0.40 | 0 | -1 |
- B2 (filled down to B5)
=TRUNC(A2) - C2 (filled down to C5)
=INT(A2)
For positive values the two functions agree: 7 on the first row. For -7.50, TRUNC gives -7 while INT gives -8; for -0.40, TRUNC returns 0 and INT -1.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result not rounded
Cause: 12.999 gave 12 instead of the expected 13; TRUNC does not round, it only cuts.
Fix: Use ROUND for the nearest value.
- Different result for negatives
Cause: For -7.5, TRUNC gives -7 while INT gives -8; the two functions were mixed up.
Fix: Use TRUNC to cut towards zero and INT for the next lower whole number.
- #VALUE!
Cause: number or num_digits is text that cannot be read as a number.
Fix: Make sure the cells really hold numbers.
Good to know
- To get only the decimal part of a number:
=B2-TRUNC(B2); for 12.75 it gives 0.75. - Hiding decimals with a cell format does not change the value; totals still include the hidden part. To calculate with the cut value, you need TRUNC.
In Nexell
Get Nexell to do it from a sentence
Add a column to the price list that works out the price excluding VAT from the price including VAT and cuts it to two decimals without rounding
Convert to tableBring your price list into the tool and type this. Nexell adds the column with real Excel formulas and shows it in the preview; in the downloaded file, the amount excluding VAT changes when the price does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between TRUNC and INT?
For positive numbers they give the same result; both drop the decimal part. For negative numbers TRUNC cuts towards zero (-7.5 → -7), while INT goes down to the next lower whole number (-7.5 → -8). TRUNC can also cut at a chosen digit.