This article explains how to use Excel MOD function that returns a remainder after the division of one number by another. In other words, it returns the leftover of two numbers after division.
Excel MOD function used to get the division remainder. For example, MOD (14, 5) returns 4 because five (5) goes into 14 twice, with a leftover of 4.
Tip: The function can also be used with other formulas to process “every nth” value.
MOD (number, divisor)
- number: The number to be divided.
- divisor: The number to divide by.
Examples: Excel MOD Function
The following formula in cell C3 returns 0 because number 9 divided by divisor value 3 without any remainder.
=MOD (9, 3)
In the following image, divisor value supplied in both positive and negative sign and MOD has returned the answer in the same sign as divisor.
Extract Time value from Date-Time Stamp
In this example, I will show you how can you use MOD function to return time value from the date-time stamp.
Excel stores dates & times as sequential numbers (date as integers and time as decimal fractions) so that we can easily use them for calculations as well.
The first example is given in above image, “10-Jan-2018 13:29” has an underline serial “43110.56237” supplied as number argument and one (1) as divisor value. Since all numbers are evenly divisible by 1, MOD returns the fractional part only.
Tip: MOD returns the answer in the same format as Date-Time stamp supplied to number argument. Use custom time format to change the output in the required time format.
Additional Notes: –
- #DIV/0! error occurs if the divisor supplied as 0.
- The Function always returns a result in the same sign as the divisor value.
- If any of the supplied arguments are non-numeric, MOD function returns the #VALUE! error.
Excel MOD Function: Video Tutorial
INT | ROUNDUP | ROUNDDOWN | ROUND