Formula reference - Dataverse formula columns

In this article, learn about all the functions available with formula columns in Microsoft Dataverse.

A

Abs – Absolute value of a number.

And – Boolean logic AND. Returns true if all arguments are true. You can also use the && operator.

Average – Calculates the average of a table expression or a set of arguments.

B

Blank – Returns a blank value that can be used to insert a NULL value in a data source.

C

Char – Translates a character code into a string.

Concatenate – Concatenates strings.

D

DateAdd – Adds days, months, quarters, or years to a date/time value.

DateDiff – Subtracts two date values, and shows the result in days, months, quarters, or years.

Day – Retrieves the day portion of a date/time value.

Decimal – Converts a string to a decimal number.

E

EndsWith – Checks whether a text string ends with another text string.

Error – Create a custom error or pass through an error.

Exp – Returns e raised to a power.

F

Float – Converts a string to a floating point number.

H

Hour – Returns the hour portion of a date/time value.

I

If – Returns one value if a condition is true and another value if not.

IfError – Detects errors and provides an alternative value or takes action.

Int – Rounds down to the nearest integer.

IsBlank – Checks for a blank value.

IsError – Checks for an error.

ISOWeekNum – Returns the ISO week number of a date/time value.

IsUTCToday – Checks whether a date/time value is sometime today in Coordinated Universal Time (UTC).

L

Left – Returns the left-most portion of a string.

Len – Returns the length of a string.

Ln – Returns the natural log.

Lower – Converts letters in a string of text to all lowercase.

M

Max – Maximum value of a table expression or a set of arguments.

Mid – Returns the middle portion of a string.

Min – Minimum value of a table expression or a set of arguments.

Minute – Retrieves the minute portion of a date/time value.

Mod – Returns the remainder after a dividend is divided by a divisor.

Month – Retrieves the month portion of a date/time value.

N

Not – Boolean logic NOT. Returns true if its argument is false, and returns false if its argument is true. You can also use the ! operator.

Now – Returns the current date/time value in the user's time zone.

O

Or – Boolean logic OR. Returns true if any of its arguments are true. You can also use the || operator.

P

Power – Returns a number raised to a power. You can also use the ^ operator.

R

Replace – Replaces part of a string with another string, by starting position of the string.

Right – Returns the right-most portion of a string.

Round – Rounds to the closest number.

RoundDown – Rounds down to the largest previous number.

RoundUp – Rounds up to the smallest next number.

S

Second – Retrieves the second portion of a date/time value.

Sqrt – Returns the square root of a number.

StartsWith – Checks if a text string begins with another text string.

Substitute – Replaces part of a string with another string, by matching strings.

Sum – Calculates the sum of a table expression or a set of arguments.

Switch – Matches with a set of values and then evaluates a corresponding formula.

T

Text – Converts any value and formats a number or date/time value to a string of text.

Trim – Removes extra spaces from the ends and interior of a string of text.

Trunc – Truncates the number to only the integer portion by removing any decimal portion.

TrimEnds – Removes extra spaces from the ends of a string of text only.

U

Upper – Converts letters in a string of text to all uppercase.

UTCNow – Returns the current date/time value in Coordinated Universal Time (UTC).

UTCToday – Returns the current date-only value in Coordinated Universal Time (UTC).

V

Value – Converts a string to a number.

W

Weekday – Retrieves the weekday portion of a date/time value.

WeekNum – Returns the week number of a date/time value.

Y

Year – Retrieves the year portion of a date/time value.