How to Use the TRUNC Function in Excel

 

How to Use the TRUNC Function

Let’s look at examples of the TRUNC function with some sample data. The below example uses the following TRUNC function.

=TRUNC(A2)

If you do not specify how many digits to truncate, all decimal places will be removed.

You can see with the value in cell A2 that the TRUNC function does not apply any rounding. It simply truncates the number to 411.

Let’s see another example. This time we will reduce the values to two decimal places.

=TRUNC(A2,2)

The TRUNC function will not display extra decimals if you ask it to show more than you have.

Take the following example, and let’s truncate it to two decimal places.

=TRUNC(A2,2)

The value in cell A4 is reduced to two decimal places, but the values in A2 and A3 stay as they are because they have less than two decimal places already.

If you want to display the two decimals, the cells will need to be formatted to be forced to show them.

Remove the Time from a Date-Time Stamp

A useful example of TRUNC is to remove the time from a date and time stamp in Excel.

Imagine having the following date and time stamps, but we just want the date in a column for analysis.

The following formula will work to remove the time.

=TRUNC(A2)

Although the time is removed, the resulting cells will still need to be formatted as a date only.

Use TRUNC to Shorten Numbers

This is a rare technique, but it is worth knowing that the TRUNC function will also accept negative numbers for the digits argument. When you use a negative number, the formula truncates the numbers to the left of the decimal point. However, it does not change the number of digits. It will replace them with zeroes.

Let’s look at the following example.

=TRUNC(A2,-1)

You can see in each example that zero was used to replace the number that was removed from the left of the decimal point.