The Formula Help button in the Report Center redirects here. This page contains a guide to adding and using formulas. See *Add a new column from a formula *in Create A Report for how to use the information on this page.

Formulas are expressions made up of columns, constants, functions, and operators.

Columns are values that come from the data. Column titles are shown inside square brackets, for example, [ShippedDate]. Depending on their data type, they may be used as text strings, numbers, and date/time values.

Constants are specific fixed values that you enter into a formula.

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

Operators perform arithmetic and logical comparisons.

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 a 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 weeks since the shipment date.

`WeekdayName( Weekday( [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

`UCase( [LastName] + ", " + [FirstName] )`

Convert to upper case. This might return: SMITH, JOHN

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

VBScript evaluates formulas. Click here for additional information and some more advanced VBScript functions.

Function Name |
Description |
Syntax |
Notes |

Abs | Returns the absolute value of a number. | Abs(number) | The absolute value of -5 is 5. |

Date | Returns the current date. | Date() | |

DateAdd | Adds or subtracts some interval of time from a date or time. | DateAdd(interval, number, date) | Interval may be: yyyy=year q=quarter m=month y=day of year d=day w=week ww=week of year h=hour n=minute s=secondIntervals must be quoted. |

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=week ww=week of year h=hour n=minute s=secondIntervals must be quoted. |

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=week ww=week of year h=hour n=minute s=secondIntervals must be quoted. |

DateSerial | Combines date parts together to make a date. | DateSerial(year, month, day) | |

DateValue | Returns a date from a date string. The function can convert dates from many different formats. | DateValue(text) | `DateValue("April 2, 1962")` |

Day | Returns the day of the month. Possible return values are from 1-31. | Day(date) | |

Exp | Returns e raised to a power. e is the base of natural logarithms, called the antilogarithm. |
Exp(number) | |

FormatCurrency | Format a number value into currency. | FormatCurrency(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) | |

FormatDateTime | Formats a date. | FormatDateTime(date[, NamedFormat]) | Named Format may be vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, or vbLongTime |

FormatNumber | Formats a number. | FormatNumber(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) | |

FormatPercent | Formats a number as a percentage. | FormatPercent(number [,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]]) | |

Hour | Returns the hour of the day. Possible return values are 0-23. | Hour(time) | |

IIF | Returns one value or another, depending on if the expression is evaluates to True or False. | IIF(expression,true-value,false-value) | Expression is a formula that returns True or False. For example, this would return Blue because 1 does not equal 2.`IIF(1=2,"Red","Blue")` |

InStr | Returns the character location where one string is found within another string. | InStr([start, ]string1, string2[, compare]) | The first characters is at position 1. Instr() returns 0 when the string is not found.
Set compare to 1 for case-insensitive searches. |

InStrRev | Returns the character location where one string is found within another string. The search is started from the end of the string rather than the beginning. | InStrRev(string1, string2[, start[, compare]]) | |

Int | Returns the integer portion of a number, removing any decimal places. | Int(number) | |

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

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

LCase | Converts all characters to lower case. | LCase(text) | |

Left | Returns the length number of characters from the left side of the input text. |
Left(text, length) | |

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. | Mid(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. | Minute(time) | |

Month | Returns the month of the year. Possible return values are 1-12. | Month(date) | |

MonthName | Returns the name of the month. | MonthName(month[, abbreviate]) | Set abbreviate to True for an abbreviated month name. |

Now | Returns the current date and time. | Now() | |

Replace | Searches textSearch for textFind, replacing it with the textReplaceWith value. | Replace(textSearch, textFind, textReplaceWith[, start[, count[, compare]]]) | start is the starting character position to be searched.
Set
produces |

Right | Returns the length number of characters from the right side of the input text. |
Right(text, length) | |

Rnd | Returns a random number between 0 and 1. | Rnd[(number)] | |

Round | Returns a number rounded to a specified number of decimal places. | Round(expression[, numdecimalplaces]) | |

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. | Second(time) | |

Sgn | Returns -1 if the number is negative. Returns 1 if the number is positive. Returns 0 if the number is 0. | Sgn(number) | |

Space | Returns text consisting of the number of spaces. | Space(number) | |

Sqr | Returns the square root of a number. | Sqr(number) | |

String | Returns text consisting of the character duplicated the number of times. | String(number, character) | |

StrReverse | Returns the text with the characters in reverse order. | StrReverse(text) | |

TimeValue | Returns a time value from a time string. The function can convert dates from many different formats. | TimeValue(time) | |

Trim | Removes space characters from both the left and rights sides of the text. | Trim(text) | |

UCase | Converts all characters to uppercase. | UCase(text) | |

Weekday | Returns the number of the day of the week. Possible return values are 1-7. | Weekday(date, [firstdayofweek]) | |

WeekdayName | Returns the name of the day corresponding to the weekday number. | WeekdayName(numberWeekday, abbreviate, firstdayofweek) | |

Year | Returns the number of the year of the specified date. | Year(date) |

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

Operators do arithmetic and logical comparisons. Formulas are evaluated through VBScript. Click here for more information about VBScript operators.

Operator |
Description |

– | Negation |

^ | Exponentiation |

* | Multiplication |

/ | Division |

Integer Division | |

Mod | Modulus (Remainder) |

+ | Addition |

– | Subtraction |

+ | String Concatenation |

= | Equals |

<> | Not Equals |

< | Less Than |

> | Greater Than |

<= | Less Than or Equal To |

>= | Greater Than or Equal To |

Not | Logical Not |

And | Logical And |

Or | Logical Or |

( and ) | Parenthesis to manage precedence. |

You may represent true and false values as True and False.

If a Formula has an error, it will appear as either an empty value or a *?*. Check the formula syntax to ensure it is correct.