Operators and functions for use in custom columns

Custom columns are report fields that are derived from user-defined formulas. Users create these formulas by combining columns (or fields) available in the report with operators and functions.

The operators and functions listed in the sections below are supported for use in custom columns.

Numeric operators

Operator1

Use this operator to specify…

+

Addition

-

Subtraction

*

Multiplication

/

Division

(...)

Parentheses

=

Two values are equal

<

Less than

>

Greater than

<=

Less than or equal

>=

Greater than or equal

!=

Not equal

Numeric functions

Function

Use this function to specify...

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

Text string operators

Operator1

Use this operator to…

+

Concatenate (combine) two strings

=

Compare two values for equality2

!=

Compare two values for non-equality

Text string functions

Function

This function…

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 operators and functions

Logical functions and operators can be used in string or numeric formulas.

Operators

Operator

Use this operator to specify the...

&&

Logical AND operator. Indicates whether both operands are true.

||

Logical OR operator. Indicates whether either operand is true.

Functions

Function

Use this function to…

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.

Conversions

String numeric conversions are used to convert text to numbers and vice versa.

Function

Use this function to…

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.

DateTimeToDate Converts DateTime to a date value, dropping the time component
DateToDateTime Converts a date string value to a DateTime string value
TextToDate Converts text to a date value
TextToDateTime Converts text to a DateTime value

Date and DateTime operators

Date operators are used to compare dates.

Operator

Use this operator to determine whether...

=

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 and DateTime functions

Date functions are used to display dates and carry out calculations using dates. For example, you can use date functions to:

  • Add days or months to a date field and return a new date
  • Return a numeric value for a date's month, day, or year, or
  • Return the latest anniversary of a user's start (for use, for example, in calculating payout owing for accrued time off)

You can use any of the date functions that return numeric values in numeric formulas.

Function

This function…

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

This function cannot be used with DateTime values. 

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)3

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)4

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)5

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 functions

Function

This function…

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




1 Operators follow standard order of operations rules. For example: 2 + 2 * 2 = 6, but (2 + 2) * 2 = 8

2 String comparisons are not case-sensitive.

3 If the laterdate value entered is falls before the earlierdate value, the result will be a negative value.

4 If you enter February 29th, may return February 28th of the most recent year. If you enter a future date, the function will still return a past value.

5 Returns the last day of the month if the day of the month does not exist in the new month (for example, if the initial date is the 31st, but there are only 30 days in the returned month