Special Functions Reference

Special Functions Reference

Article ID	RC-CALC-01 — Special Functions Reference
Domain	Calculations & Special Functions
Applies To	All REDCap project types; functions are available wherever REDCap logic or calculations are used
Requires	Any supported version
Verified Against	REDCap v17.4.1 (Standard) / v17.3.7 (LTS) — changelog review; page not re-captured. Blank handling in `sum()` and `+` tested on v16.0.49 (LTS), 2026-09
Prerequisite	RC-BL-01 — Branching Logic: Overview & Scope
Version	1.3
Last Updated	2026-09
Author	See KB-SOURCE-ATTESTATION.md
Related Topics	RC-BL-02 — Branching Logic: Syntax & Atomic Statements; RC-AT-09 — Action Tags: @CALCTEXT & @CALCDATE — Calculations; RC-PIPE-03 — Smart Variables Overview
Synonyms	list of redcap special functions; how do i use datediff in redcap; calculate the difference between two dates; redcap functions for logic and calculations; round sum and if functions in calc fields; what functions can i use in calculated fields; text and date manipulation functions; redcap built-in function reference

1. Overview¶

REDCap provides a library of built-in functions — called Special Functions — that can be used wherever logic or calculations are written in the platform. This includes branching logic, calculated fields, report filters, survey invitations, alerts, and the Data Quality module. Functions perform operations that go beyond simple comparisons: calculating the difference between two dates, extracting parts of a date, rounding numbers, manipulating text strings, and applying conditional logic. This article is a complete reference for all available Special Functions as of REDCap 16.1.3.

2. Key Concepts & Definitions¶

Special Function

A built-in operation in REDCap's logic and calculation syntax that takes one or more inputs (called parameters) and returns a value. Functions are identified by their name followed by parentheses containing the parameters — e.g., datediff([date1], [date2], 'd').

Parameter

A value passed into a function that the function operates on. Parameters are separated by commas inside the parentheses. Some parameters are required; others are optional and have defaults when omitted.

Return Value

The value a function produces. Depending on the function, this may be a number, a date, a text string, or a boolean (true/false). In branching logic, functions that return a number are typically used inside a comparison statement (e.g., datediff([dob], 'today', 'y') >= 18). In calculated fields, the function's return value is stored directly.

Boolean

A value that is either true (1) or false (0). Several functions — primarily text and missing data functions — return a boolean. In branching logic, a function returning true will satisfy the condition; returning false will not.

Haystack / Needle

Terminology REDCap uses in text functions. The "haystack" is the larger text being searched; the "needle" is the text being looked for within it.

Missing Data Code

A project-defined code indicating that a value is intentionally absent (e.g., "UNK" for unknown). Missing Data Codes are configured in Project Setup under Additional Customizations. Several functions specifically handle these codes.

3. Where Special Functions Can Be Used¶

Special Functions work in any location in REDCap where logic or calculations are written:

Context	Typical Use
Branching Logic	Use a function's return value as part of a show/hide condition
Calculated Fields	Use a function as the entire calculation expression
Report Filters	Filter records based on a function's result
Survey Invitations	Control invitation timing or eligibility using date/logic functions
Alerts & Notifications	Trigger or suppress alerts based on a function's result
Data Quality Module	Define custom data quality rules using function logic
Action Tags (@CALCTEXT, @CALCDATE)	Drive dynamic field population

Note on piping: You cannot pipe the choice label of a multiple choice field into a Special Function. Using the :label modifier (e.g., [my_field:label]) to pass a display label as a function parameter will not work. Use the raw field variable instead.

4. Conditional Function¶

4.1 if()¶

Syntax: if(CONDITION, value_if_true, value_if_false)

Evaluates a condition and returns one of two values depending on whether the condition is true or false. This is REDCap's equivalent of an if/then/else statement.

If CONDITION evaluates as true, the first value is returned.
If CONDITION evaluates as false, the second value is returned.

Example: if([weight] > 100, 44, 11) returns 44 if weight is greater than 100, otherwise returns 11.

if() can be nested inside other functions or used as a parameter within another if() to handle multiple conditions.

5. Date and DateTime Functions¶

5.1 datediff()¶

Syntax: datediff([date1], [date2], "units", returnSignedValue)

Calculates the difference between two dates or datetimes.

Units options: - 'y' — years (1 year = 365.2425 days) - 'M' — months (1 month = 30.44 days) - 'd' — days - 'h' — hours - 'm' — minutes - 's' — seconds

The returnSignedValue parameter must be true or false (default: false). When false, the result is always positive regardless of which date is larger. When true, the result is negative if date2 is earlier than date1.

You may use the literal 'today' or 'now' as either date parameter.

Common use: datediff([date1], 'today', 'd') — number of days between a field's date and today.

5.2 age_at_date()¶

Syntax: age_at_date([date_of_birth], [other_date], returnDecimal)

Calculates a person's age in years using their date of birth and another specified date. Returns an integer by default. To get a decimal value (e.g., 0.24 years for a newborn), add true as the third parameter. If datetime fields are provided, the time component is ignored — only the date portion is used.

Critical — version caveat (17.0.6 to 17.2.0 Standard). The date-related special functions were unreliable across five consecutive releases, and the failures were silent: the calculation produced nothing rather than an error.

Symptom Fixed in

age_at_date(), year(), month() and day() failed "in many cases" 17.0.7

Same functions failed when referencing a field on another instrument where that instrument is repeating 17.0.8

age_at_date() failed on a calc field whenever the third parameter was omitted — directly contradicting the "optional third parameter" guidance above 17.1.2

Date functions failed on a calc field where the referenced field was in another event and not in YMD format 17.1.3

Further date-function failures on calc fields 17.2.0

On 17.0.6–17.1.1, pass the third parameter explicitly as a workaround. Treat age_at_date() as requiring 17.2.0 or later for full correctness. If age calculations were populated on an affected version, they may be blank or absent rather than wrong — check before relying on them.

5.3 dayoftheweek()¶

Syntax: dayoftheweek([date])

Returns an integer corresponding to the day of the week for a given date or datetime: 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday.

You may use 'today' or 'now' as the date parameter.

5.4 year(), month(), day()¶

Extract individual components from a date or datetime field:

Function	Returns	Example
`year([date])`	The year as a 4-digit number	`year([dob])` → 1985
`month([date])`	The month as a number (1–12)	`month([visit_datetime])` → 3
`day([date])`	The day of the month (1–31)	`day([visit_date])` → 15

You may use 'now' or 'today' (in quotes) instead of a field variable in any of these functions.

5.5 Comparing Against Blank Values — Version Caveats¶

Comparisons involving blank or null values have been a recurring source of silently wrong results — the calculation returns a plausible number rather than failing.

Version caveat (below 15.0.28 Standard): Calculations comparing a field to a number or another field using < or <= where the referenced value is blank/null could return an incorrect result. REDCap's own example: if([v1] < 5, [v1], 0).

Behavior change (16.0.41 LTS, July 2026): REDCap changed how special functions handle fields with a blank value (changelog: "Using various REDCap special functions in calculations in certain ways with fields that have a blank value could result in incorrect results", ticket #281007b; it followed a concat() fix for the same issue in 16.0.39, ticket #281007). This is a permanent change, not a bug. The most visible effect: sum() of inputs that are all blank now returns blank, where it used to return 0. Projects that relied on the old result, including workarounds written to tell an all-blank sum() apart from a genuine 0, can see stored calculated values change from 0 to blank the next time the record is recalculated (form save, import, or rule H). See §6.3.

Version caveat (16.0.42 and 16.0.43 LTS only): A regression introduced in 16.0.42 made various special functions used with fields compared to a blank value — [field1]="" or [field2]<>"" — return incorrect results. 16.0.43 fixed the ="" case (ticket #281847) and 16.0.44 fixed the <>"" case (ticket #281847b). Versions before 16.0.42 were not affected by this regression, and 16.0.44 and later are fixed.

Blank-comparison patterns are common in branching logic and calculated fields, so this is worth checking if historical calculated values on an older instance look wrong in ways nobody can explain. See also RC-BL-02 — Branching Logic: Syntax & Atomic Statements.

6. Numeric Functions¶

6.1 Rounding¶

Function	Behavior	Example
`round(number, decimals)`	Rounds to nearest	`round(14.384, 1)` → 14.4
`roundup(number, decimals)`	Always rounds up	`roundup(14.384, 1)` → 14.4
`rounddown(number, decimals)`	Always rounds down	`rounddown(14.384, 1)` → 14.3

If the decimals parameter is omitted, it defaults to 0 (rounding to a whole number).

6.2 Mathematical Operations¶

Function	Description	Example
`sqrt(number)`	Square root	`sqrt([height])`
`(number)^(exponent)`	Exponentiation	`(4)^(3)` → 64; `([weight]+43)^(2)`
`abs(number)`	Absolute value	`abs(-7.1)` → 7.1
`exponential(number)`	e raised to the power of a number	`exponential(1)` → 2.718...
`log(number, base)`	Logarithm	`log(100, 10)` → 2; omit base for natural log
`mod(dividend, divisor)`	Remainder of integer division	`mod(10, 4)` → 2
`random(min, max)`	Random integer between `min` and `max`, inclusive of both. Requires REDCap 17.0.3+	`random(1, 6)` → an integer from 1 to 6

Important — random() is not a randomization tool. It returns a fresh value each time the calculation is evaluated, and calculated fields re-evaluate whenever their inputs change or the form is re-saved. It is unsuitable for treatment allocation or anything requiring a stable, auditable assignment — use the Randomization module for that, see RC-RAND-01 — Randomization Concepts & Terminology.

Important: For exponents, the surrounding parentheses on both the base and the exponent are required. 4^3 will not work — it must be (4)^(3).

6.3 Aggregate Functions¶

These functions operate on a set of two or more values. Blank values are always ignored and excluded from the calculation.

Function	Description	Example
`min(n1, n2, ...)`	Lowest value in the set	`min([score1], [score2], [score3])`
`max(n1, n2, ...)`	Highest value in the set	`max([score1], [score2], [score3])`
`mean(n1, n2, ...)`	Average of the set	`mean([score1], [score2], [score3])`
`median(n1, n2, ...)`	Median of the set	`median([score1], [score2], [score3])`
`sum(n1, n2, ...)`	Sum of the set	`sum([score1], [score2], [score3])`
`stdev(n1, n2, ...)`	Standard deviation of the set	`stdev([score1], [score2], [score3])`

There is no limit to the number of values that can be passed into these functions.

sum() vs. the + operator: sum() skips blank values and totals the rest. If every input is blank, sum() returns blank (since 16.0.41 LTS; earlier versions returned 0). The + operator treats blank values as 0: [a]+[b] returns the filled value when one input is blank, and 0 when both are blank. Neither one waits for all inputs to be filled in. If a total must stay blank until every input has a value, test for blanks explicitly: if(isblankormissingcode([var1]) or isblankormissingcode([var2]) or isblankormissingcode([var3]), "", [var1]+[var2]+[var3])

Tested on Yale 16.0.49 LTS (2026-09-28) on data entry, data import and Data Quality rule H, with identical results in all three. Older documentation, including earlier versions of this article, said + returns blank if any operand is blank. That does not hold on current versions.

6.4 Type-Checking Functions¶

Function	Returns true if...
`isnumber(value)`	The value is an integer or floating-point decimal
`isinteger(value)`	The value is a whole number without decimals

7. Missing Data Code Functions¶

These functions are only meaningful in projects where Missing Data Codes have been defined (Project Setup → Additional Customizations).

Function	Returns true if...
`isblankormissingcode(value)`	The field is blank/null OR the value is any Missing Data Code
`isblanknotmissingcode(value)`	The field is blank/null AND the value is NOT a Missing Data Code
`ismissingcode(value)`	The value is any Missing Data Code
`hasmissingcode(value, 'codes')`	The value is a Missing Data Code AND matches one of the codes listed (comma-delimited string in the second parameter)

Example: hasmissingcode([age], 'NASK,NI,UNK') returns true if [age] is one of those three specific codes.

8. Text Functions¶

Important constraint: Text functions should not be used on date or datetime fields that use MDY or DMY date formatting. They work correctly on date fields using YMD formatting.

8.1 Searching Within Text¶

Function	Returns	Example
`contains(haystack, needle)`	true if needle is found anywhere in haystack (case-insensitive)	`contains([name], "taylor")`
`not_contain(haystack, needle)`	true if needle is NOT found in haystack (case-insensitive)	`not_contain([name], "smith")`
`starts_with(haystack, needle)`	true if haystack begins with needle (case-insensitive)	`starts_with([name], "rob")`
`ends_with(haystack, needle)`	true if haystack ends with needle (case-insensitive)	`ends_with([name], "taylor")`
`find(needle, haystack)`	Position of needle in haystack (1-based); returns 0 if not found	`find('y', [last_name])` → 3 for "Taylor"

8.2 Extracting and Manipulating Text¶

Function	Returns	Example
`left(text, n)`	Leftmost n characters	`left([last_name], 3)` → "Tay" for "Taylor"
`right(text, n)`	Rightmost n characters	`right([last_name], 4)` → "ylor" for "Taylor"
`mid(text, start, n)`	n characters starting at position start (1-based)	`mid([last_name], 2, 3)` → "ayl" for "Taylor"
`length(text)`	Number of characters in the string	`length([last_name])` → 6 for "Taylor"
`trim(text)`	Removes leading and trailing spaces	`trim(' hello ')` → "hello"
`upper(text)`	Converts to uppercase	`upper('John Doe')` → "JOHN DOE"
`lower(text)`	Converts to lowercase	`lower('John Doe')` → "john doe"
`replace_text(haystack, search, replace)`	Replaces all occurrences of search with replace	`replace_text([field1], "Taylor", "Harris")`

8.3 Combining Text¶

Function	Returns	Example
`concat(text, text, ...)`	Joins all items into one string	`concat([first_name], ' ', [last_name])` → "Rob Taylor"
`concat_ws(separator, text, text, ...)`	Joins items with a separator between each; skips empty values	`concat_ws(" and ", [veggie1], [veggie2], "Tomatoes")` → "Peas and Carrots and Tomatoes"

9. Practical Examples¶

Goal	Expression
Days between a date field and today	`datediff([date1], 'today', 'd')`
Person's age in whole years	`age_at_date([date_of_birth], [other_date])`
BMI from metric values (cm, kg), 1 decimal	`round(([weight]*10000)/(([height])^(2)), 1)`
BMI from English values (lb, in), 1 decimal	`round(([weight]/(([height])^(2))*703), 1)`
Strip a prefix+dash from record name (e.g., '4890-2318' → '2318')	`mid([record-name], find('-', [record-name])+1, length([record-name])-find('-', [record-name])+1)`
Convert first + last name to username format ('john_doe')	`lower(concat(trim([first_name]), '_', trim([last_name])))`
Add leading zeros to an integer (e.g., '7' → '007')	`right(concat('00', [my_integer]), 3)`

10. Common Questions¶

Q: Where can I access the Special Functions list in REDCap?

A: Open the Online Designer and click into any field's branching logic or calculated field editor. The Logic Editor toolbar includes a "Special Functions" button that opens the complete reference list. It is also accessible from the Codebook page.

Q: Can I use datediff() in branching logic, not just calculated fields?

A: Yes. Any Special Function can be used in branching logic. The function's return value is used as part of a comparison — for example, datediff([visit_date], 'today', 'd') > 30 will evaluate as true or false and control field visibility accordingly.

Q: What's the difference between datediff() and age_at_date()?

A: datediff() is a general-purpose date subtraction function that works in any units (days, months, years, hours, etc.) and can return signed or unsigned values. age_at_date() is specifically designed to calculate a person's age and correctly handles calendar-accurate age calculation (e.g., whether a birthday has passed in a given year). For calculating age, use age_at_date(). For other date differences, use datediff().

Q: Why do my aggregate functions (sum, mean, etc.) give unexpected results?

A: Blank values are silently excluded from all aggregate calculations. If a field has no value, it is not counted — so mean([a], [b], [c]) with only [a] and [b] filled in will return the average of just those two values, not divide by 3. This is intentional behavior. Use isblankormissingcode() in branching logic to handle fields where blank values need special treatment.

Q: What's the difference between sum() and just adding variables together with +?

A: They differ only when inputs are blank. sum([a], [b], [c]) skips blanks and totals the rest, and returns blank if all three are blank. [a]+[b]+[c] treats blanks as 0, so it always returns a number, and returns 0 if all three are blank. Use sum() when you want an all-blank result to stay blank rather than show a 0. Neither one waits for all inputs to be filled in; for that, wrap the calculation in if() with isblankormissingcode() checks (see §6.3).

Q: After our upgrade, some sum() calculations changed from 0 to blank. Why?

A: In 16.0.41 LTS, REDCap changed how special functions handle blank values. sum() now returns blank when every input is blank; before, it returned 0. This is intended behavior and will not be reversed, and REDCap cannot be downgraded. The old values are still in the project's Logging and in each field's data history. Update the logic in a copy or test project first, move it to production through Draft Mode, then run Data Quality rule H to recalculate every record in one pass. Don't run rule H on production before the logic is fixed: it would rewrite all the affected values using the current logic.

Q: Can I nest functions inside other functions?

A: Yes. REDCap functions can be nested to any reasonable depth. For example, round(datediff([dob], 'today', 'y'), 0) rounds the result of datediff, and lower(concat(trim([first_name]), '_', trim([last_name]))) nests trim, concat, and lower together. Parentheses must be balanced.

Q: Can I use 'today' or 'now' in date functions?

A: Yes. The literals 'today' and 'now' (in single quotes) can be used in datediff(), dayoftheweek(), year(), month(), and day() instead of a field variable. 'today' resolves to the current date; 'now' resolves to the current date and time.

Q: Do text functions work on date fields?

A: Only on date fields using YMD formatting. Text functions should not be used on date or datetime fields formatted as MDY or DMY — the results will be unreliable. If you need to extract parts of a date, use year(), month(), or day() instead.

Q: What is the difference between contains() and find()?

A: contains() returns true or false and is used in branching logic or boolean conditions. find() returns the position (a number) of the needle within the haystack — useful when you need to know where in a string something appears, such as finding the position of a dash to then use with mid().

11. Common Mistakes & Gotchas¶

Forgetting parentheses around both the base and exponent. For exponentiation, (base)^(exponent) is required. Writing 4^3 without the surrounding parentheses will not calculate correctly. Both the base and the exponent must each be wrapped in their own parentheses: (4)^(3).

Using :label as a function parameter. It is not possible to pass the display label of a multiple choice field into a Special Function using the :label modifier (e.g., contains([field:label], 'value')). Only the raw stored value of a field can be passed into functions. Use the field variable directly without any modifier.

Expecting blank fields to affect aggregate results. sum(), mean(), min(), max(), median(), and stdev() all silently skip blank values. If a field is unanswered, it is excluded from the calculation entirely — it does not count as zero. If zero is the intended value for unanswered fields, that must be handled separately (e.g., using if()). For sum() specifically: if every input is blank, the result is blank, not 0 (16.0.41 LTS and later).

Using + to keep a total blank until all inputs are filled in. On current versions + treats blank values as 0, so [var1]+[var2]+[var3] returns a partial total when some inputs are blank and 0 when all are blank. It does not stay blank. If the total must stay blank until every input has a value, wrap it: if(isblankormissingcode([var1]) or isblankormissingcode([var2]) or isblankormissingcode([var3]), "", [var1]+[var2]+[var3]).

Relying on how an older version handled blank values. Logic built around a blank-handling result, such as code to tell an all-blank sum() (formerly 0) apart from a real 0, can silently change results after an upgrade. Test for blanks explicitly with isblankormissingcode() instead of relying on what a function returns for blank inputs.

Using text functions on MDY/DMY date fields. Functions like left(), right(), find(), and contains() should not be used on date or datetime fields with MDY or DMY formatting. The string representation of the date differs from what the function expects. Use year(), month(), and day() for date component extraction.

Expecting datediff() to return whole years accurately for age. datediff() with units 'y' uses 365.2425 days as one year, which is an approximation. For accurate age-in-years (i.e., whether a birthday has actually passed), use age_at_date() instead.

Misreading log() base behavior. log(number, base) — if the base is omitted or not numeric, it defaults to base e (natural log), not base 10. To get a base-10 log, you must explicitly write log([value], 10).

Symptom	Fixed in
`age_at_date()`, `year()`, `month()` and `day()` failed "in many cases"	17.0.7
Same functions failed when referencing a field on another instrument where that instrument is repeating	17.0.8
`age_at_date()` failed on a calc field whenever the third parameter was omitted — directly contradicting the "optional third parameter" guidance above	17.1.2
Date functions failed on a calc field where the referenced field was in another event and not in YMD format	17.1.3
Further date-function failures on calc fields	17.2.0