How to use the INT function

What is the INT function?

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

1. Introduction

What is an integer?

An integer is a whole number that can be positive, negative, or zero. Integers do not include fractions or decimal values. Some examples of integers are ...0, 2, -7, 365, -88.

The set of integers is represented mathematically by the symbol ℤ or Z. The integers are evenly spaced on the number line, with consecutive integers having an absolute difference of 1.

Integers have a fundamental role in mathematics and have applications across many fields, including counting, indexing, and as quantified measures. Many basic arithmetic operations like addition, subtraction, multiplication, and division (except division by zero) can be performed on integers while still resulting in integer values.

1. Syntax

INT(number)

number

Required. The number you want to convert to an integer.

Boolean values are converted into their equivalent integer. TRUE is 1 and FALSE is 0, see section 5 below for more.

Back to top

3. Example

The image above demonstrates the INT function, the arguments are in cells B3:B10 and the results are in cells D3:D10.

Formula in cell D3:

• Cell B3 contains 1.9 and the INT function removes the decimal part.
• Cell B4 contains numerical value 2 and the INT function returns 2 in cell D4.
• Cell B7 contains a text string : "A", the INT function returns a #VALUE! error.
• Cell B8 contains a text string : "#DIV/0!", the INT function returns a #DIV/0! error.
• Cell B9 contains boolean value TRUE, the INT function returns 1 which is the numerical equivalent.
• Cell B10 contains -6,9, the INT function returns -7. This shows that the INT function does not remove decimals if the number is negative, it simply rounds the number down to its nearest integer.

Back to top

4. Alternative

This example shows an alternative to the INT function. The formula in cell C3 removes the decimal part by subtracting a number based on the MOD function.

Formula in cell C3:

The MOD function returns the remainder after a number is divided by divisor.

Function syntax: MOD(number, divisor)

The MOD function returns the decimal part if the second argument is equal to 1.

4.1 Explaining formula

Step 1 - Calculate remainder

The MOD function returns the remainder after a number is divided by a divisor.

MOD(number, divisor)

MOD(B3,1)

becomes

MOD(1.9, 1)

and returns 0.9

Step 2 - Subtract the number

The minus sign lets you subtract numbers in an Excel formula.

B3-MOD(B3,1)

becomes

1.9 -0.9 equals 1.

Back to top

5. Convert boolean values to their numerical equivalents

The image above demonstrates how the INT function converts a boolean value to its corresponding number.

• TRUE - 1
• FALSE - 0 (zero)

This can be useful if an Excel function doesn't allow boolean values. Right now I can only think of the MMULT function that requires numerical values and not the boolean equivalents.

Formula in cell C3:

This can be used in the MMULT function or SUMPRODUCT function to convert boolean values to numerical equivalents. These functions require numerical values to work, however, there are more ways to convert boolean values:

TRUE + 0 equals 1.
FALSE + 0 equals 0 (zero).

TRUE * 1 equals 1.
FALSE * 1 equals 0 (zero).

The examples above demonstrates how to convert boolean values to their numerical equivalents using addition and multiplication.

Back to top

6. Split string and remove decimals

This example shows how to split a string containing numerical values based on a comma as a delimiting value.

The argument is in cell B3 and the result is an array in cell D3 that spills to cells below as far as needed. The string is: 1.2, -4.1, 9.9, -10.9

Formula in cell D3:

This formula works only in Excel 365 as the TEXTSPLIT function is a relative new function. The returned array is: {1;-5;9;-11}, these values are integers. No decimal values are left.

Explaining formula

Step 1 - Split values into an array

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3,,",")

becomes

TEXTSPLIT("1.2, -4.1, 9.9, -10.9",,",")

and returns

{"1.2";" -4.1";" 9.9";" -10.9"}.

Step 2 - Remove decimals

Note that the INT function also converts the text values to numbers automatically.

INT(TEXTSPLIT(B3,,","))

becomes

INT({"1.2"; " -4.1"; " 9.9"; " -10.9"})

and returns {1; -5; 9; -11}. The double quotes are gone indicating they are now numbers and not text.

Back to top

7. IF function

This formula checks if the value on the same row in column B is a text value and returns "Text" if so. If not then the formula returns the integer of the numerical value.

Formula in cell C3:

Cell range B3:B7 contains the arguments and cells C3:C7 contains the result.

• Cell B3 and B4 contains numerical values 1.9 and 3.4 respectively. The results are 1 and 3 displayed in cells C3 and C4.
• Cell B5 contains "A" which is a text string, the formula returns "Text" in cell C5.
• Cell B6 contains a boolean value and cell C6 returns the numerical equivalent.
• Cell B7 contains -6.9 and cell C7 returns -7.

7.1 Explaining formula

Step 1 - Check if the value is a text value

The ISTEXT function returns TRUE if the value is a text value.

ISTEXT(value)

ISTEXT(B3)

becomes

ISTEXT(1.9)

and returns FALSE. 1.9 is not a text value.

Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(ISTEXT(B3),"Text", INT(B3))

becomes

IF(FALSE,"Text", INT(B3))

Step 3 - Evaluate the INT function

INT(B3)

becomes

INT(1.9)

and returns 1.

Back to top

8. Split date from a date and time value

The INT function removes the decimals from a number which is useful while manipulating Excel dates. An Excel date and time value has two parts. The integer is the date and the decimal is the time.

Remove the decimal and the date is left, remove the integer and the time is left. This is demonstrated in the image above. Date 5/11/2025 1:45 PM corresponds to the numerical value 45788.57292. If we remove the decimals then the remaining part is the integer value which represents the date.

Formula in cell D3:

INT(45788.57292) returns 45788.

Back to top

9. Split time from a date and time value

This example shows how to extract time from an Excel date and time value using the INT function. The date and time value is specified in cell B3 and the result is in cell D3. The result contains only the time part of the value in cell B3.

Formula in cell D3:

The integer part of an Excel date and time value represents the date and the decimals part represents the time. The formula in cell D3 subtracts the original value with its integer part which results in only the decimal part.

Explaining formula

Step 1 - Remove decimals

INT(45788.572916667)

returns 45788.

Step 2 - Calculate decimals

B3-INT(B3)

becomes

45788.572916667 - 45788

and returns 0.572916667

Back to top

10. Extract the hour from a time value

The HOUR function lets you calculate the hour value based on an Excel date (and time value), however, you can perform the same calculation using the INT function.

An hour in Excel represents 1/24 since one day or 24 hours is equal to 1.

Formula in cell D3:

If we multiply the time value with 24 and the calculates the integer part using the INT function we get the hour from the source Excel time value specified in cell B3.

Explaining formula

Step 1 - Calculate hours

B3*24

becomes

0.572916666664241*24

and returns

13.7499999999418

Step 2 - Remove decimals

INT(B3*24)

becomes

INT(13.7499999999418)

and returns 13.

Back to top

 

Useful links

INT function - Microsoft
INT Function in Excel

12. Calculate the number of weeks between given dates

The image above demonstrates a formula that calculates the number of complete weeks between two dates. Cell range B3:B14 contains the start dates and cell range C3:C14 contains the end dates.

Formula in cell D3:

Copy cell D3 and paste to cells below as far as needed.

1.1 Explaining formula in cell D3

This formula works fine if the start is later than the end date, however, you get a minus sign before the number.

If you want to remove the minus sign simply use the ABS function to remove it, the formula then becomes:

Step 1 - Subtract dates

C3-B3 becomes 35067 - 35685 equals 618 days.

Step 2 - Divide with 7

There are seven days in a week so we need to divide the result with 7.

(C3-B3)/7 becomes 618/7 equals 88.28571429.

Step 3 - Round the number down

The ROUNDDOWN function rounds the number down.
ROUNDDOWN(number, num_digits)

ROUNDDOWN((C3-B3)/7) becomes ROUNDDOWN(88.28571429) and returns 88.

Back to top

13. Calculate the number of weeks and days between given dates

This formula returns the total number of weeks and days between a given start and end date.

Formula in cell D3:

2.1 Explaining formula

Step 1 - Calculate days between dates in cells C3 and B3

The minus sign lets you subtract numbers in an Excel formula.

C3-B3 becomes 45339-44522 and returns 817.

Step 2 - Calculate weeks

The division character lets you divide numbers in an Excel formula. The parentheses let you control the order of operation, we want to subtract before we divide.

(C3-B3)/7 becomes 817/7 and returns approx. 116.71

Step 3 - Remove decimals

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.
INT(number)

INT((C3-B3)/7) becomes INT(116.71) and returns 116

Step 4 - Concatenate number and string

The ampersand character lets you concatenate values in an Excel formula. Use double quotes with text values to avoid a formula #NAME error.

INT((C3-B3)/7)&" weeks " becomes 116&" weeks" and returns 116 weeks.

Step 5 - Calculate the remainder

The MOD function returns the remainder after a number is divided by a divisor.
MOD(number, divisor)

MOD(C3-B3, 7) becomes MOD(817, 7) and returns 5.

Step 6 - Concatenate numbers and text values

The ampersand character concatenates values in an Excel formula.

INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" days" becomes 116&" weeks "&5&" days" and returns 116 weeks 5 days.

Back to top

14. Calculate the number of weeks and days between given dates - dynamic text values

This formula works only in Excel 365, it calculates weeks and days between a given start and end date. It is also dynamic meaning if the result is a whole week the number of days is left out from the output.

Excel 365 formula in cell D3:

Explaining formula

Step 1 - First argument expression

The SWITCH function returns a given value determined by an expression and a list of values. The SWITCH function is made for exact matches, however, there is a workaround to use larger than and smaller than characters.

If any of the value arguments returns a value equal to the expression argument the corresponding result argument is returned.

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

TRUE and FALSE are boolean values, they are often the result of a logical test. I am going to use TRUE in this expression argument.

Step 2 - Second argument value1

The following formula calculates the remaining days after we subtract two Excel dates and then divide by seven, there are seven days in one week.

The MOD function returns the remainder after a number is divided by a divisor.
MOD(number, divisor)

MOD(C3-B3,7)=0 becomes 0=0 and returns TRUE. This value matches the expression argument, the formula will now return the result argument.

Step 3 - Third argument result1

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.
INT(number)

INT((C3-B3)/7)&" weeks " returns "116 weeks" in cell D3.

There are two more value arguments:
MOD(C3-B3,7)=1 adds day to the result. The remainder is one.
MOD(C3-B3,7)>1 adds days to the result. The remainder is more than one.

Step 4 - Shorten the formula

The LET function allows you to name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

SWITCH(TRUE(),MOD(C3-B3,7)=0,INT((C3-B3)/7)&" weeks ",MOD(C3-B3,7)=1,INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" day",MOD(C3-B3,7)>1,INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" days")

I have named intermediate calculations if they are repeated in the formula, this creates a shorter formula.

y - C3-B3
x - MOD(y,7)
z - INT((y)/7)
q - " weeks "

LET(y,C3-B3,x,MOD(y,7),z,INT((y)/7),q," weeks ",SWITCH(TRUE(),x=0,z&q,x=1,z&q&x&" day",x>1,z&q&x&" days"))

15. Function not working

The INT function returns

• #VALUE! error if you use a non-numeric input value.
• #NAME? error if you misspell the function name.
• propagates errors, meaning that if the input contains an error (e.g., #VALUE!, #REF!), the function will return the same error.

15.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

15.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue.

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B7 converted to hard-coded value using the F9 key. The INT function requires numerical values which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

15.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

'INT' function examples

Functions in 'Math and trigonometry' category

The INT function function is one of 62 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment