System StatusContact Support

# Formula Help

Editions

This feature is available in Ultimate edition only. Related Articles

Show Me ## Overview

Formulas are expressions that must contain data columns, and can include constants, functions, and operators.

• Columns are values that come from the data. Their names are are enclosed in square brackets, like [ShippedDate]. Depending on their data type, they may be used as text strings, numbers, and date/time values.

• Constants are simply specific fixed values that you enter into a Formula.

• Functions return values, usually computations based on columns and constants.

• Operators do arithmetic and logical comparisons. Logical comparisons must be used with Case statements.

Here are some examples:

[UnitPrice] * [Quantity]
Multiply two data columns, UnitPrice and Quantity, to make an ExtendedPrice column.

[UnitPrice] * .04
Multiply a data column by an constant value to calculate the tax applied to the price.

DateDiff("d", [OrderDate], [ShippedDate] )
Get the number of days from the order to the shipment.

DateDiff("w", [ShippedDate], Now )
Get the number of weekdays since the shipment date.

DateName( dw, ShippedDate )
Return the name of the day of the week of the shipment date.

[LastName] + ', ' + [FirstName]
Concatenate columns and strings together. This might return: Smith, John

Upper( [LastName] + ', ' + [FirstName] )
Convert to upper case. This might return: SMITH, JOHN

### Functions

Functions accept some number of values and return a single value.

Function Name

Description

Syntax

Notes

Abs

Returns the absolute value of a number.

Abs(number)

The absolute value of -5 is 5.

Case

Returns one value or another, depending on if the expression is evaluates to True or False./td>

Case variable When value Then return value Else other value End

variable has a values that will be compared.
value is something that may be matched in the variable.
return value is returned when the variable and value match.
other value is returned when there are no matches.

For example,
Case [Color]
When 'pink' Then 'red'
When 'amber' Then 'yellow'
Else 'gray'
End

Date

Returns the current date.

Getdate()

Adds or subtracts some interval of time from a date or time.

Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=weekday
ww=week of year
hh=hour
n=minute
s=second

DateDiff

Computes the difference between two dates.

DateDiff(interval, date1, date2)

Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=weekday
ww=week of year
hh=hour
n=minute
s=second

DatePart

Returns part of a date.

DatePart(interval, date)

Interval may be:
yyyy=year
q=quarter
m=month
y=day of year
d=day
w=weekday
ww=week of year
hh=hour
n=minute
s=second

DateSerial

Combines date parts together to make a date.

Cast(Cast(year AS varchar) + '-' + Cast(month AS varchar) + '-' + Cast(day AS varchar) AS DATETIME)

DateValue

Returns a date from a date string. The function can convert dates from many different formats.

Convert(DATETIME, date_string)

date_string is a string value which represents a date or date/time.

Day

Returns the day of the month. Possible return values are from 1-31.

Day(date)

FormatCurrency

Format a number value into currency.

'\$' + Convert(varchar(12), NumericValue, 1)

FormatDateTime

Formats a date.

Convert(varchar, date, 120)

"120" is the code that dictates the date format. More codes are available for different formats.

FormatNumber

Formats a number.

Convert(varchar(12), NumericValue, 1)

FormatPercent

Formats a number as a percentage.

Convert(varchar,Convert(Decimal(6,2), number)) + '%'

Hour

Returns the hour of the day. Possible return values are 0-23.

DatePart(hh, date)

Case

Returns one value or another, depending on if the expression is evaluates to True or False.

Case When Expression Then True Else False End

Expression is a formula that returns True or False.
For example, this would return "Blue", because 1 does not equal 2.
Case When 1=2 Then "Red" Else "Blue" End

InString

Returns the character location where one string is found within another string.

CharIndex(expressionToFind ,expressionToSearch [,start_location])

Returns the index value if the string is found, else returns 0.

Int

Returns the integer portion of a number, removing any decimal places.

Round(Floatvalue,0,1)

ex. ROUND(150.75, 0) = 151.00
ROUND(150.75, 0, 1) = 150.00

IsDate

Returns True if the text is a date.

IsDate(text)

Return 1 if True, else returns False

IsNumeric

Returns True if the text is a number.

IsNumeric(text)

Return 1 if True, else returns False

Lower

Converts all characters to lower case.

Lower(text)

Left

Returns the "length" number of characters from the left side of the input text.

Left(character_expression, integer_expression)

Len

Returns the number of characters in the text.

Len(text)

LTrim

Removes the space characters from the left side of the text.

LTrim(text)

Mid

Returns characters from the middle of the text.

Substring(text,start,length)

"start" is the first character to be returned. The first character is at position 1.

"length" is the number of characters to be returned.

Minute

Returns the minute of the hour. Possible return values are 0-59.

DatePart(mi,date)

Month

Returns the month of the year. Possible return values are 1-12.

Month(date)

MonthName

Returns the name of the month.

DateName(month,date)

Now

Returns the current date and time.

Getdate()

Replace

Searches textSearch for textFind, replacing it with the textReplaceWith value.

Replace(string1,string_to_replace,[replacement_string])

Right

Returns the "length" number of characters from the right side of the input text.

Right(str,len)

Rand

Returns a random number between 0 and 1.

Rand([seed])

Seed is an integer expression (tinyint, smallint, or int) that gives the seed value. This parameter is Optional.

Round

Returns a number rounded to a specified number of decimal places.

Round(number,length )

ex. ROUND(150.75, 0) = 151.00
ROUND(150.75, 0, 1) = 150.00

RTrim

Removes any space characters from the right side of the text.

RTrim(text)

Second

Returns the second of the minute. Possible values are 0-59.

DatePart(ss,date)

Sign

Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0.

Sign(number)

Space

Returns text consisting of the number of spaces.

Space (number)

Square

Returns the square of a number.

Square(number)

String

Returns text consisting of the character duplicated the number of times.

Replicate(text,count)

Reverse

Returns the text with the characters in reverse order.

Reverse(text)

TimeValue

Returns a time value from a time string. The function can convert dates from many different formats.

Convert(varchar(8),Convert(datetime,text),108)

Trim

Removes space characters from both the left and rights sides of the text.

LTrim(RTrim(text))

Upper

Converts all characters to upper case.

Upper(text)

Weekday

Returns the number of the day of the week. Possible return values are 1-7.

DatePart(wk,date)

WeekdayName

Returns the name of the day corresponding to the weekday number.

DateName(dw,date)

Year

Returns the number of the year of the specified date.

Year(date)

In the Syntax column, parameters in square brackets are optional.

### Operators

Operators do arithmetic and logical comparisons

Operator Description
- Negation
^ Exponentiation: Power( m, n ). Function returns m raised to the nth power.
* Multiplication
/ Division
% Modulus: m%n.