Continuous billing/ bench formula

​

Hi everyone,

I have employee-level daily billing data for the last 8 months. Each employee belongs to a band, and each band has a fixed utilization target, for example:

A1 → 100%

A2 → 90%

A3 → 80%

---

What I’m trying to calculate

I want to calculate bench days per employee with the following business rules:

---

Rules / Scenarios

1. Continuous Billing Rule

If an employee is continuously billed for 42 days or more, their bench days should reset to 0.

Once they go into bench after this 42-day billing streak, bench days should start counting again from that point.

---

1. Bench Calculation (when not billed 42 days continuously)

For each bench day, bench value = (utilization target * 1)

Example:

A2 target = 90% → bench value per bench day = 0.90

Total bench = sum of this value for each bench day until reset conditions are met.

---

1. Scenario Example

Employee was on bench for 50 days

Then billed continuously for 40 days (less than 42)

Then goes back to bench

✅ Bench should continue from 50 days (no reset, because 42-day billing condition not met)

---

1. Another Reset Scenario

Employee billed continuously for 42+ days

Bench resets to 0

From the next bench day, bench starts accumulating again using band target logic

---

What I need help with

I’m looking for an Excel formula (or approach) to calculate this bench value:

Based on daily billing status

With band-wise targets

Handling 42-day continuous billing reset logic

Preferably without using VBA (Excel formulas / Power Query also fine)