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.

Article Contents

## Formulas

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

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

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. |

## Other Notes of Interest

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.