What it does
=MOD(125,12) returns 5: 12 goes into 125 ten times and 5 is left. For the whole part of the division use QUOTIENT or INT(125/12); together they are the basis of box, pallet and pack calculations.
The remainder takes the sign of the divisor: =MOD(-7,3) returns 2 and =MOD(7,-3) returns -2. That is useful with times: on a shift that passes midnight the end time looks smaller than the start, and =MOD(End-Start,1) makes up the negative difference to a full day and gives the right duration.
Sequence numbers make regular patterns easy: =MOD(A2,3)=0 catches every third record and =MOD(A2,2)=0 the even numbers. In conditional formatting, =MOD(ROW(),2)=0 shades alternate rows. ISEVEN and ISODD tell you directly whether a number is even or odd.
Syntax
=MOD(number, divisor)=MOD(sayı; bölen)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number | Required | The number, cell or formula to divide. |
divisor | Required | The number to divide by. It cannot be 0; the result takes its sign. |
Examples
1Units left over after packing boxes
QUOTIENT gives the number of full boxes and MOD the number of units left over.
=QUOTIENT(B2,C2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Qty | Per Box | Full Boxes | Left Over |
| 2 | Grounded socket | 125 | 12 | 10 | 5 |
| 3 | Circuit breaker | 300 | 25 | 12 | 0 |
| 4 | Light switch | 98 | 10 | 9 | 8 |
| 5 | Cable duct | 61 | 20 | 3 | 1 |
- D2 (filled down to D5)
=QUOTIENT(B2,C2) - E2 (filled down to E5)
=MOD(B2,C2)
125 sockets fill 10 boxes with 5 left over. The switches leave 8 and the cable ducts 1.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Length of a shift that passes midnight
On a night shift the end time is smaller than the start, so a plain subtraction gives a negative result. MOD takes the difference relative to 1 (one day) and makes up the negative result; day shifts are unaffected.
=MOD(C2-B2,1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Start | End | Duration |
| 2 | Emma | 08:00 | 16:30 | 08:30 |
| 3 | Liam | 22:00 | 06:30 | 08:30 |
| 4 | Olivia | 16:00 | 00:30 | 08:30 |
| 5 | Noah | 23:30 | 07:15 | 07:45 |
- D2 (filled down to D5)
=MOD(C2-B2,1)
Liam's 22:00-06:30 shift lasts 08:30; Noah's lasts 07:45. Emma's day shift comes out right too: 08:30.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Flag every third order for inspection
Quality control inspects one order in three. When the sequence number divides by 3 the remainder is 0, and the row is flagged.
=IF(MOD(A2,3)=0,"Inspect","-")| A | B | C | |
|---|---|---|---|
| 1 | Seq | Order | Inspect |
| 2 | 1 | S-1001 | - |
| 3 | 2 | S-1002 | - |
| 4 | 3 | S-1003 | Inspect |
| 5 | 4 | S-1004 | - |
| 6 | 5 | S-1005 | - |
| 7 | 6 | S-1006 | Inspect |
- C2 (filled down to C7)
=IF(MOD(A2,3)=0,"Inspect","-")
Orders 3 and 6 show Inspect; the others stay at -. Use 5 instead of 3 to pick every fifth order.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #DIV/0!
Cause: divisor is 0 or an empty cell.
Fix: Check the divisor; for cells that may be empty, use
=IF(B2=0,"",MOD(A2,B2)).- Comparison fails
Cause: With decimals, the remainder can be stored with a tiny difference even though it looks right;
=MOD(5.3,1)=0.3can return FALSE.Fix: Round before comparing:
=ROUND(MOD(5.3,1),2)=0.3.
Good to know
- For the length of a shift that passes midnight, use
=MOD(C2-B2,1)and give the result a time format. - To get the decimal part of a number,
=MOD(A2,1)is enough: it returns 0.25 for 7.25.
In Nexell
Get Nexell to do it from a sentence
Add a column that calculates the hours worked from start and end times, including shifts that pass midnight
Convert to tableBring your shift list into the tool and type this. Nexell builds the duration column with real Excel formulas and shows it in the preview first; in the downloaded file, the duration follows any change in the times.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find odd and even numbers with MOD?
=MOD(A2,2) returns 0 for even numbers and 1 for odd ones. For TRUE/FALSE directly, use ISEVEN or ISODD.
What is the difference between MOD and QUOTIENT?
QUOTIENT returns the whole part of a division and MOD the remainder. Dividing 125 by 12, QUOTIENT returns 10 and MOD 5; 10 × 12 + 5 = 125.