January 21, 2021

How to use the TRUNC Function in Excel

There are a wide range of methods in Excel to get rid of decimal points and shortening selection values. In this article, we demonstrate how to use the TRUNC operate and what tends to make it various from other tactics.

What Is the TRUNC Function?

The TRUNC perform truncates a quantity to a specified amount of decimal destinations. The vital aspect that tends to make TRUNC distinct from other capabilities that take away decimal spots is that the TRUNC functionality does not round values. If you use TRUNC to get rid of all the decimals from the worth 4.68, the final result is 4.

The TRUNC perform calls for two items of details:

=TRUNC(selection, [digits])

The range is the worth you want to truncate. Digits are the variety of numerals you want to truncate the price to. The digits portion is optional, and if not answered, TRUNC will take out all decimal places.

How to Use the TRUNC Purpose

Let us glimpse at illustrations of the TRUNC function with some sample knowledge. The below illustration utilizes the adhering to TRUNC perform.


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

First TRUNC function example

You can see with the benefit in cell A2 that the TRUNC perform does not use any rounding. It merely truncates the variety to 411.

Let us see another illustration. This time we will lessen the values to two decimal destinations.


TRUNC function to two decimal places

The TRUNC operate will not display screen further decimals if you inquire it to present more than you have.

Consider the next illustration, and let’s truncate it to two decimal sites.


No extra decimals shown by TRUNC

The price in mobile A4 is minimized to two decimal areas, but the values in A2 and A3 continue to be as they are since they have significantly less than two decimal sites previously.

If you want to exhibit the two decimals, the cells will need to have to be formatted to be compelled to show them.

Remove the Time from a Date-Time Stamp

A practical case in point of TRUNC is to remove the time from a day and time stamp in Excel.

Consider possessing the next date and time stamps, but we just want the day in a column for analysis.

Date and time sample data

The adhering to system will operate to get rid of the time.


Time removed from a date in Excel

Even though the time is eradicated, the ensuing cells will nevertheless require to be formatted as a day only.

Use TRUNC to Shorten Quantities

This is a unusual approach, but it is worthy of being aware of that the TRUNC function will also acknowledge adverse numbers for the digits argument. When you use a negative quantity, the method truncates the numbers to the still left of the decimal point. Having said that, it does not alter the range of digits. It will change them with zeroes.

Let us look at the pursuing case in point.


Entering minus digits for the second argument

You can see in each and every example that zero was utilized to replace the quantity that was eradicated from the left of the decimal stage.

There are many strategies in Excel to take out decimal places, having said that, most of these will use a rounding of some character. The toughness of the TRUNC functionality is that it does not spherical values and just shortens them to the specified decimal area volume.

Leave a Reply

Your email address will not be published. Required fields are marked *