Skip to main content

Simplifying and Managing Report Formulas in VeriClock

Reayah
Updated

Introduction to Custom Formulas in Reports

Custom formulas are a powerful tool you can use to get even more out of your VeriClock reports, without needing any coding experience.

You might want to use a custom formula if you need to:

  • Show job hours as a separate column.
  • Calculate mileage reimbursement for employees based on the distance driven while clocked in.
  • Track the cost of hours being billed to a client.
  • Or customize your reports in other ways to match how you run your business.

This guide will walk you through how to create and apply formulas step-by-step, using real examples to help you follow along easily. Custom Formulas in VeriClock Reports let you transform raw time data into powerful, business-ready insights—no coding required. Using built-in fields, math operators, and conditional logic, you can calculate mileage reimbursements, separate billable vs. non-billable hours, split regular and overtime costs, apply role-based pay rates, and customize time displays for payroll, billing, and compliance. 

With support for rounding, nested conditions, job and service item logic, and time zone adjustments, custom formulas give admins full control to tailor reports exactly to how their organization tracks labor, costs, and performance.

In your VeriClock account, navigate to the tabs Reports>Reports>click the blue Customize button>scroll to the bottom of the Displayed Fields box>To the right of the Formula option, click the blue Create button.

Click on 'Formula Available Fields' to see which fields you can use. Copy and paste them into your formula to avoid typos.

Available Fields

Here are some of the most commonly used fields when creating custom formulas:

  • empInfo(1) – Pulls info from an employee’s profile under Employees > Edit > Other tab. Use this to display fixed text or numeric values related to the employee. There are 5 info fields available in the employee profile.
  • jobInfo(1) – Similar to empInfo, this pulls job-specific info from Jobs > Edit > Other tab. Helpful for showing job-related details or costs.
  • serviceItemInfo(1) – Used to track extra details or costs related to the selected service item (like tasks, materials, or equipment) from Settings> Service Items>Other tab. It can also be used in formulas with custom fields.
  • job('code') == 2 – Use this to reference a specific job. Replace “2” with your job code, or replace the = = with a greater than or less than sign to include multiple job codes in your formula.
  • serviceItem('code') == 2 – Same idea as above, but for service items. Replace “2” with your service item code, or replace the = = with a greater than or less than sign to include multiple service item codes in your formula.
  • customField("Km's Driven") – Pulls info from a custom field filled out during clock-in or clock-out from Settings>Custom Fields. Replace the field name with your exact custom field name. Can be used to calculate values or match selected list items.
  • punchHours() – Returns total hours from a clock event. You can specify types like punchHours("regular") or punchHours("Overtime 1").
  • wage("regular") or wage("Overtime 1") – Used to access the employee's pay rate for the selected payroll item from Settings>Payroll Items. Change the name in quotes to match your payroll item.
  • __row("cost_sum") – Pulls the total cost shown in the report’s Cost column.
  • " " – Quotation marks can be used to show a blank field or to display specific text or a number inside them (e.g., "full time" will display the words full time). Useful for templates or reports that need a placeholder.

Tip: Common math equations that can be used:  - + * or /

Note: After creating a formula, be sure to save your template. If you leave the page or switch templates without saving your changes, including any new formulas, any unsaved changes will be lost.

Example Formulas:

  • Example:  punchHours()*serviceItemInfo(1)   ← Calculates the cost of the service or task based on hours worked.
  • Example:  punchHours()+empInfo(1)   ← Adds extra pay or value to the employee's hours (e.g., for a bonus or premium).
  • Example:  punchHours()*jobInfo(1)   ← Calculates what you charge the client for the employee's time (different from what you pay the employee).
  • Example:  customField("KM driven per shift")*.45   ← Calculates mileage reimbursement at $0.45 per kilometer.
  • Example:  empInfo(4)*punchHours()   ← Uses a custom rate stored in the employee's profile (Other Info (4) to calculate pay, instead of their regular wage.

 

Note: Formula names can only include letters and numbers - no symbols or punctuation.

Note: Spaces in formulas don't affect how they work. Feel free to add extra spacing to make them easier to read.

Note: "" and ' ' are treated the same in report formulas—just be sure to use the same one at the beginning and end to stay consistent.

Rounding a Number to the Nearest Decimal Place

To round a number in your formula to two decimal places, use:

round((your formula), 2)

  • Example:  round((empInfo(1)*punchHours()),2)

Tip: It’s a good idea to round your results when using multiplication or division, since those can create long decimal numbers.

Showing Regular and Overtime Costs Separately

The cost column is a combination of both regular hours * the regular wage and overtime hours * the overtime wage. The regular and overtime costs can be isolated by using these two formulas, and each formula can be displayed as a separate column.

By default, the cost column combines both regular and overtime hours by the wage paid for those hours. If you want to show them in separate columns, use these formulas:

Example Regular Cost:

  • Example:  round(punchHours("regular")*wage("regular"),2)

Example Overtime Cost:

  • Example:  round(punchHours("Overtime 1")*wage("Overtime 1"),2)

________________________________________________________________________________________________________________

Using Conditional Formulas

Conditional Statement General Format:

if field == value ? result_if_true : result_if_false

  • Example:  customField("Km's Driven") >99 ? customField("Km's Driven")*.01 : 0

Explanation: This formula works like a simple "if this, then that" rule. It checks the number an employee enters into the custom field called "Km's Driven" when they clock in or out.

If the number is more than 100, the formula multiplies it by 0.01 (which means 1 hour of pay for every 100 km driven).
If the number is 100 or less, it just shows a 0 (no extra pay).

This is a great way to automatically pay employees for long-distance driving while skipping small amounts.

Conditional Formula Equation Options:

  • ==  ← is equal to.
  • !=  ← is not equal to.
  • >  ← is more than.
  • <  ← is less than.
  • >=  ← is more than or equal to.
  • <=  ← is less than or equal to.
  • and  ← both conditions must be true.
  • or  ← only one of the conditions needs to be true.
  • "Error"  ← shows an error message if the formula conditions aren’t met.

Additional Conditional Statement Formulas:

  • Example:  customField("Per Diem") == 1 ? punchHours() : 0 

Explanation: If the employee selects “1” in the Per Diem custom field, show their punch hours. If they don’t, show 0.

  • Example:  serviceItem('code') == 17 or serviceItem('code') == 15 ? 0 : punchHours() 

Explanation: If the service item used in the clock event is code 17 or 15, show 0. Otherwise, show the punch hours.

  • Example:  job('code') == 10 ? 50 : 60   ← A flat rate can also be used.

Explanation: This sets a flat rate. If job code 10 is selected, display 50. If any other job is selected, display 60.

  • Example:  customField("Material cost") > 0 ? customField("Material cost") : "Error"

Explanation: If the material cost custom field has a value greater than 0, show that value. If not, show "Error".

 

Separate billable from non-billable hours

  • Example:  job('code') > 100 ? punchHours() : 0   ← Non Billable Hours column.

Explanation: In this example, all non-billable hours would have job codes lower than 100, which would then display as a separate column from billable hours. A second formula could be added for a billable hours column. The punch hours can also be broken down into regular and overtime for both billable and non billable hours.


Display job hours in a separate column (This is commonly used for tracking paid time off jobs, or holiday hours)

  • Example:  job('code') == 2 ? punchHours() : 0

Explanation: If job code 2 is selected, show the punch hours. If not, show 0.

  • Example:  job('code') == 2 ? 0 : punchHours()

Explanation: This flips the logic. If job code 2 is selected, show 0. Otherwise, show the punch hours.

Tip:  Use punchHours("regular") or punchHours("Overtime 1") if you want to split the hours by time type.

Tip: You can also use service item codes instead of job codes—see “Available Fields” above.

 

Remove job hours from the Regular Hours column

If you're displaying hours for specific jobs or service items in their own columns, you may also want to remove them from the regular hours column.

  • Example:  punchHours("regular") > 0 and (job('code') == 7 or job('code') == 8 or job('code') == 9) ? 0 : punchHours("regular")

Explanation:  This hides regular hours for jobs 7, 8, or 9.
The first part, punchHours("regular") > 0 ensures the formula only applies if the hours are marked as regular. If these jobs are marked as overtime exempt, you can remove that part.
If the hours are for one of those jobs, display 0. Otherwise, display the regular hours. This is useful when regular hours for these jobs are already being shown in a separate column.

________________________________________________________________________________________________________________

Nested Conditional Formulas

Sometimes you’ll want your formula to check for more than one condition. These are called nested formulas, and while they can look a bit complex, they follow a clear structure.

Nested Conditional Format:

round(punchHours('Holiday Worked') > 0 ? (BIG_FORMULA) : 0, 2)

BIG_FORMULA IS:  serviceItem('code') == 1 ? (B) : (A)

B is:  jobInfo(1)*punchHours('Holiday Worked')

A is:  jobInfo(2)*punchHours('Holiday Worked')

 

Final Formula Equation:

  • Example:  round(punchHours('Holiday Worked') > 0 ? (serviceItem('code') == 1 ? jobInfo(1) * punchHours('Holiday Worked') : jobInfo(2) * punchHours('Holiday Worked')) : 0,2)

Nested Formulas Using List Custom Fields

  • Example:  round(punchHours() * (customField("Role") == 1 ? 30 : (customField("Role") == 2 ? 35 : (customField("Role") == 3 ? 40 : (customField("Role") == 4 ? 45 : (customField("Role") == 5 ? 50 : (customField("Role") == 6 ? 55 : (customField("Role") == 7 ? 50 : (customField("Role") == 8 ? 65 : (customField("Role") == 9 ? 70 : (customField("Role") == 10 ? 75 : 0)))))))))),2)

Explanation: This formula applies different pay rates depending on the role selected. Some employees may work in multiple roles, each with its own rate. List custom fields have a code assigned to each list item, and these codes can be the rate the employee selects. However, this formula is an alternative to hide those rates from employees. The formula is rounded to two decimal places, so the result displays as a dollar amount.

Tip: Every opening bracket ( needs a matching closing bracket ). That’s why there are so many at the end of the last example.

Tip: If your formula gets hard to read, try breaking it down line by line. Put each or else ( : ) part on its own line, so you can clearly match all the brackets.

  • Example:  customField("Role") == 1 ? 30 : (customField("Role") == 2 ? 35 : (customField("Role") == 3 ? 40 : (customField("Role") == 4 ? 45 : (customField("Role") == 5 ? 50 : (customField("Role") == 6 ? 55 : (customField("Role") == 7 ? 50 : (customField("Role") == 8 ? 65 : (customField("Role") == 9 ? 70 : (customField("Role") == 10 ? 75 : "Error")))))))))

Can be displayed as: 

customField("Role") == 1 ? 30 :

(customField("Role") == 2 ? 35 :

(customField("Role") == 3 ? 40 :

(customField("Role") == 4 ? 45 :

(customField("Role") == 5 ? 50 :

(customField("Role") == 6 ? 55 :

(customField("Role") == 7 ? 60 :

(customField("Role") == 8 ? 65 :

(customField("Role") == 9 ? 70 :

(customField("Role") == 10 ? 75 : 

"Error")))))))))

It's now easier to count the 9 opening and 9 closing brackets. Since spacing doesn't affect the formula, you can copy and paste either version into the Create Formula Column box—both will work.

________________________________________________________________________________________________________________

Time modification formulas

To change how the clock-in or clock-out time appears in your report, use this formula:

epocToDate(__row("clock_in_time"), ‘format’)

You might want to reformat the time to make it easier to read, match other report styles, or prepare your data for importing into another system.

 

The word 'format' in this formula epocToDate(__row("clock_in_time"), 'format')can be any format from this list:

  • YYYY:  ← 4-digit year '2019'
  • YY:  ← 2-digit year '19'
  • MMMM:  ← Full-length month 'June'
  • MMM:  ← 3 character month 'Jun'
  • MM:  ← Month of the year, zero-padded '06' ← displays the same answer as M
  • DD:  ← Day of the month, zero-padded '01'  ← displays the same answer as D
  • D:  ← Day of the month '1'
  • Do:  ← Day of the month with numeric ordinal contraction '1st'
  • d: ← 0 1 ... 5 6
  • do:  ← 0th 1st ... 5th 6th <- only diff b/w “Do” looks to be 0th instead of 31st
  • dd:  ← Su Mo ... Fr Sa
  • ddd:  ← Sun Mon ... Fri Sat
  • dddd:  ← Sunday Monday ... Friday Saturday
  • HH:  ← hour of day from 0-24, zero-padded, '14' <-hr of c/i time <- displays H
  • H:  ← hour of day from 0-24, '14'
  • hh:  ← hour of day on 12-hour clock, zero-padded, '02' ← padding is used to display the second, ie. 10
  • h:  ← hour of the day on 12 hour clock, '2'
  • mm:  ← minute, zero-padded, '04' ← padding is used to display the second number, ie. 45
  • m:  ← minute, '4' ← minutes at clock in. ie, 3:45 would display with formula as 45
  • ss:  ← second, zero-padded   ← padding is used to display the second, ie. 10
  • s:  ← second 
  • A:  ← 'AM' or 'PM'
  • a:  ← 'am' or 'pm'

Formula to display day of the week column

  • Example: epocToDate(__row("clock_in_time"), "dddd")    ← This will display the entire day name

Tip: Don't check Show Totals for time modifications, as that column won't work.

 

Offset the clock in or out time

  • Example: epocToDate(__row("clock_in_time")+offset, 'format') ←  where offset is +/- number seconds you want to offset the time by.
  • Example: epocToDate(__row("clock_in_time")+120, "m")  ← would increase the displayed time by + 2 minutes.

Offset the clock in or clock out time to display in a different time zone

  • Example: epocToDate(__row("clock_out_time")-3600, "h:mm") ← would decrease the displayed time by one hour.

Explanation:  Each account has a set time zone, but sometimes employees work in a different time zone. If you want their clock in and clock out times to show in their local time instead of your account’s time zone, you can adjust the time in your reports.

Add + 3600 (1 hour) to move back one time zone (for example, from Pacific Time to Mountain Time).

Subtract -3600 (1 hour) to move forward one time zone (for example, from Mountain Time to Pacific Time).

  • -3600 /+3600 changes 1 time zone
  • -7200 / +7200 changes 2 time zones
  • -10800 / +10800 changes 3 time zones

Adjust the clock in time and clock out time +3600 or -3600 for every 1 hour time zone change.

Tip: Add an :a to the end of the formula if you want am or pm displayed at the end of the time

  • Example: epocToDate(__row("clock_in_time")-7200, "hh:mm:a") 

________________________________________________________________________________________________________________

Resolving Errors

When you click Save Formula, you might see an error message. This usually means there’s a small typo in your formula. Counting the characters (including spaces) can help you find where the error is.

To fix it quickly, try copy-pasting the Available Fields back into your formula. This can help catch things like missing brackets or commas that are easy to miss.

If your formula saves but shows an error in the report, it might be because the clock event is missing a job, service item, custom field, or info field used in the formula.

If you're stuck, the VeriClock Support Team is always here to help.

________________________________________________________________________________________________________________

See related VeriClock report guides:

Customizing Your Reports & Templates

Renaming Report Columns

Run a Report

View, Download and Print Reports

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk