Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: INT function

INT function: Description, Usage, Syntax, Examples and Explanation

What is INT function in Excel? INT function is one of the Math and Trig functions in Microsoft Excel that rounds a number down to the nearest integer. Syntax of INT function INT(number) The INT function syntax has the following arguments: Number: The real number you want to round down to an integer. Example of INT function Steps to follow: 1. Open…

Convert inches to feet and inches in Excel

To convert a measurement in inches to inches and feet (i.e. 53 to 4′ 5″) you can use a formula based on the INT and MOD functions. See illustration below: Formula =INT(A1/12)&”‘ “&MOD(A1)&”””” Explanation In the example shown, the formula in D5 is: =INT(B5/12)&”‘ “&MOD(B5,12)&”””” How this formula works This formula converts a numeric value in inches to text representing…

Cash denomination calculator in Excel

To calculate required currency denominations, given a specific amount, you can build a currency calculation table as shown in the example. This solution uses the INT and SUMPRODUCT functions. Formula =INT((amount-SUMPRODUCT(denoms,counts))/currentdenom) Explanation In the example show, the formula in D5 is: =INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4) How this formula works To start off, the formula in C5 is: =INT($B5/C$4) This formula divides the amount in column B…

Get age from birthday in Excel

This tutorial shows how to get age from birthday in Excel using example below. If you need to calculate a person’s age from their birth date, you can do so with the YEARFRAC, INT, and TODAY functions. As stated in the formula below, birthdate is the person’s birthday with year, and TODAY supplies the date on which to calculate age.…

Extract date from a date and time in Excel

This tutorial show how to Extract date from a date and time in Excel using the example below. If you have dates with time values and you want to extract only the date portion, you can use a formula that uses the INT function. Note: Excel handles dates and time using a scheme in which dates are serial numbers and…

Get days, hours, and minutes between dates in Excel

To calculate and display the days, hours, and minutes between two dates, you can use the TEXT function with a little help from the INT function. Alternatively, you can adapt the formula using SUMPRODUCT. Formula =INT(end-start)&” days “&TEXT(end-start,”h”” hrs “”m”” mins “””) Explanation In the example shown, the formula in D5 is: =INT(C5-B5)&” days “&TEXT(C5-B5,”h”” hrs “”m”” mins “””) How this…

Calculate years between dates in Excel

If you want to calculated the number of years between two dates,  then this tutorials is for you. See example below; To calculated the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. Here are a few examples of the results that YEARFRAC…

Round a number to n significant digits in Excel

This tutorials shows how to Round a number to n significant digits in Excel. If you need to round a number to a given (variable) number of specified digits or figures, you can do so with an elegant formula that uses the ROUND and LOG10 functions. Formula =ROUND(number,digits-(1+INT(LOG10(ABS(number))))) Explanation In the example shown, the formula in D6 is as follows:…

Use of INT and TRUNC function in Excel

This example illustrates two functions to chop off decimals in Excel. The INT and the TRUNC function. Int The INT (Integer) function rounds a number down to the nearest integer. Conclusion: the INT function only chops off decimals from positive numbers. Trunc Truncate a number to a given precision. The TRUNC (Truncate) function always chops off decimals. The TRUNC function can keep any number of decimals and…