TimeLive Help

Formula Column Syntax

Formula columns are derived from user-defined formulas. Users create these formulas by combining columns (fields) available in the report.

When might I need to create a custom column?

Custom columns are useful for creating such numeric fields as:

  • Values with non-standardized modes of calculation that are therefore not included in TimeLive as default columns (for example, overhead cost etc)
  • Calculations specific to your organization
  • Highly-specialized calculations

Numeric Formula Examples:

  • A formula for 10% Overhead cost in the [Detail Timesheet Report]: (Amount * 10 / 100)
  • A formula for calculating profit margin in the [Detail Timesheet Report]. (BillingRate – EmployeeRate) * TotalHours

Text Formula Examples:

  • To identify the location and their departments in a single field, you could combine both location and department by creating a formula like this: (AccountLocation+’-’ DepartmentName)

Numeric Operators:

Operator 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
% Modulus

Operators follow standard order of operations rules. For example:

  • 2 + 2 * 2 = 6, but (2 + 2) * 2 = 8
  • 2 + 2 / 2 = 3, but (2 + 2) / 2 = 2

Functions:

Functions Description Syntax Example
IIF Gets one of two values depending on the result of a logical expression Iif ( expr, truepart, falsepart ) Iif (billingrate>500,‘expensive’,‘dear’ )
LEN Gets the length of a string LEN ( expression ) Len (EmployeeName)
CONVERT Converts particular value to a specified .NET Framework Type. Convert(expression, type) Convert(total, ‘System.Int32’), Convert(id,‘System.String’)
SUBSTRING Gets a sub-string of a specified length, starting at a specified point in the string. SUBSTRING (expression, start, length ) SUBSTRING ( phone, 7, 8 )