How to create a formula to count % of time utilization

1

Hey there Coda Makers!

I’ve been tasked with creating a resource management system for our company and I want to use Coda for that. Now I’ve created the bulk of the setup, but I came across a formula limitation I can’t overcome.

How can I create a formula that would count the % of utilisation of a given employee based on the assignments of that employee?

To give you some context, this is the current setup:

  1. Employees Table - contains the list of all employees.

Columns:

  1. Name: Name of the employee.
  2. Current Contract: Fraction of full-time employment (e.g., 1 for 100%, 0.5 for 50%).
  3. Utilization: A calculated column that should display the employee’s current % utilization.
  1. Assignments Table - tracks the assignments of employees to projects.

Columns:

  1. Employee: Relation to the Employees table.
  2. % of FTE: The percentage of their FTE used for this assignment.
  3. Start date and End date: The dates defining the duration of the assignment.

Now, I want the Utilization column in the Employees table to calculate the total % utilization of an employee today.

I’ve tried using a combination of Filter, FormulaMap, and Sequence, but I keep running into issues with overlapping assignment dates.

This is the formula that I use:

[Assignemnts (TEST)].Filter(
Employee= thisRow AND Today() >= [Start date] 
  AND (Today() <= [End date]
  OR [End date].IsBlank())
).[% of FTE].Sum()
/
(thisRow.[Current Contract]*100)

The problem arises when I have assignments like this one:

Employee A is assigned to project Z for 100% of his FTE from the 1st of December till the 14th of December.

Then the same employee is assigned to project X for 100% of his FTE from the 16th of December till 31st of January 2025.

Using the formula I pasted above, the result for Utilization is 133.33%.

How would you approach solving this problem?

I don’t really understand why the formula I created counts the 2nd assignment if it’s start date is higher than today ;(

Thanks for any tips!

PS - This is a simplified version of the system I’m building: Resource Management System (For demonstration)

Edit: fixed some typos

2

Hi @Jakub_Skupien

I believe the issue lies in the logic. It is returning 133% because you are dividing 100% by 75%. Essentially, you are stating that an employee is allocated 100% to a project, but they only have 75% of their week available. This results in a utilization rate of 133%.

What are you trying to achieve? Should the utilization represent the current percentage of an employee’s workload based on what criteria? Is 100% based on their current contract, or is it based on a full-time schedule?

  • For the first option (based on the current contract), you don’t need to factor the contract into the formula—just return the sum of their utilization.
  • For the second option (based on a full-time schedule), you need to multiply by the current contract, meaning that 100% for an employee would correspond to 75% of a full-time workload.

Best Regards,
Arnhold

1 Like