Help Administrators

Report custom column formula usage examples

Custom columns are report fields derived from user-defined formulas. You can use them for many things. These are just some examples of how they can be used.

Example #1: Alerting managers when projects exceed hours estimates

Say you want to alert managers when projects exceed hours estimates.

To do this, you could set up a custom field called Alert in a report based on the Project template, that uses this formula:

IF(TotalActualHours/ProjectEstimatedHours>1.3,"RED",IF(TotalActualHours/ProjectEstimatedHours>1,"YELLOW",""))

Then, this column will show YELLOW if the project hours exceed the estimate, and RED if hours are more than 130% of the estimate.

Example #2: Converting decimal hour format to HH:MM format

By default, Replicon displays time in decimal hour format (7.50). The user can change this the HH:MM format (7:30) via their user settings. But, changing this preference doesn't change how hours display in reports.

To convert time from decimal to HH:MM format, use the following formula in Microsoft Excel or Google Sheets:

=TEXT(C6/24, "h:mm")

Or, create a custom column in a report. The formula you'll use depends on which report you're adding it to:

Left(NumberToText(Hours), Len(NumberToText(Hours))-3) +"h"+ " " +
Left(NumberToText((Hours-Floor(Hours))*60),
Len(NumberToText((Hours-Floor(Hours))*60))-3)+"m"

You can use this formula with any hours field (e.g. Time Off Hours, Regular Hours, etc), just substitute the correct field name in place of Hours.

Example #3: Calculating project revenue

Revenue is typically calculated as:

= Sales - Cost of production 

In Replicon, revenue can be calculated using the following custom formula:

= Sum for all project team members of (Hourly Cost*Total Hours)

Or, if you are including expenses in the calculation:

= Sum for all project team members of (Hourly Cost * Total Hours) + Expense Amount

Example #4: Viewing timesheet hours approved on time and approved late

A company follows a semi-monthly timesheet period for all the employees.

The first timesheet for a given month is normally approved within the same month and the second timesheet for the month may or may not be approved in the same month.

If the second timesheet is not approved in the same month, the company would like to run a report which shows for all the users:

  • Timesheet hours approved on time (i.e. ones approved within the same month), and
  • Timesheet hours approved late (i.e. ones approved in the following month)

The company can report on this data by creating two custom columns in a report, like one based on the Time Entry Details template:

Approved on time:
If((Month(ApprovalDateTime)=Month(TimesheetEndDate),Hours,0))

Not approved on time:
If((Month(ApprovalDateTime)!=Month(TimesheetEndDate),Hours,0))

This formula should only be used for timesheets with one approver. If there are multiple approvers, you could create a date-type timesheet-level custom field, called something like ManualApprovalDate. Then, have one of the approvers manually fill out this field when they approve the timesheet. You could have to update the formula used to sort timesheet hours like this:

Approved on time:
If((Month(ManualApprovalDate)=Month(Timesheetperiodenddate),Hours,0))

Not approved on time:

If((Month(ManualApprovalDate)!=Month(Timesheetperiodenddate),Hours,0))

Related links

Creating custom columns using formulas
Operators and functions for use in custom columns
Using Replicon reports (video)
Using Replicon reports
Reports Field Matrix

Start a free Replicon trial today based on your business needs

Start Free Trial