Formula Help
Editions
This feature is available in Ultimate edition only.
Related Articles
Additional Options
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. 
Date  Returns the current date.  Getdate() 

DateAdd  Adds or subtracts some interval of time from a date or time.  DateAdd(interval, number, date)  Interval may be: 
DateDiff  Computes the difference between two dates.  DateDiff(interval, date1, date2)  Interval may be: 
DatePart  Returns part of a date.  DatePart(interval, date)  Interval may be: 
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 131.  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 023.  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. 
InString  Returns the character location where one string is found within another string.  CharIndex(expressionToFind ,expressionToSearch [,start_location]) 

Int  Returns the integer portion of a number, removing any decimal places.  Round(Floatvalue,0,1) 

IsDate  Returns True if the text is a date.  IsDate(text) 

IsNumeric  Returns True if the text is a number.  IsNumeric(text) 

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. 
Minute  Returns the minute of the hour. Possible return values are 059.  DatePart(mi,date) 

Month  Returns the month of the year. Possible return values are 112.  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 ) 

RTrim  Removes any space characters from the right side of the text.  RTrim(text) 

Second  Returns the second of the minute. Possible values are 059.  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 17.  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. 
+  Addition 
  Subtraction 
+  String Concatenation 