Formula functions

Every function you can use in a formula, with the description Grindstone shows for it. When you type a formula, function names aren’t case-sensitive; the formula builder shows the same names with spaces between their words. To learn how formulas work, see Formulas.

Under each function are the ways it can be called. The parameters just before … can be repeated, together, as many times as you need.

Abs

Returns a number or time span without its sign.

  • Abs(Number)
  • Abs(Time Span)

Acos

Returns the angle, in radians, whose cosine is Number.

  • Acos(Number)

Acosh

Returns the inverse hyperbolic cosine of Number.

  • Acosh(Number)

Add

Adds numbers together, adds time spans to a date and time, or adds time spans together.

  • Add(Number, Number, …)
  • Add(Date & Time, Time Span, …)
  • Add(Time Span, Time Span, …)

AddDays

Adds a number of days, which may be fractional or negative, to a date and time.

  • AddDays(Date & Time, Days)

AddHours

Adds a number of hours, which may be fractional or negative, to a date and time or a time span.

  • AddHours(Date & Time, Hours)
  • AddHours(Time Span, Hours)

AddMilliseconds

Adds a number of milliseconds, which may be fractional or negative, to a date and time or a time span.

  • AddMilliseconds(Date & Time, Milliseconds)
  • AddMilliseconds(Time Span, Milliseconds)

AddMinutes

Adds a number of minutes, which may be fractional or negative, to a date and time or a time span.

  • AddMinutes(Date & Time, Minutes)
  • AddMinutes(Time Span, Minutes)

AddMonths

Adds a number of calendar months, which may be negative, to a date and time in the workspace time zone.

  • AddMonths(Date & Time, Months)

AddSeconds

Adds a number of seconds, which may be fractional or negative, to a date and time or a time span.

  • AddSeconds(Date & Time, Seconds)
  • AddSeconds(Time Span, Seconds)

AddWorkDays

Returns the date Work Days work days after Start, or before it when negative, skipping weekends (Saturday and Sunday unless Weekend says otherwise, as a code or seven 0s and 1s starting with Monday) and the Holiday Date of each entity in Holidays.

  • AddWorkDays(Start, Work Days)
  • AddWorkDays(Start, Work Days, Weekend)
  • AddWorkDays(Start, Work Days, Holidays, Holiday Date)
  • AddWorkDays(Start, Work Days, Weekend, Holidays, Holiday Date)

AddYears

Adds a number of years, which may be negative, to a date and time.

  • AddYears(Date & Time, Years)

And

Yes when every condition is true.

  • And(Condition, Condition, …)

Asin

Returns the angle, in radians, whose sine is Number.

  • Asin(Number)

Asinh

Returns the inverse hyperbolic sine of Number.

  • Asinh(Number)

Atan

Returns the angle, in radians, whose tangent is Number.

  • Atan(Number)

Atan2

Returns the angle, in radians, between the positive x-axis and the point at X and Y. Unlike Excel, Y comes first.

  • Atan2(Y, X)

Atanh

Returns the inverse hyperbolic tangent of Number.

  • Atanh(Number)

Average

Returns the average of Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Date and time ranges are averaged by their lengths.

  • Average(Measure)
  • Average(Measure, Condition)
  • Average(Scope, Measure)
  • Average(Scope, Measure, Condition)

Ceiling

Rounds a number up to a whole number or to a multiple of Significance, or rounds a date and time or a duration up to a multiple of Block. With Mode, negative numbers round away from zero.

  • Ceiling(Number)
  • Ceiling(Date & Time, Block)
  • Ceiling(Date & Time Range, Block)
  • Ceiling(Time Span, Block)
  • Ceiling(Number, Significance)
  • Ceiling(Number, Significance, Mode)

Clean

Removes control characters from Text.

  • Clean(Text)

Coalesce

Returns the first value that is not blank.

  • Coalesce(Value, …)

Concat

Joins the values together as text.

  • Concat(Value, …)

Contains

Yes when Text contains Search Text, matching case exactly.

  • Contains(Text, Search Text)

ContainsCaseInsensitive

Yes when Text contains Search Text, ignoring case. Search Text may use ? for any one character, * for any run of characters and ~ before either to match it literally.

  • ContainsCaseInsensitive(Text, Search Text)

Cos

Returns the cosine of an angle in radians.

  • Cos(Angle)

Cosh

Returns the hyperbolic cosine of Number.

  • Cosh(Number)

Count

Counts the values of Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Count(Measure)
  • Count(Measure, Condition)
  • Count(Scope, Measure)
  • Count(Scope, Measure, Condition)

Date

Returns the start of the day containing a date and time, or builds a date from Year, Month and Day, in the workspace time zone.

  • Date(Date & Time)
  • Date(Year, Month, Day)

Day

Returns the day of the month, from 1 to 31, of a date and time in the workspace time zone.

  • Day(Date & Time)

DayOfWeek

Returns the day of the week of a date and time in the workspace time zone, from 1 for Sunday to 7 for Saturday unless Return Type numbers the days differently.

  • DayOfWeek(Date & Time)
  • DayOfWeek(Date & Time, Return Type)

Days

Returns the number of calendar days from Start to End in the workspace time zone, or the whole days in a time span or a date and time range.

  • Days(Date & Time Range)
  • Days(Time Span)
  • Days(End, Start)

Days360

Returns the number of days from Start to End on a 360-day year of twelve 30-day months, using the European method when European Method is true.

  • Days360(Start, End)
  • Days360(Start, End, European Method)

DefaultAssignment

Returns no assignment.

  • DefaultAssignment()

DefaultBoolean

Returns No.

  • DefaultBoolean()

DefaultDateTime

Returns the empty date and time.

  • DefaultDateTime()

DefaultDateTimeRange

Returns the empty date and time range.

  • DefaultDateTimeRange()

DefaultDecimal

Returns zero as a decimal number.

  • DefaultDecimal()

DefaultFloat

Returns zero as a floating point number.

  • DefaultFloat()

DefaultInteger

Returns zero as an integer.

  • DefaultInteger()

DefaultListValue

Returns no list value.

  • DefaultListValue()

DefaultMultiLineText

Returns empty multi-line text.

  • DefaultMultiLineText()

DefaultPerson

Returns no person.

  • DefaultPerson()

DefaultSingleLineText

Returns empty single-line text.

  • DefaultSingleLineText()

DefaultTimeSlice

Returns no time slice.

  • DefaultTimeSlice()

DefaultTimeSpan

Returns a time span of zero.

  • DefaultTimeSpan()

DefaultTrait

Returns no trait.

  • DefaultTrait()

DefaultWorkItem

Returns no work item.

  • DefaultWorkItem()

Divide

Divides a number or a duration by Divisor. Dividing a duration by a duration gives how many times one fits in the other.

  • Divide(Number, Divisor)
  • Divide(Date & Time Range, Divisor)
  • Divide(Time Span, Divisor)

E

Returns the mathematical constant e.

  • E()

End

Returns the end of a date and time range.

  • End(Date & Time Range)

EndOfMonth

Returns the last day of the month that is Months months after a date and time, or before it when negative, in the workspace time zone.

  • EndOfMonth(Date & Time, Months)

EndsWith

Yes when Text ends with Search Text, matching case exactly.

  • EndsWith(Text, Search Text)

EndsWithCaseInsensitive

Yes when Text ends with Search Text, ignoring case. Search Text may use ? for any one character, * for any run of characters and ~ before either to match it literally.

  • EndsWithCaseInsensitive(Text, Search Text)

Equal

Yes when Value equals Compared To. Text is compared without regard to case.

  • Equal(Value, Compared To)

Exact

Yes when Text and Other Text are identical, matching case exactly.

  • Exact(Text, Other Text)

Exp

Returns e raised to the power of Number.

  • Exp(Number)

Floor

Rounds a number down to a whole number or to a multiple of Significance, or rounds a date and time or a duration down to a multiple of Block. With Mode, negative numbers round toward zero.

  • Floor(Number)
  • Floor(Date & Time, Block)
  • Floor(Date & Time Range, Block)
  • Floor(Time Span, Block)
  • Floor(Number, Significance)
  • Floor(Number, Significance, Mode)

Format

Fills the numbered placeholders in Format, such as {0} and {1}, with the values that follow it.

  • Format(Format, Value, …)

FromCodePoint

Returns the character with the Unicode code point Code Point.

  • FromCodePoint(Code Point)

FromDays

Returns a time span of a number of days.

  • FromDays(Days)

FromHours

Returns a time span of a number of hours.

  • FromHours(Hours)

FromMilliseconds

Returns a time span of a number of milliseconds.

  • FromMilliseconds(Milliseconds)

FromMinutes

Returns a time span of a number of minutes.

  • FromMinutes(Minutes)

FromSeconds

Returns a time span of a number of seconds.

  • FromSeconds(Seconds)

GreaterThan

Yes when Value is greater than Compared To. Text is compared without regard to case.

  • GreaterThan(Value, Compared To)

GreaterThanOrEqual

Yes when Value is greater than or equal to Compared To. Text is compared without regard to case.

  • GreaterThanOrEqual(Value, Compared To)

Hour

Returns the hour, from 0 to 23, of a date and time in the workspace time zone.

  • Hour(Date & Time)

Hours

Returns the hours part, from 0 to 23, of a time span or of a date and time range’s length.

  • Hours(Date & Time Range)
  • Hours(Time Span)

HoursDisplay

Returns a time span or a date and time range’s length as text in hours, minutes and seconds, with hours going past 24.

  • HoursDisplay(Date & Time Range)
  • HoursDisplay(Time Span)

Humanize

Describes a date and time relative to now, a date and time range, or a time span in words.

  • Humanize(Date & Time)
  • Humanize(Date & Time Range)
  • Humanize(Time Span)

If

Returns If True when Condition is true, or If False otherwise.

  • If(Condition, If True, If False)

IfBlank

Returns the value of Measure, or If Blank when there is none, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • IfBlank(Measure, If Blank)
  • IfBlank(Measure, If Blank, Condition)
  • IfBlank(Scope, Measure, If Blank)
  • IfBlank(Scope, Measure, If Blank, Condition)

IfFault

Returns Value, or If Fault when working out Value faults.

  • IfFault(Value, If Fault)

IndexOf

Returns the position, counting from 1, of the first occurrence of Search Text in Text, matching case exactly. Faults when it is not found.

  • IndexOf(Text, Search Text)

IndexOfCaseInsensitive

Returns the position, counting from 1, of the first match for Search Text in Text, ignoring case. Search Text may use ? for any one character, * for any run of characters and ~ before either to match it literally. Faults when there is no match.

  • IndexOfCaseInsensitive(Text, Search Text)

Insert

Inserts Text to Insert into Text before the character at Position, counting from 1.

  • Insert(Text, Position, Text to Insert)

IsBlank

Yes when Measure has no values among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • IsBlank(Measure)
  • IsBlank(Measure, Condition)
  • IsBlank(Scope, Measure)
  • IsBlank(Scope, Measure, Condition)

IsLeapDay

Yes when a date and time falls on 29 February in the workspace time zone.

  • IsLeapDay(Date & Time)

IsLeapYear

Yes when a date and time falls in a leap year in the workspace time zone.

  • IsLeapYear(Date & Time)

IsoWeekOfYear

Returns the ISO 8601 week number of a date and time in the workspace time zone.

  • IsoWeekOfYear(Date & Time)

IsParameterSet

Yes when Parameter has a value.

  • IsParameterSet(Parameter)

IsParameterUnset

Yes when Parameter has no value.

  • IsParameterUnset(Parameter)

Join

Joins values into one text separated by Delimiter, leaving out empty ones when Ignore Empty is true. Given a measure, it joins the values of Measure, sorted, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Join(Delimiter, Ignore Empty, Measure)
  • Join(Delimiter, Ignore Empty, Measure, Condition)
  • Join(Scope, Delimiter, Ignore Empty, Measure)
  • Join(Scope, Delimiter, Ignore Empty, Measure, Condition)
  • Join(Delimiter, Ignore Empty, Value, …)

Large

Returns the largest value of Measure at Rank, where 1 is the largest, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Large(Measure, Rank)
  • Large(Measure, Rank, Condition)
  • Large(Scope, Measure, Rank)
  • Large(Scope, Measure, Rank, Condition)

LastIndexOf

Returns the position, counting from 1, of the last occurrence of Search Text in Text, matching case exactly. Faults when it is not found.

  • LastIndexOf(Text, Search Text)

LastIndexOfCaseInsensitive

Returns the position, counting from 1, of the last match for Search Text in Text, ignoring case. Search Text may use ? for any one character, * for any run of characters and ~ before either to match it literally. Faults when there is no match.

  • LastIndexOfCaseInsensitive(Text, Search Text)

Length

Returns the number of characters in Text.

  • Length(Text)

LessThan

Yes when Value is less than Compared To. Text is compared without regard to case.

  • LessThan(Value, Compared To)

LessThanOrEqual

Yes when Value is less than or equal to Compared To. Text is compared without regard to case.

  • LessThanOrEqual(Value, Compared To)

Ln

Returns the natural logarithm of Number.

  • Ln(Number)

Log

Returns the logarithm of Number in Base, or in base 10 when no base is given.

  • Log(Number)
  • Log(Number, Base)

Log10

Returns the base-10 logarithm of Number.

  • Log10(Number)

Max

Returns the largest value of Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Max(Measure)
  • Max(Measure, Condition)
  • Max(Scope, Measure)
  • Max(Scope, Measure, Condition)

Median

Returns the median of Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Date and time ranges are measured by their lengths.

  • Median(Measure)
  • Median(Measure, Condition)
  • Median(Scope, Measure)
  • Median(Scope, Measure, Condition)

Millisecond

Returns the millisecond, from 0 to 999, of a date and time in the workspace time zone.

  • Millisecond(Date & Time)

Milliseconds

Returns the milliseconds part, from 0 to 999, of a time span or of a date and time range’s length.

  • Milliseconds(Date & Time Range)
  • Milliseconds(Time Span)

Min

Returns the smallest value of Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Min(Measure)
  • Min(Measure, Condition)
  • Min(Scope, Measure)
  • Min(Scope, Measure, Condition)

Minute

Returns the minute, from 0 to 59, of a date and time in the workspace time zone.

  • Minute(Date & Time)

Minutes

Returns the minutes part, from 0 to 59, of a time span or of a date and time range’s length.

  • Minutes(Date & Time Range)
  • Minutes(Time Span)

Mode

Returns the most frequent value of Measure, the smallest of those tied, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Faults when no value repeats.

  • Mode(Measure)
  • Mode(Measure, Condition)
  • Mode(Scope, Measure)
  • Mode(Scope, Measure, Condition)

Modulo

Returns the remainder of dividing a number or a duration by Divisor, with the sign of Divisor.

  • Modulo(Number, Divisor)
  • Modulo(Date & Time Range, Divisor)
  • Modulo(Time Span, Divisor)

Month

Returns the month, from 1 to 12, of a date and time in the workspace time zone.

  • Month(Date & Time)

Multiply

Multiplies numbers together, or multiplies a duration by a number.

  • Multiply(Number, Number, …)
  • Multiply(Date & Time Range, Number)
  • Multiply(Number, Date & Time Range)
  • Multiply(Time Span, Number)
  • Multiply(Number, Time Span)

Nand

Yes unless every condition is true.

  • Nand(Condition, Condition, …)

Negate

Returns a number or a duration with its sign reversed.

  • Negate(Number)
  • Negate(Date & Time Range)
  • Negate(Time Span)

Nor

Yes when no condition is true.

  • Nor(Condition, Condition, …)

Not

Yes when Condition is false.

  • Not(Condition)

NotEqual

Yes when Value does not equal Compared To. Text is compared without regard to case.

  • NotEqual(Value, Compared To)

Now

Returns the current date and time.

  • Now()

NowInUniversalTime

Returns the current date and time in Coordinated Universal Time.

  • NowInUniversalTime()

Or

Yes when any condition is true.

  • Or(Condition, Condition, …)

Overlap

Returns the part of time that First Range and Second Range have in common.

  • Overlap(First Range, Second Range)

ParameterAssignment

Returns the assignment Parameter is set to.

  • ParameterAssignment(Parameter)

ParameterBoolean

Returns the yes or no Parameter is set to.

  • ParameterBoolean(Parameter)

ParameterDateTime

Returns the date and time Parameter is set to.

  • ParameterDateTime(Parameter)

ParameterDateTimeRange

Returns the date and time range Parameter is set to.

  • ParameterDateTimeRange(Parameter)

ParameterDecimal

Returns the decimal number Parameter is set to.

  • ParameterDecimal(Parameter)

ParameterFloat

Returns the floating point number Parameter is set to.

  • ParameterFloat(Parameter)

ParameterInteger

Returns the integer Parameter is set to.

  • ParameterInteger(Parameter)

ParameterListValue

Returns the list value Parameter is set to.

  • ParameterListValue(Parameter)

ParameterMultiLineText

Returns the multi-line text Parameter is set to.

  • ParameterMultiLineText(Parameter)

ParameterPerson

Returns the person Parameter is set to.

  • ParameterPerson(Parameter)

ParameterSingleLineText

Returns the single-line text Parameter is set to.

  • ParameterSingleLineText(Parameter)

ParameterTimeSlice

Returns the time slice Parameter is set to.

  • ParameterTimeSlice(Parameter)

ParameterTimeSpan

Returns the time span Parameter is set to.

  • ParameterTimeSpan(Parameter)

ParameterTrait

Returns the trait Parameter is set to.

  • ParameterTrait(Parameter)

ParameterWorkItem

Returns the work item Parameter is set to.

  • ParameterWorkItem(Parameter)

PercentileExclusive

Returns the value of Measure at Percentile, a fraction between 0 and 1 interpolated by the exclusive method, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • PercentileExclusive(Measure, Percentile)
  • PercentileExclusive(Measure, Percentile, Condition)
  • PercentileExclusive(Scope, Measure, Percentile)
  • PercentileExclusive(Scope, Measure, Percentile, Condition)

PercentileInclusive

Returns the value of Measure at Percentile, a fraction from 0 to 1 interpolated by the inclusive method, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • PercentileInclusive(Measure, Percentile)
  • PercentileInclusive(Measure, Percentile, Condition)
  • PercentileInclusive(Scope, Measure, Percentile)
  • PercentileInclusive(Scope, Measure, Percentile, Condition)

Perspective

Returns the person whose perspective the view is being worked out for.

  • Perspective()

PerspectiveId

Returns the identifier of the person whose perspective the view is being worked out for.

  • PerspectiveId()

Pi

Returns the mathematical constant pi.

  • Pi()

Power

Returns Base raised to the power of Exponent.

  • Power(Base, Exponent)

QuartileExclusive

Returns the value of Measure at Quartile, from 1 to 3 interpolated by the exclusive method, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • QuartileExclusive(Measure, Quartile)
  • QuartileExclusive(Measure, Quartile, Condition)
  • QuartileExclusive(Scope, Measure, Quartile)
  • QuartileExclusive(Scope, Measure, Quartile, Condition)

QuartileInclusive

Returns the value of Measure at Quartile, from 0 for the smallest to 4 for the largest, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • QuartileInclusive(Measure, Quartile)
  • QuartileInclusive(Measure, Quartile, Condition)
  • QuartileInclusive(Scope, Measure, Quartile)
  • QuartileInclusive(Scope, Measure, Quartile, Condition)

QueryForAssignmentInContext

Works out Formula as though for Entity, giving an assignment.

  • QueryForAssignmentInContext(Entity, Formula)

QueryForBooleanInContext

Works out Formula as though for Entity, giving yes or no.

  • QueryForBooleanInContext(Entity, Formula)

QueryForDateTimeInContext

Works out Formula as though for Entity, giving a date and time.

  • QueryForDateTimeInContext(Entity, Formula)

QueryForDateTimeRangeInContext

Works out Formula as though for Entity, giving a date and time range.

  • QueryForDateTimeRangeInContext(Entity, Formula)

QueryForDecimalInContext

Works out Formula as though for Entity, giving a decimal number.

  • QueryForDecimalInContext(Entity, Formula)

QueryForFloatInContext

Works out Formula as though for Entity, giving a floating point number.

  • QueryForFloatInContext(Entity, Formula)

QueryForIntegerInContext

Works out Formula as though for Entity, giving an integer.

  • QueryForIntegerInContext(Entity, Formula)

QueryForListValueInContext

Works out Formula as though for Entity, giving a list value.

  • QueryForListValueInContext(Entity, Formula)

QueryForPersonInContext

Works out Formula as though for Entity, giving a person.

  • QueryForPersonInContext(Entity, Formula)

QueryForTextInContext

Works out Formula as though for Entity, giving text.

  • QueryForTextInContext(Entity, Formula)

QueryForTimeSliceInContext

Works out Formula as though for Entity, giving a time slice.

  • QueryForTimeSliceInContext(Entity, Formula)

QueryForTimeSpanInContext

Works out Formula as though for Entity, giving a time span.

  • QueryForTimeSpanInContext(Entity, Formula)

QueryForTraitInContext

Works out Formula as though for Entity, giving a trait.

  • QueryForTraitInContext(Entity, Formula)

QueryForWorkItemInContext

Works out Formula as though for Entity, giving a work item.

  • QueryForWorkItemInContext(Entity, Formula)

Quotient

Returns the whole part of dividing a number by Divisor.

  • Quotient(Number, Divisor)

RankAvg

Returns the rank of Value among the values of Measure, 1 being the largest unless Ascending is true, with tied values sharing the average of their ranks, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Faults when Value is not among them.

  • RankAvg(Value, Measure)
  • RankAvg(Value, Measure, Condition)
  • RankAvg(Scope, Value, Measure)
  • RankAvg(Scope, Value, Measure, Condition)
  • RankAvg(Value, Measure, Ascending)
  • RankAvg(Value, Measure, Ascending, Condition)
  • RankAvg(Scope, Value, Measure, Ascending)
  • RankAvg(Scope, Value, Measure, Ascending, Condition)

RankEq

Returns the rank of Value among the values of Measure, 1 being the largest unless Ascending is true, with tied values sharing the best of their ranks, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Faults when Value is not among them.

  • RankEq(Value, Measure)
  • RankEq(Value, Measure, Condition)
  • RankEq(Scope, Value, Measure)
  • RankEq(Scope, Value, Measure, Condition)
  • RankEq(Value, Measure, Ascending)
  • RankEq(Value, Measure, Ascending, Condition)
  • RankEq(Scope, Value, Measure, Ascending)
  • RankEq(Scope, Value, Measure, Ascending, Condition)

RegexGetGroup

Returns the text captured by the group named or numbered Group in the first match of Regular Expression in Text, or empty text when there is none.

  • RegexGetGroup(Text, Regular Expression, Group)

RegexReplace

Replaces matches of Regular Expression in Text with Replacement: every match, or only the match at Occurrence when it is given (counting from 1, or from the end when negative), ignoring case when Ignore Case is true.

  • RegexReplace(Text, Regular Expression, Replacement)
  • RegexReplace(Text, Regular Expression, Replacement, Occurrence)
  • RegexReplace(Text, Regular Expression, Replacement, Occurrence, Ignore Case)

RegexTest

Yes when Regular Expression matches Text.

  • RegexTest(Text, Regular Expression)

Repeat

Repeats Text Count times.

  • Repeat(Text, Count)

Replace

Replaces every occurrence of Search Text in Text with Replacement, matching case exactly, or only the occurrence at Instance, counting from 1, when it is given.

  • Replace(Text, Search Text, Replacement)
  • Replace(Text, Search Text, Replacement, Instance)

ReplaceAt

Replaces Length characters of Text, starting at Start counting from 1, with Replacement.

  • ReplaceAt(Text, Start, Length, Replacement)

Round

Rounds a number to Digits decimal places, or to a whole number when no digits are given, with halves rounding away from zero, or rounds a date and time or a duration to the nearest multiple of Block.

  • Round(Number)
  • Round(Number, Digits)
  • Round(Date & Time, Block)
  • Round(Date & Time Range, Block)
  • Round(Time Span, Block)

RoundDown

Rounds Number toward zero to Digits decimal places. Negative Digits round to the left of the decimal point.

  • RoundDown(Number, Digits)

RoundToMultiple

Rounds Number to the nearest multiple of Multiple, with halves rounding away from zero.

  • RoundToMultiple(Number, Multiple)

RoundUp

Rounds Number away from zero to Digits decimal places. Negative Digits round to the left of the decimal point.

  • RoundUp(Number, Digits)

Second

Returns the second, from 0 to 59, of a date and time in the workspace time zone.

  • Second(Date & Time)

Seconds

Returns the seconds part, from 0 to 59, of a time span or of a date and time range’s length.

  • Seconds(Date & Time Range)
  • Seconds(Time Span)

Sign

Returns 1 when a number or time span is positive, -1 when it is negative, or 0.

  • Sign(Number)
  • Sign(Time Span)

Sin

Returns the sine of an angle in radians.

  • Sin(Angle)

Sinh

Returns the hyperbolic sine of Number.

  • Sinh(Number)

Small

Returns the smallest value of Measure at Rank, where 1 is the smallest, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • Small(Measure, Rank)
  • Small(Measure, Rank, Condition)
  • Small(Scope, Measure, Rank)
  • Small(Scope, Measure, Rank, Condition)

Sqrt

Returns the square root of Number.

  • Sqrt(Number)

StandardDeviationPopulation

Returns the standard deviation of Measure, treating the values as the whole population, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • StandardDeviationPopulation(Measure)
  • StandardDeviationPopulation(Measure, Condition)
  • StandardDeviationPopulation(Scope, Measure)
  • StandardDeviationPopulation(Scope, Measure, Condition)

StandardDeviationSample

Returns the standard deviation of Measure, treating the values as a sample, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • StandardDeviationSample(Measure)
  • StandardDeviationSample(Measure, Condition)
  • StandardDeviationSample(Scope, Measure)
  • StandardDeviationSample(Scope, Measure, Condition)

Start

Returns the start of a date and time range.

  • Start(Date & Time Range)

StartsWith

Yes when Text starts with Search Text, matching case exactly.

  • StartsWith(Text, Search Text)

StartsWithCaseInsensitive

Yes when Text starts with Search Text, ignoring case. Search Text may use ? for any one character, * for any run of characters and ~ before either to match it literally.

  • StartsWithCaseInsensitive(Text, Search Text)

Sub

Returns the part of Text that starts at Start, counting from 1, and runs for Length characters, or to the end when no length is given.

  • Sub(Text, Start)
  • Sub(Text, Start, Length)

Subtract

Subtracts Amount to Subtract from a number, a date and time, or a duration.

  • Subtract(Number, Amount to Subtract)
  • Subtract(Date & Time, Amount to Subtract)
  • Subtract(Date & Time Range, Amount to Subtract)
  • Subtract(Time Span, Amount to Subtract)

Sum

Adds up Measure among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given. Measure can be a formula worked out for each related entity of Subject. Date and time ranges add up their lengths, counting only the parts between From and To when they are given.

  • Sum(Measure)
  • Sum(Measure, Condition)
  • Sum(Measure, From, To)
  • Sum(Measure, From, To, Condition)
  • Sum(Scope, Measure)
  • Sum(Scope, Measure, Condition)
  • Sum(Scope, Measure, From, To)
  • Sum(Scope, Measure, From, To, Condition)
  • Sum(Subject, Measure)
  • Sum(Subject, Measure, Condition)
  • Sum(Scope, Subject, Measure)
  • Sum(Scope, Subject, Measure, Condition)

SumFrom

Adds up the lengths of the date and time ranges in Measure, counting only the parts on or after From, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • SumFrom(Measure, From)
  • SumFrom(Measure, From, Condition)

SumTo

Adds up the lengths of the date and time ranges in Measure, counting only the parts before To, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • SumTo(Measure, To)
  • SumTo(Measure, To, Condition)

Switch

Returns the Result paired with the first Condition that is true, or Default when none is, or an empty value when there is no default.

  • Switch(Condition, Result, …)
  • Switch(Condition, Result, …, Default)

Tan

Returns the tangent of an angle in radians.

  • Tan(Angle)

Tanh

Returns the hyperbolic tangent of Number.

  • Tanh(Number)

TextAfter

Returns the part of Text after Delimiter: the first occurrence, or the one at Instance (counting from the end when negative). Ignore Case ignores case, Match End treats the end of Text as a delimiter, and If Not Found is returned instead of faulting when the delimiter is missing.

  • TextAfter(Text, Delimiter)
  • TextAfter(Text, Delimiter, Instance)
  • TextAfter(Text, Delimiter, Instance, Ignore Case)
  • TextAfter(Text, Delimiter, Instance, Ignore Case, Match End)
  • TextAfter(Text, Delimiter, Instance, Ignore Case, Match End, If Not Found)

TextBefore

Returns the part of Text before Delimiter: the first occurrence, or the one at Instance (counting from the end when negative). Ignore Case ignores case, Match End treats the end of Text as a delimiter, and If Not Found is returned instead of faulting when the delimiter is missing.

  • TextBefore(Text, Delimiter)
  • TextBefore(Text, Delimiter, Instance)
  • TextBefore(Text, Delimiter, Instance, Ignore Case)
  • TextBefore(Text, Delimiter, Instance, Ignore Case, Match End)
  • TextBefore(Text, Delimiter, Instance, Ignore Case, Match End, If Not Found)

TimeOfDay

Returns the time since midnight of a date and time in the workspace time zone.

  • TimeOfDay(Date & Time)

ToBoolean

Converts Value to yes or no.

  • ToBoolean(Value)

ToCodePoint

Returns the Unicode code point of the first character of Text.

  • ToCodePoint(Text)

ToDateTime

Converts Value to a date and time, or reads one from Text using Format.

  • ToDateTime(Value)
  • ToDateTime(Text, Format)

ToDateTimeRange

Returns the date and time range from Start to End.

  • ToDateTimeRange(Start, End)

Today

Returns the start of the current day in the workspace time zone.

  • Today()

ToDays

Returns a time span or a date and time range’s length in days, including any fraction.

  • ToDays(Date & Time Range)
  • ToDays(Time Span)

ToDecimal

Converts Value to a decimal number, reading text as a number or a percentage.

  • ToDecimal(Value)

ToFloat

Converts Value to a floating point number, reading text as a number or a percentage.

  • ToFloat(Value)

ToHours

Returns a time span or a date and time range’s length in hours, including any fraction.

  • ToHours(Date & Time Range)
  • ToHours(Time Span)

ToId

Converts Value to an identifier.

  • ToId(Value)

ToInteger

Converts Value to an integer, dropping any fraction and reading text as a number or a percentage.

  • ToInteger(Value)

ToLocalTime

Converts a date and time, or a date and time range, to the workspace time zone.

  • ToLocalTime(Date & Time)
  • ToLocalTime(Date & Time Range)

ToLowerCase

Converts Text to lower case.

  • ToLowerCase(Text)

ToMetricNumeral

Writes Number with a metric prefix, such as 1.5k.

  • ToMetricNumeral(Number)

ToMilliseconds

Returns a time span or a date and time range’s length in milliseconds, including any fraction.

  • ToMilliseconds(Date & Time Range)
  • ToMilliseconds(Time Span)

ToMinutes

Returns a time span or a date and time range’s length in minutes, including any fraction.

  • ToMinutes(Date & Time Range)
  • ToMinutes(Time Span)

ToOrdinalWords

Writes Number as an ordinal in words, such as first, abbreviated when Abbreviate is true.

  • ToOrdinalWords(Number, Abbreviate)

ToProperCase

Capitalizes the first letter of each word in Text and lowers the rest.

  • ToProperCase(Text)

ToRomanNumeral

Writes Number as a Roman numeral.

  • ToRomanNumeral(Number)

ToSeconds

Returns a time span or a date and time range’s length in seconds, including any fraction.

  • ToSeconds(Date & Time Range)
  • ToSeconds(Time Span)

ToText

Converts Value to text.

  • ToText(Value)

ToTimeSpan

Converts Value to a time span, or reads one from Text using Format.

  • ToTimeSpan(Value)
  • ToTimeSpan(Text, Format)

ToUniversalTime

Converts a date and time, or a date and time range, to Coordinated Universal Time.

  • ToUniversalTime(Date & Time)
  • ToUniversalTime(Date & Time Range)

ToUpperCase

Converts Text to upper case.

  • ToUpperCase(Text)

ToWords

Writes Number in words, adding “and” when Add And is true and abbreviating when Abbreviate is true.

  • ToWords(Number, Add And, Abbreviate)

Trim

Removes spaces from the start and end of Text and reduces each run of spaces inside it to one.

  • Trim(Text)

TrimEnd

Removes white space from the end of Text.

  • TrimEnd(Text)

TrimEnds

Removes white space from the start and end of Text.

  • TrimEnds(Text)

TrimStart

Removes white space from the start of Text.

  • TrimStart(Text)

Truncate

Drops the fractional part of Number.

  • Truncate(Number)

TruncateEnd

Returns the first Length characters of Text, or the first character when no length is given.

  • TruncateEnd(Text, Length)
  • TruncateEnd(Text)

TruncateStart

Returns the last Length characters of Text, or the last character when no length is given.

  • TruncateStart(Text, Length)
  • TruncateStart(Text)

Value

Returns the value of Measure for each entry, taking the first of the entities related to it, or of every entity in Scope, counting only those where Condition is true when it is given.

  • Value(Measure)
  • Value(Measure, Condition)
  • Value(Scope, Measure)
  • Value(Scope, Measure, Condition)

VariancePopulation

Returns the variance of Measure, treating the values as the whole population, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • VariancePopulation(Measure)
  • VariancePopulation(Measure, Condition)
  • VariancePopulation(Scope, Measure)
  • VariancePopulation(Scope, Measure, Condition)

VarianceSample

Returns the variance of Measure, treating the values as a sample, among the entities related to each entry, or every entity in Scope, counting only those where Condition is true when it is given.

  • VarianceSample(Measure)
  • VarianceSample(Measure, Condition)
  • VarianceSample(Scope, Measure)
  • VarianceSample(Scope, Measure, Condition)

WeekOfYear

Returns the week number of a date and time in the workspace time zone, using the workspace culture’s rule unless Return Type chooses another.

  • WeekOfYear(Date & Time)
  • WeekOfYear(Date & Time, Return Type)

WeekStart

Returns the start of the week containing a date and time, using the workspace culture’s first day of the week.

  • WeekStart(Date & Time)

WorkDays

Counts the work days from Start to End, including both, skipping weekends (Saturday and Sunday unless Weekend says otherwise, as a code or seven 0s and 1s starting with Monday) and the Holiday Date of each entity in Holidays.

  • WorkDays(Start, End)
  • WorkDays(Start, End, Weekend)
  • WorkDays(Start, End, Holidays, Holiday Date)
  • WorkDays(Start, End, Weekend, Holidays, Holiday Date)

Xnor

Yes when Condition and Other Condition are both true or both false.

  • Xnor(Condition, Other Condition)

Xor

Yes when exactly one of Condition and Other Condition is true.

  • Xor(Condition, Other Condition)

Year

Returns the year of a date and time in the workspace time zone.

  • Year(Date & Time)

YearFraction

Returns the fraction of a year from Start to End, counted by the day-count convention Basis (0 to 4).

  • YearFraction(Start, End)
  • YearFraction(Start, End, Basis)

Didn’t find what you need? Speak with a specialist.