MOD

Description

Use this function to return the remainder of two numbers after division. Supported in Chains.

The result of MOD carries the same sign as the divisor. If divisor is positive, the result from MOD is positive, if divisor is negative, the result from MOD is negative.

Syntax

MOD(number, divisor)

Inputs

This function has the following arguments:

Name	Required	Description	Valid input
`number`	Yes	The number to be divided.	A cell reference, a number, or a formula which results in either of these.
`divisor`	Yes	The value to divide the number by.	A cell reference, a number, or a formula which results in either of these.

Examples

Sample data

Table 1

	A	B	C	D	E
1	Date	Cookies Sold	Cookies per package	Surplus	Packages	Hidden
2	12/1/2024	45	4	1	12	FALSE
3	12/2/2024	34	4	2	9	FALSE
4	12/3/2024	35	6	5	6	TRUE
5	12/4/2024	40	4	0	10	FALSE
6	12/5/2024	24	4	0	6	FALSE
7	12/6/2024	34	4	2	9	TRUE
8	12/7/2024	36	6	0	6	FALSE
9	12/8/2024	41	4	1	11	FALSE
10	12/9/2024	25	6	1	5	TRUE
11	12/10/2024	37	4	1	10	FALSE
12	12/11/2024	33	4	1	9	FALSE
13	12/12/2024	15	4	3	4	TRUE
14	12/13/2024	23	4	3	6	FALSE
15	12/14/2024	43	4	3	11	FALSE

Table 2

	A	B	C	D
1	Date	Hours worked	Hours	Minutes
2	12/2/2024	7.5	7	30
3	12/3/2024	6.75	6	45

Table 3

	A	B
1	Date-Time	Standard time
2	12/2/24 10:40 AM	10:40 AM
3	12/4/24 10:14 AM	10:14:10 AM
4	12/5/24 10:37 AM	10:37:20 AM
5	12/6/24 9:50 AM	9:50:00 AM

Sample formulas

Use case	Formula	Explanation and Result
Calculate the remainder after a division (D2), the integer result (E2), and the total number of packages needed (F2).	In D2: `=MOD(B2,C2)` In E2: `=INT(B2/C2)` In F2: `=ROUNDUP(B2/C2, 0)`	Using the data in Table 1, this formula works in the following manner: `=MOD(B2,C2)`: (45, 4) Returns the remainder 1. (Result in D2). `=INT` `(B2/C2)`: (45/4) = 11.25. Returns the integer part, 11. (Result in E2) `=ROUNDUP(B2/C2, 0)`: (45/4) = 11.25. Rounds to the next integer, 12. (Result in F2).
Split a decimal-based hour value into hours and minutes.	In C2: `=INT(B2)` In D2: `=MOD(B2,1)*60`	Using the data in Table 2, this formula works in the following manner: `=INT(B2)`: As B2 = 7.5, this returns the integer part, 7. (Result in C2). `=MOD(B2,1)60`: As B2 = 7.5, `MOD(7.5, 1)` returns the decimal remainder, 0.5. 0.560 = 30. (Result in D2).
Extract the time (as text) from a cell containing a date and time.	`=TEXT(MOD(A2,1),"h:mm:ss AM/PM")`	Using the data in Table 3, this formula works in the following manner: `MOD(A2,1)`: Divides the date/time value in A2 by 1 and returns the remainder. The integer part of a date/time value is the date; the decimal part is the time. This isolates the time (0.4444...). `TEXT(...)`: Formats the resulting decimal (0.4444...) into a human-readable time string based on the format "h:mm:ss AM/PM". For this data set, the formula returns 10:40:00 AM.
Find the last Friday of a given month.	`=TEXT(EOMONTH(A2,0)-MOD(EOMONTH(A2,0)-6,7),"dd/mm/yyyy")`	Using the data in Table 1, this formula works in the following manner: `EOMONTH(A2, 0)`: Finds the last day of the month for the date in A2 (12/1/2024), which is 12/31/2024. `EOMONTH(A2,0)-6`: Subtracts 6 days (to find the Friday of that week, assuming 1=Sunday...6=Friday). `MOD(..., 7)`: Calculates the remainder when divided by 7, finding the day-of-week offset. `EOMONTH(...) - MOD(...)`: Subtracts the offset from the last day of the month to find the date of the last Friday. `TEXT(...)`: Formats the resulting date serial number as "dd/mm/yyyy". For this data set, the formula returns 27/12/2024.

Notes

MOD returns the remainder after division. For example, MOD(3,2) returns 1, because 2 goes into 3 once, with a remainder of 1.
Wildcards don't work with this function.

Tips

Avoid "#DIV/0"Errors: Use IF to check for a zero divisor: =IF(B1=0, "Error", MOD(A1, B1)).

Related functions

ABS
EXP
LN
N
POWER
PRODUCT
SIGN
SQRT
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT

Support

Community

Workiva Support Center

MOD