Operators and functions for use in custom columns

+

Addition

-

Subtraction

*

Multiplication

/

Division

(...)

Parentheses

=

Two values are equal

<

Less than

>

Greater than

<=

Less than or equal

>=

Greater than or equal

!=

Not equal

Abs(x)

Absolute value

Mod(x,y)

Modulo (remainder of x/y). Modulo is always positive.

Max(x,y)

Whichever value is larger

Min(x,y)

Whichever value is smaller

Pow(x,y)

x to the power of y

Ceiling(x)

Rounds towards infinity: 1.1 → 2, -1.1 → -1

Floor(x)

Rounds towards -infinity: 1.1 → 1, -1.1 → -2

RoundAwayFromZero(x)

Rounds away from zero: 1.1 → 2, -1.1 → -2

RoundTowardsZero(x)

Rounds towards zero: 1.1 → 1, -1.1 → -1

RoundHalfTowardsZero(x)

Rounds to nearest, ties going towards zero: 1.5 → 1, -1.5 → -1

RoundHalfAwayFromZero(x)

Rounds to nearest, ties going away from zero: 1.5 → 2, -1.5 → -2

RoundHalfEven(x)

Rounds to nearest, ties going to nearest even integer: 1.5 → 2, 2.5 → 2

+

Concatenate (combine) two strings

=

Compare two values for equality²

!=

Compare two values for non-equality

Len(TextField)

Displays the number of characters in a text string

Left(TextField, length)

Displays the first length characters from a text string

Mid(TextField, start, length)

Displays characters from a text string, beginning with the start position, length characters long

Right(TextField, length)

Displays the last length characters from a text string

Find(TextField, "sub")

Displays the location from the left (represented by an integer) of sub in TextField. If sub is not found, -1 displays.

RFind(TextField, "sub")

Displays the location from the right (represented by an integer) of sub in TextField. If sub is not found, -1 displays.

IsEmpty(TextField)

Displays whether or not a field is empty. If the field has a value, this field displays 0. If the field is empty, this field is blank.

IsNullOrEmpty(TextField)

Displays whether a field is empty or null. If the field is empty or null, this field displays 1 (true result). If the field has a value, this field displays 0 (false result).

&&

Logical AND operator. Indicates whether both operands are true.

||

Logical OR operator. Indicates whether either operand is true.

If(cond,true,false)

Logical IF statement: IF cond THEN true ELSE false

cond must be a numeric value or a logical comparison

true and false must be the same type, whether string or numeric

IsNull(variable)

Display whether the variable is:

• null (and can therefore not be used in calculations)

If the variable is null, the field displays 1 (true result)

• has a value (and therefore can be used in calculations)

If the variable has a value, field displays 0 (false result)

variable must be a name of another report column; any other value will yield a true result.

TextToNumber(s)

Converts a text string to a numeric value. The number can be used in numeric custom fields.

This function will only work with text string fields that are comprised of numerals (for example, EmployeeID).

NumberToText(s)

Converts a number to a string. The string can be used in text string custom fields.

You can specify the string format used in the output for this function by entering the format after the field name. For example:

• To output whole numbers as text with no decimals, you would type: NumberToText(columnname,"0.###")

• To output total hours as text with one decimal place, you would type: NumberToText(columnname,"0.0##")

Decimal values will not be rounded, regardless of the output format chosen. For example, if you choose "0.###", the number 5 will display as 5, while the number 5.35 will still display as 5.35.

=

Two dates or datetimes are equal

<

One date or datetime falls after another

>

One date or datetime falls before another

<=

One date or datetime falls before or is equal to another

>=

One date or datetime falls after or is equal to another

!=

Two dates or datetimes are not equal

Date(year, month, day)

Returns the date value

For example, enter Date(2009, 2, 3) in the formula to return February 3rd, 2009 in the field

Today()

Returns today's date

Day(date)

Returns the day of the month of the date entered

For example, if an employee started work on May 3, entering Day(UserStartDate) will return a value of 3 for that user

Month(date)

Returns the numeric month of the date entered

For example, if an employee's end date is September 3, entering Month(UserEndDate) will return a value of 9 for that user.

Year(date)

Returns the numeric year of the date entered

For example, if an employee's start date is September 3, 2006, entering Year(UserStartDate) will return a value of 2006 for that user

DayDiff(earlierdate,laterdate)³

Returns the number of days between the two dates you enter

For example, if an employee's start date is April 3, 2009, and you enter DayDiff(UserStartDate,Date(2009,4, 13)), the value 10 will be returned in the field

RecentAnniversary(date)⁴

Returns the date of most recent anniversary of date entered

For example, if today is August 11, 2009, entering RecentAnniversary(UserStartDate) for an employee who started on July 13, 2006 will return a value of July 13, 2009

AddDays(date, #ofdays)

Adds the #ofdays value to the date value, returning a new date

For example, if an employee's start date is December 3, 2008, and you enter AddMonths(UserStartDate,5), December, 2009 will be returned in the field

AddMonths(date, #ofmonths)⁵

Adds the #ofmonths value to the date value, returning a new date

For example, if an employee's start date is December 3, 2008, and you enter AddMonths(UserStartDate,3), February 3, 2009 will be returned in the field

DateTime

Converts date and time values, or a character string, into a DateTime value

Hour(datetime)

Returns the hour

Minute(datetime)

Returns the minute

Second(datetime)

Returns the second

HourDiff(earlierdatetime,laterdatetime)

Returns the number of hours between two DateTime values

MinuteDiff(earlierdatetime,laterdatetime)

Returns the number of minutes between two DateTime values

SecondDiff(earlierdatetime,laterdatetime)

Returns the number of seconds between two DateTime values

AddHours(datetime,#ofhours)

Returns a new DateTime that adds the specified number of hours to the value of this instance

AddMinutes(datetime,#ofminutes)

Returns a new DateTime that adds the specified number of minutes to the value of this instance

AddSeconds(datetime,#ofseconds)

Returns a new DateTime that adds the specified number of seconds to the value of this instance