 # Formula Column Syntax You are here:
• 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 OfficeTimer 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 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…
Subtraction
* Multiplication
/ Division
(…) Parentheses
= Two values are equal
< Less than
> Greater than
<= Less than or equal
>= Greater than are 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 )