---
title: "Excel Formulas"
slug: "excel-formulas"
tags: ["data-integration", "formulas"]
updated: 2026-09-28T17:15:07Z
published: 2026-09-28T17:15:07Z
canonical: "docs.unqork.io/excel-formulas"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.unqork.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Excel Formulas

Unqork supports a large set of Excel-compatible formulas that work the same way as their Excel equivalents. Creators familiar with Excel can use them here without additional configuration.

For formulas unique to Unqork, see [Unqork Formulas](/unqorkai/docs/unqork-formulas).

> [!NOTE]
> Some formulas have both a modern name (like `STDEV.S`) and a legacy alias (like `STDEVS`). Both are supported and behave identically.

## Logical

| Formula | Description |
| --- | --- |
| `AND(value1, value2, ...)` | Returns `true` if all arguments are true. |
| `EQ(value1, value2)` | Returns `true` if the two values are equal. |
| `FALSE()` | Returns the logical value `false`. |
| `GTE(value1, value2)` | Returns `true` if value1 is greater than or equal to value2. |
| `IF(condition, valueIfTrue, valueIfFalse)` | Returns value if a condition is true, another if false. |
| `LT(value1, value2)` | Returns `true` if value1 is less than value2. |
| `LTE(value1, value2)` | Returns `true` if value1 is less than or equal to value2. |
| `NE(value1, value2)` | Returns `true` if the two values are not equal. |
| `NOT(logical)` | Reverses the logical value of its argument. |
| `OR(value1, value2, ...)` | Returns `true` if any argument is true. |
| `SWITCH(expression, value1, result1, ...)` | Evaluates an expression against a list of values and returns the result for the first match. |
| `TRUE()` | Returns the logical value `true`. |
| `XOR(value1, value2, ...)` | Returns `true` if an odd number of arguments are true. |

## Math

| Formula | Description |
| --- | --- |
| `ABS(number)` | Returns the absolute value of a number. |
| `ADD(number1, number2)` | Adds two numbers. |
| `CEILING(number, significance)` | Rounds a number up to the nearest multiple of significance. |
| `COMBIN(n, k)` | Returns the number of combinations for a given number of items. |
| `DEGREES(angle)` | Converts radians to degrees. |
| `DIVIDE(number1, number2)` | Divides two numbers. |
| `EVEN(number)` | Rounds a number up to the nearest even integer. |
| `EXP(number)` | Returns e raised to the power of a number. |
| `FACT(number)` | Returns the factorial of a number. |
| `FLOOR(number, significance)` | Rounds a number down to the nearest multiple of significance. |
| `GCD(number1, number2, ...)` | Returns the greatest common divisor. |
| `INT(number)` | Rounds a number down to the nearest integer. |
| `LCM(number1, number2, ...)` | Returns the least common multiple. |
| `LN(number)` | Returns the natural logarithm of a number. |
| `LOG(number, base)` | Returns the logarithm of a number to a specified base. |
| `LOG10(number)` | Returns the base-10 logarithm of a number. |
| `MOD(number, divisor)` | Returns the remainder after division. |
| `MROUND(number, multiple)` | Rounds a number to the nearest specified multiple. |
| `MULTIPLY(number1, number2)` | Multiplies two numbers. |
| `ODD(number)` | Rounds a number up to the nearest odd integer. |
| `PI()` | Returns the value of π. |
| `POWER(number, power)` | Returns a number raised to a power. |
| `PRODUCT(number1, number2, ...)` | Multiplies all the numbers together. |
| `QUOTIENT(numerator, denominator)` | Returns the integer portion of a division. |
| `RADIANS(angle)` | Converts degrees to radians. |
| `RAND()` | Returns a random number between 0 and 1. |
| `RANDBETWEEN(bottom, top)` | Returns a random integer between two values. |
| `ROUND(number, digits)` | Rounds a number to a specified number of digits. |
| `ROUNDDOWN(number, digits)` | Rounds a number down toward zero. |
| `ROUNDUP(number, digits)` | Rounds a number up away from zero. |
| `SIGN(number)` | Returns the sign of a number: 1, -1, or 0. |
| `SQRT(number)` | Returns the square root of a number. |
| `SUM(number1, number2, ...)` | Adds all numbers together. |
| `SUMIF(range, criteria, sumRange)` | Adds numbers that meet a condition. |
| `SUMIFS(sumRange, range1, criteria1, ...)` | Adds numbers that meet multiple conditions. |
| `SUMPRODUCT(array1, array2, ...)` | Returns the sum of products of corresponding array elements. |
| `TRUNC(number, digits)` | Truncates a number to an integer by removing the decimal portion. |

## Text

| Formula | Description |
| --- | --- |
| `CHAR(number)` | Returns the character specified by a code number. |
| `CLEAN(text)` | Removes all non-printable characters from text. |
| `CODE(text)` | Returns the numeric code for the first character in a text string. |
| `CONCATENATE(text1, text2, ...)` | Joins several text strings into. |
| `DOLLAR(number, decimals)` | Converts a number to text using currency format. |
| `EXACT(text1, text2)` | Checks if two text strings are identical (case-sensitive). |
| `FIND(findText, withinText, startNum)` | Finds a text string in another (case-sensitive). |
| `FIXED(number, decimals)` | Formats a number as text with a fixed number of decimals. |
| `LEFT(text, numChars)` | Returns the leftmost characters from a text string. |
| `LEN(text)` | Returns the number of characters in a text string. |
| `LOWER(text)` | Converts text to lowercase. |
| `MID(text, startNum, numChars)` | Returns a specific number of characters from a text string. |
| `PROPER(text)` | Capitalizes the first letter of each word. |
| `REPLACE(text, startNum, numChars, newText)` | Replaces part of a text string with different text. |
| `REPT(text, numberTimes)` | Repeats text a given number of times. |
| `RIGHT(text, numChars)` | Returns the rightmost characters from a text string. |
| `SEARCH(findText, withinText, startNum)` | Finds a text string in another (case-insensitive). |
| `SUBSTITUTE(text, oldText, newText, instanceNum)` | Replaces existing text with new text. |
| `T(value)` | Returns the value if it is text, otherwise returns an empty string. |
| `TEXT(value, format)` | Converts a value to text in a specific number format. |
| `TEXTJOIN(delimiter, ignoreEmpty, text1, ...)` | Joins text strings using a delimiter, optionally ignoring empty values. |
| `TRIM(text)` | Removes extra spaces from text. |
| `UNICHAR(number)` | Returns the Unicode character for a given number. |
| `UNICODE(text)` | Returns the Unicode code point for the first character of a text string. |
| `UPPER(text)` | Converts text to uppercase. |
| `VALUE(text)` | Converts a text string that represents a number to a number. |

## Date & Time

| Formula | Description |
| --- | --- |
| `DATE(year, month, day)` | Returns the date value for a specified year, month, and day. |
| `DATEVALUE(dateText)` | Converts a date stored as text to a date value. |
| `DAY(date)` | Returns the day of the month for a given date. |
| `DAYS(endDate, startDate)` | Returns the number of days between two dates. |
| `DAYS360(startDate, endDate)` | Returns the number of days between two dates based on a 360-day year. |
| `EDATE(startDate, months)` | Returns the date a specified number of months before or after a start date. |
| `EOMONTH(startDate, months)` | Returns the last day of the month a specified number of months away. |
| `HOUR(time)` | Returns the hour from a time value. |
| `ISOWEEKNUM(date)` | Returns the ISO week number for a given date. |
| `MINUTE(time)` | Returns the minute from a time value. |
| `MONTH(date)` | Returns the month from a date. |
| `NETWORKDAYS(startDate, endDate)` | Returns the number of working days between two dates. |
| `NOW()` | Returns the current date and time. |
| `SECOND(time)` | Returns the seconds from a time value. |
| `TIME(hour, minute, second)` | Returns the time value for a specific hour, minute, and second. |
| `TIMEVALUE(timeText)` | Converts a time stored as text to a time value. |
| `TODAY()` | Returns today's date. |
| `WEEKDAY(date, returnType)` | Returns the day of the week for a date. |
| `WEEKNUM(date, returnType)` | Returns the week number for a date. |
| `WORKDAY(startDate, days)` | Returns the date a number of working days from a start date. |
| `YEAR(date)` | Returns the year from a date. |
| `YEARFRAC(startDate, endDate, basis)` | Returns the fraction of a year between two dates. |

### Comparing Dates Against TODAY()

Date fields in Unqork are stored as ISO 8601 strings (for example, `2026-09-15T00:00:00.000Z`). Because `TODAY()` returns a Unix epoch timestamp in milliseconds, comparing a date field directly against `TODAY()` does not produce a valid date comparison. Two patterns produce incorrect results for all inputs:

| Pattern | Result | Why |
| --- | --- | --- |
| `dateField < TODAY()` | Always `false` | The string is coerced to `NaN`; `NaN < number` is always false |
| `DATEVALUE(dateField) < TODAY()` | Always `true` | `DATEVALUE()` returns an Excel serial number (~46,000); `TODAY()` returns milliseconds (~1,750,000,000,000), so the serial number is always smaller |

Use of the following patterns instead:

| Pattern | How it works |
| --- | --- |
| `DATEFORMAT(dateField, "YYYY-MM-DD") < DATEFORMAT(TODAY(), "YYYY-MM-DD")` | Normalizes both sides to the same string format before comparing |
| `DAYS(TODAY(), dateField) > 0` | Returns the number of days between two dates; positive means the date is in the past |
| `MOMENT(TODAY(), "diff", MOMENT(dateField), "days") > 0` | Uses Moment.js to compute the day difference |

## Information

| Formula | Description |
| --- | --- |
| `ISBLANK(value)` | Returns `true` if the value is empty. |
| `ISEVEN(number)` | Returns `true` if the number is even. |
| `ISLOGICAL(value)` | Returns `true` if the value is a logical value. |
| `ISNONTEXT(value)` | Returns `true` if the value is not text. |
| `ISNUMBER(value)` | Returns `true` if the value is a number. |
| `ISODD(number)` | Returns `true` if the number is odd. |
| `ISTEXT(value)` | Returns `true` if the value is text. |

## Statistical

| Formula | Description |
| --- | --- |
| `AVERAGE(number1, number2, ...)` | Returns the average of the arguments. |
| `AVERAGEIF(range, criteria, averageRange)` | Returns the average of values that meet a condition. |
| `AVERAGEIFS(averageRange, range1, criteria1, ...)` | Returns the average of values that meet multiple conditions. |
| `COUNT(value1, value2, ...)` | Counts how many numbers are in the arguments. |
| `COUNTA(value1, value2, ...)` | Counts how many values (including text) are in the arguments. |
| `COUNTBLANK(range)` | Counts the number of empty cells. |
| `COUNTIF(range, criteria)` | Counts the number of cells that meet a condition. |
| `COUNTIFS(range1, criteria1, ...)` | Counts the number of cells that meet multiple conditions. |
| `LARGE(array, k)` | Returns the k-th largest value. |
| `MAX(number1, number2, ...)` | Returns the maximum value. |
| `MEDIAN(number1, number2, ...)` | Returns the median value. |
| `MIN(number1, number2, ...)` | Returns the minimum value. |
| `RANK.EQ(number, ref, order)` | Returns the rank of a number in a list. |
| `SMALL(array, k)` | Returns the k-th smallest value. |
| `STDEV.S(number1, number2, ...)` | Returns the standard deviation based on a sample. |
| `STDEV.P(number1, number2, ...)` | Returns the standard deviation based on the entire population. |
| `SUBTOTAL(functionNum, ref1, ...)` | Returns a subtotal in a list. |
| `VAR.S(number1, number2, ...)` | Returns the variance based on a sample. |
| `VAR.P(number1, number2, ...)` | Returns the variance based on the entire population. |

## Financial

| Formula | Description |
| --- | --- |
| `FV(rate, nper, pmt, pv, type)` | Returns the future value of an investment. |
| `IRR(values, guess)` | Returns the internal rate of return for a series of cash flows. |
| `MIRR(values, financeRate, reinvestRate)` | Returns the modified internal rate of return. |
| `NPV(rate, value1, value2, ...)` | Returns the net present value of an investment. |
| `PMT(rate, nper, pv, fv, type)` | Returns the periodic payment for a loan. |
| `PV(rate, nper, pmt, fv, type)` | Returns the present value of an investment. |
| `RATE(nper, pmt, pv, fv, type, guess)` | Returns the interest rate per period. |
| `SLN(cost, salvage, life)` | Returns the straight-line depreciation of an asset. |
| `XIRR(values, dates, guess)` | Returns the internal rate of return for irregular cash flows. |
| `XNPV(rate, values, dates)` | Returns the net present value for irregular cash flows. |

## Lookup & Reference

| Formula | Description |
| --- | --- |
| `CHOOSE(indexNum, value1, value2, ...)` | Returns a value from a list based on an index number. |
| `COLUMN(reference)` | Returns the column number of a reference. |
| `COLUMNS(array)` | Returns the number of columns in a reference. |
| `MATCH(lookupValue, lookupArray, matchType)` | Returns the position of a value in a range. |
| `ROW(reference)` | Returns the row number of a reference. |
| `ROWS(array)` | Returns the number of rows in a reference. |
| `TRANSPOSE(array)` | Returns a transposed array. |

## Engineering

Engineering formulas are available but rarely used in standard Unqork applications. They include number base conversions (`BIN2DEC`, `DEC2HEX`, and so on), bitwise operations (`BITAND`, `BITOR`, `BITXOR`, and so on), and complex number functions (`IMABS`, `IMSUM`, and so on). For details on these formulas, see standard Excel documentation.

---

**Changelog**

| Date | Change |
| --- | --- |
| 2026-09-28 | Added Comparing Dates Against TODAY() section — documents why direct comparison and DATEVALUE() fail, with three working patterns (EN-8163). |
| — | Initial publication. |

Also known as Unqork Users, or Designer Users; is anyone who is inside the Unqork platform.

An array is a type of object that stores one or more data types. Data types supported in arrays include numbers, strings, and objects.

Elements are are what you use to build Unqork applications. Elements include modules, workflows, Data Collections, Data Schema, and Record Collections.

A string is an object that represents a sequence of characters. Strings typically hold data represented in text form.

A string is an object that represents a sequence of characters. Strings typically hold data represented in text form.

A delimiter is a chracter or symbol that seperates words, data, or other characters. Use delimiters to mark the begining, or end of of value. Example delimiters include the comma ",", ampersand "&", or an empty space " ".

A standard format for representing dates and times. ISO 8601 format is represented in the following order: year, month, day, hour, minutes, seconds, and milliseconds.

Unix time is measured by the number of seconds elapsed since 00:00:00 UTC on 1 January 1970, the beginning of the Unix epoch.

A JavaScript date library that lets you parse, validate, manipulate, and reformat dates.

The location of an element in an array.