In Formulas, the Time Tracking Log has become a compound field that can be used similarly to Linked Records.
Its sub-fields are accessible through dot notation:
[Time Tracking Log].[Reported By]
[Time Tracking Log].[Reported On]
[Time Tracking Log].[Reported Time]
[Time Tracking Log].[Note]
Basic Example
The MIN() function retrieves the date of the earliest time entry.
In other words, this represents the actual start date of a task, often compared against a planned start date.
Calculate tasks's actual duration.
A delta between the earliest and the latest reported dates becomes task’s actual duration, measured in days.
Let’s use WORKDAYS_DIFF() to calculate it.
This value can be compared against a planned duration to evaluate estimation accuracy.
Totals by person.
Oftentimes, several people book hours against the same task.
A simple SUMIF() clause extracts the total amount of reported time for a specified person.
Remember to divide the resulting value by 3600 to get decimal hours.
Filter by Notes.
Users can add Notes to their time entries.
Let’s say “NB” stands for non-billable hours.
Here’s how we can exclude those from the calculation.
Filter by Dates.
Here’s how to extract data for a certain period.
Let’s say we need to see total hours for an assigned person for today.
“Today” actually represents a duration of 24 hours. Pay attention to how we are using two conditions to capture this range.
Rollup for a Project.
Here’s how you can calculate the total number of hours a specific person booked for a project during the previous month.
The assumptions:
Each project is linked to multiple tasks,
hours are booked on a Task level, and
we’re building a formula at the Project level.
Formulas Cheat Sheet
Grab these useful formulas for your builds now!
