Applies to versions: 1.6, 1.8

Supported Functions for Advanced Expressions

To use expressions in Custom Payroll and Custom Report:

Select "Expression" from the drop down
Put the expression in the Default Column
All expressions begin with an "=" (equals sign). For example =utcdate("Y") would show the current 4-digit year.
To use a variable provided by the report, enclose the variable's name in square brackets (for example, [variable_name]). See the documentation for each report for a list of available variables.
When a row is summarized (the "Total (sum)" or other summarize function is selected for at least one column in the report) it's possible to have an expression that evaluates for each row and a separate expression that evaluates on the summarized row. In the summarized row, access columns with [n] where n is the column number. This is useful, for example, when you need to compare summarized values, or when you want to show something only once, such as the total worked hours in a year. To separate the per-row and summarized-row expressions, use this format:
=[row expression]
--summarizeExpression--
=[summarized row expression]
Example: Total hours worked in the current calendar year, in column 3. Note that the summarized expression only sees the summarized rows, so it cannot directly access [usersID] needed by findWorkedTime. So, the first line puts the employee's ID in the column so it can be used as the first argument to findWorkedTime:
=[usersID]
--summarizeExpression--
=round(findWorkedTime([3],strtoutctime(utcdate("Y-01-01")))/3600,2)

Function	Effect	Example Expression	Example Return
if(condition, trueValue, falseValue)	If the condition is true, returns trueValue, otherwise returns falseValue. You may also use IIF() or iif() instead of if().	if([x] > 0, "positive", "negative")	"negative" (if x is -2)
round(number[,precision])	Returns the number rounded to decimal points specified in precision. Negative precision rounds ahead of the decimal. You may also use ROUND() instead of round().	round(5.2345,2) round(123445,-2)	5.23 123400
floor(number)	Returns the largest whole number less than number (rounds down). You may also use FLOOR() instead of floor().	floor(2.3)	2
ceil(number)	Returns the smallest whole number greater than number (rounds up). You may also use CEIL() instead of ceil().	ceil(2.3)	3
empty(variable)	Returns true if the variable does not exist or is equal to zero, false, or an empty string, otherwise returns false. You may also use ISBLANK() instead of empty().	empty([x])	1 (if x is -2)
min(x, y)	Returns the smaller of x and y. You may also use MIN() instead of min().	min(2, 1)	1
max(x, y)	Returns the larger of x and y. You may also use MAX() instead of max().	max(2, 1)	2
str_replace(before, after, subject)	Replaces all occurrences of before that appear in subject with after.	str_replace("a", "A", "bad")	"bAd"
substr(subject, start, length)	Returns length characters out of subject starting at start. Note that the first character is 0, not 1.	substr("abcd", 1, 2)	"bc"
stristr(haystack, needle)	Returns all of haystack from first occurrence of needle to the end. Case insensitive.	stristr("abcdefg", "cd")	"cdefg"
strstr(haystack, needle)	Returns all of haystack from first occurrence of needle to the end. Case sensitive.	stristr("abcdefg", "cd")	"cdefg"
stripos(haystack, needle, search_start_position)	Returns the numeric position of the first occurrence of needle in haystack starting at search_start_position. Case insensitive. Needle may be one or more characters. Search_start_position is optional.	stripos("abcd abcd", "bc", 4)	"6"
strpos(haystack, needle, search_start_position)	Returns the numeric position of the first occurrence of needle in haystack starting at search_start_position. Case sensitive. Needle may be one or more characters. Search_start_position is optional.	stripos("abcd abcd", "bc", 4)	"6"
strlen(text)	Returns the numeric length of text	strlen("abcd")	"4"
getfield(text,start_marker,end_marker)	Returns the section of text between start_marker,end_marker. Case insensitive.	getfield("$abcd~^efgh~","$","~")	"abcd"
splitfield(text,delimiter[,after])	Returns the section of text before or after the first occurence of delimiter. Optionally, set after to "1" to get the section after. Case insensitive.	splitfield("abcd~efgh","~",1)	"efgh"
in_list(needle,[haystack 1],[haystack 2],[haystack n])	Returns true or false if the needle is found in the list of items passed in as haystack. Any number of items can be passed in after the needle to make up the haystack. If only the needle is passed in then the expression will return false.	in_list([payrollEventType],"Standardtime","Overtime")	False
utcdate(format[, timestamp][, employee ID])	Formats the timestamp based on the format string (compatible with PHP's date() function) in the employee's time zone, or the company's time zone if the employee ID is omitted. If the timestamp is omitted, the current time is used (useful when you just want the current year or month).	utcdate("m/d/Y", 1230832800)	"01/01/2009" (for US time zones)
strtoutctime(datetime string[, employee ID])	Returns a UTC timestamp for the provided time in the employee's time zone, or the company's time zone if the employee ID is omitted.	strtoutctime("2010-01-01 12:15:00 PM")	" 1262366100"
adjustRangeDstUser(start timestamp,seconds[,employee ID])	Returns a UTC timestamp adjusted by the selected number of seconds for the specified employee. If the employee is not specified, the timezone of the division is used. If the adjustment spans a DST transition, the transition is added/subtracted automatically.	adjustRangeDstUser(1230832800,86400,[usersID])	"123919200"
sprintf("format string"[, data,data])	Formats the data into the format string (compatible with PHP's sprinf() function).	sprintf("%s %s",[nameFirst],[nameLast])	"Bob Smith"
custom(custom key name,custom value name, custom key,division id)	Returns the "custom value" matching the "custom key" for the custom field named "custom key name" of the type "custom value name". Optional divisionID can be provided to select data for the employee's division (use 0 for all divisions). In the example, a custom data table connects zip codes to territories. The function finds the territory of an employee, based on their zip code.	custom("Zip Code","Territory",[zip],[divisionID])	"Southern"
findLastClock(usersID,startTimeStamp, endTimeStamp,clockType)	Returns the timestamp of the last clockType (either "in" or "out") for the employee with usersID that occurs between startTimeStamp and endTimeStamp inclusive. In the example, the timestamp of the last clockout up to a hour prior to the current clock in will be returned for the employee on the row.	findLastClock([usersID],[adjTimeIn]-3600,[adjTimeIn],"out")	"1265222010"
findIn(position ,startTimeStamp, endTimeStamp,[usersID] )	Returns the timestamp of a clock In during the time range. The timestamp returned is determined by the value of position. If position is 1 then we return the first In. A position of 2 would return the second In. Negative values for position are also supported. To find the last clock In during the range use a position of -1. A position of -2 would give the second to last clock In and so on. usersID is an optional parameter that limits the scope of the search to just one user. If there are no events in the range for the user the expression returns -1.	findIn(1,1230832800,123919200,[usersID])	"123913600"
findOut(count,startTimeStamp, endTimeStamp ,[usersID] )	Returns the timestamp of a clock Out during the time range. The timestamp returned is determined by the value of position. If position is 1 then we return the first Out. A position of 2 would return the second Out. Negative values for position are also supported. To find the last clock Out during the range use a position of -1. A position of -2 would give the second to last clock Out and so on. usersID is an optional parameter that limits the scope of the search to just one user. If there are no events in the range for the user the expression returns -1.	findOut(-1,1230832800,123919200,[usersID])	"123917200"
findMaxBreak(usersID,startTimeStamp, endTimeStamp)	Returns the duration in seconds of the largest range of non-worked time between two timestamps.	findMaxBreak([usersID],[adjTimeIn],[adjTimeIn]+18000)	"1800"
schedule(usersID,dayOfTimeStamp, schedulePoint[, format])	Returns the format time (or timestamp if not specified) of the schedule point specified by schedulePoint (either "firstin" "firstout" "secondin" or "secondout") for the employee with usersID that occurs on the calendar day that includes the timestamp dayOfTimeStamp in the employee's time zone. In the example, the timestamp of the first scheduled clockin time will be returned for the employee on the row. If no schedule is present, "No Schedule" will be returned instead.	schedule([usersID],[adjTimeIn],"firstin","Y-m-d g:i:s a")	"2010-04-20 8:00:00 am"
calendarDayMatch(begin,day, occurs[,daybreak][,usersID])	Starting at "begin" (timestamp), consider a calendar day that is "day" (integer) days from then in the context of employee with "usersID." Returns true if the event at time "occurs" falls on on the day when considering "daybreak" (HHMM) to be the start/stop of a day. If daybreak is not specified, it is considered "0000" or midnight. If usersID is not specified, timezone is consitered to be the company's time zone. Example: If a report contains a column for each day, and time needs to be put into columns based on schedules that start at 9AM each day, call as shown in the example to identify time on the 3nd day into the report (2 days past the first).	calendarDayMatch([begTimestamp],2, [occurs],"0900",[usersID])	True
findWorkedTime(usersID[,startTimeStamp][, endTimeStamp])	Returns the number of seconds of worked time between the startTimeStamp and endTimeStamp. If set to 0 or left blank, startTimeStamp and endTimeStamp are considered unbounded.	findWorkedTime([usersID],0,0)	"86400"
. (dot)	Joins two results or strings together.	"First: ".[nameFirst]	"First: Bob"