Formula Help
Editions
This feature is available in Ultimate edition only.
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 