Function Index
Functions provide powerful formula capabilities for transforming and analyzing your data in real-time. This reference contains all available functions organized by category.
Quick Navigation
Date Functions
Functions for working with dates and times, including extraction, calculation, and manipulation of temporal data.
ConvertTimezone Converts datetime values to a specified time zone.
DateAdd Adds a specified amount of time to a date.
DateDiff Calculates the time difference between two dates.
DateFromUnix Converts a Unix timestamp to a Date data type.
DatePart Extracts the specified date part from a date value.
DateTrunc Truncates the date to the specified date part.
Day Returns the day of the month of a date value as a number.
DayOfYear Converts a date to the number day of the year, from 1 (Jan 1) to 365 (Dec 31). Accounts for leap years.
EndOfMonth Returns the last day of the month of a Date value.
Hour Returns the hour component of the given Date as a number.
InDateRange Determines if a date is within a specified date range.
InPriorDateRange Determines if a date is within a specified date range in a previous time period.
LastDay Evaluates a specified date component in a date value and returns the last date and time in that component as a timestamp.
MakeDate Evaluates specified year, month, day (and optional hour, minute, second) components and returns a datetime value in ISO format.
Minute Returns an integer representing the minute component of a specified date and time.
Month Returns an integer representing the month component in a specified datetime value.
MonthName Returns the name of the month component from a specified datetime value.
Now Returns the current date and time using your organization's account timezone.
Quarter Returns an integer representing the quarter component in a specified datetime value.
Second Returns an integer representing the second component of a specified date and time.
Today Returns the current date in ISO format using the Account Time Zone.
Weekday Returns an integer representing the day of the week in a specified datetime value.
WeekdayName Returns the name of the day of the week in a specified datetime value.
Year Returns an integer representing the year component in a specified datetime value.
DateFormat Formats a date value as a string according to a specified format pattern.
DateParse Parses a string representation of a date into a Date data type.
Logical Functions
Functions for conditional logic, null handling, and comparison operations.
Between Checks whether a value lies within a given range. The function is inclusive, so it includes the beginning and end values of the range.
Choose Given a specified index number, returns the matching value from a list.
Coalesce Returns the value of the first argument that is not Null. Often applied to columns to check for Null values and assign placeholder values in replacement of Nulls.
If Returns Value 1 for every row where the first condition is met. If more than one condition is supplied, subsequent conditions are evaluated and return their corresponding values. If all conditions are False, then the Else value is applied.
In Tests if a value matches any candidate value in a subsequent list of candidate values. Returns True if the value matches any of the subsequent candidate values, False if no values are matched.
IsNotNull Returns True if the argument is not Null (contains data), and False if the argument contains missing data values.
IsNull Returns True if the argument contains missing data values, and False if the argument is not Null. Often applied to a column of data to test for Null values.
Switch Returns the result corresponding to the first matching value. If the case argument evaluates to True, then the corresponding result is returned, otherwise the else argument is returned.
Zn Returns non-Null values, or 0 (zero) instead of Null values.
Math Functions
Functions for mathematical operations and calculations on numeric data.
Int Rounds the input number down to the largest integer of equal or lesser value.
IsEven Returns True if the integer is even, False if it is odd.
IsOdd Returns True if the integer is odd, False if it is even.
Sign Returns the sign of a number: -1 if negative, 1 if positive, 0 if zero.
Sqrt Compute the positive square root of a positive number.
Abs Returns the absolute value of a number.
Acos Returns the arc cosine (inverse cosine) of a number in radians.
Asin Returns the arc sine (inverse sine) of a number in radians.
Atan Returns the arc tangent (inverse tangent) of a number in radians.
Atan2 Returns the arc tangent of the quotient of its arguments in radians.
Tan Returns the tangent of an angle specified in radians.
Sin Returns the sine of an angle specified in radians.
Cos Returns the cosine of an angle specified in radians.
Ceiling Rounds a number up to the nearest integer.
Floor Rounds a number down to the nearest integer.
Round Rounds a number to a specified number of decimal places.
RoundUp Rounds a number up to a specified number of decimal places.
RoundDown Rounds a number down to a specified number of decimal places.
Trunc Truncates a number to a specified number of decimal places.
Mod Returns the remainder after dividing one number by another.
Div Performs integer division and returns the quotient.
Pi Returns the value of pi (π) to 15 decimal places.
Power Raises a number to a specified power.
Ln Returns the natural logarithm of a number.
Log Returns the logarithm of a number to a specified base.
Radians Converts degrees to radians.
Degrees Converts radians to degrees.
Cot Returns the cotangent of an angle specified in radians.
Exp Returns e raised to the power of a given number.
MRound Rounds a number to the nearest multiple of a specified value.
Least Returns the smallest value from a list of arguments.
Greatest Returns the largest value from a list of arguments.
BinFixed Creates fixed-width bins for numeric values.
BinRange Creates custom range-based bins for numeric values.
RowAvg Calculates the average of values across multiple columns in a row.
BitAnd Performs a bitwise AND operation on two integers.
BitOr Performs a bitwise OR operation on two integers.
DistanceGlobe Calculates the distance between two geographic coordinates on a sphere (Earth).
DistancePlane Calculates the Euclidean distance between two points on a 2D plane.
Text Functions
Functions for text manipulation and formatting, including case conversion, substring extraction, and string operations.
Contains Searches for specified substring(s) in a text value (case-sensitive). Returns True if found, False otherwise.
Left Returns a substring that begins at the start of a given string column values.
Lower Converts a given string to lowercase.
Right Returns a substring from the end of a string column values. The last n characters where n is the number argument.
Upper Converts a given string to uppercase.
Aggregate Functions
Functions that perform calculations across multiple rows of data, such as sums, averages, and counts.
Avg Calculates the average (mean) of values in a column.
Count Counts the number of rows in a dataset.
CountDistinct Counts the number of unique values in a column.
Max Returns the maximum value in a column.
Median Calculates the median (middle value) of values in a column.
Min Returns the minimum value in a column.
Mode Returns the most frequently occurring value in a column.
Percentile Calculates the value at a specified percentile in a column.
Stdev Calculates the sample standard deviation of values in a column.
StdevP Calculates the population standard deviation of values in a column.
Sum Calculates the sum of values in a column.
Variance Calculates the sample variance of values in a column.
VarianceP Calculates the population variance of values in a column.
GrandTotal Calculates the grand total across all data.
SubTotal Calculates subtotals within grouped data.
PercentOfTotal Calculates the percentage of a value relative to the total.
Array_Agg Aggregates values from multiple rows into an array.
Array_Agg_Distinct Aggregates distinct values from multiple rows into an array.
Corr Calculates the correlation coefficient between two columns.
RegressionIntercept Calculates the y-intercept of the linear regression line.
RegressionR2 Calculates the R-squared value of the linear regression.
RegressionSlope Calculates the slope of the linear regression line.
PercentileDisc Returns a discrete percentile value from the dataset.
PercentileCont Returns a continuous (interpolated) percentile value.
ListAgg Concatenates string values from multiple rows into a single delimited string.
ListAgg_Distinct Concatenates distinct string values from multiple rows into a single delimited string.
SumIf Calculates the sum of values that meet a specified condition.
CountIf Counts the number of rows that meet a specified condition.
AvgIf Calculates the average of values that meet a specified condition.
CountDistinctIf Counts the number of unique values that meet a specified condition.
MaxIf Returns the maximum value that meets a specified condition.
MinIf Returns the minimum value that meets a specified condition.
SumProduct Multiplies corresponding values and returns the sum of products.
Type Functions
Functions that convert values between different data types.
Date Converts a value to a Date data type.
Number Converts a value to a numeric data type.
Text Converts a value to a text/string data type.
Logical Converts a value to a boolean (True/False) data type.
Json Converts a value to JSON format.
Getting Started
- Functions Overview - Learn the basics of using functions
Syntax Basics
All formulas start with an equals sign:
=FunctionName(parameter1, parameter2, ...)
Column References
Reference columns by wrapping the column name in square brackets:
=[Column Name]
Example
=DateDiff("day", [Order Date], Today())
This calculates the number of days between the order date and today.