Build separate views for opening membership, hires/exits, closing membership and opening-cohort retention. Each percentage needs its own period, scope and denominator.
An original complete September ledger
Fictional employee scope, September 2026. The opening snapshot is immediately before the period begins on September 1; the closing snapshot is after the period ends on September 30. All listed events fall inside that period. IDs are fictional and contain no wages, demographics or exit reasons.
| Record | Supplied membership or event |
|---|---|
| Opening employees | E01, E02, E03, E04 |
| September 5 | E05 joins |
| September 10 | E05 exits |
| September 20 | E06 joins |
| September 25 | E02 exits |
| Closing employees | E01, E03, E04, E06 |
Contractor C01 is outside this example's explicitly declared employee scope. That is a dataset boundary, not a legal finding about any worker's status. The example has no rehires or internal transfers; actual treatment of those events requires the organization's supplied policy.
Reconcile the flow as well as the snapshots
Opening headcount is 4. Period hires are 2: E05 and E06. Period exits are 2: E05 and E02. The expected closing count is 4 + 2 − 2 = 4, matching the listed closing IDs.
E05 is absent from both snapshots but has two recorded events within the fictional ledger. Deleting those events because E05 is not active at either endpoint would hide a hire and an exit. A net change of zero is not a movement count of zero.
BLS's JOLTS definitions distinguish an employment reference period from monthly additions and separations, including workers hired and separated within the same month. This internal exercise uses its own opening/closing snapshots and declared formulas. It is not a reproduction of BLS employment or published turnover rates.
Compare the actual IDs as well as the arithmetic. Matching totals alone would not reveal an incorrectly replaced identity.
Declare the separation-rate basis
For this original illustration only, the monthly separation rate is all period exits divided by the average of opening and closing headcounts, multiplied by 100:
2 ÷ ((4 + 4) ÷ 2) × 100 = 2 ÷ 4 × 100 = 50%.
Show the label September 2026 separation rate; endpoint-average denominator = 4; exits = 2. The parentheses matter: calculate the average headcount first, then divide the exits by it.
This denominator is an endpoint average, not a daily or time-weighted average. The result is not an annualized rate or a US benchmark comparison. Use a different basis only when it is explicitly supplied, and disclose the change rather than relabeling this calculation.
Calculate opening-cohort retention separately
The opening cohort is E01, E02, E03 and E04. Of those four, E01, E03 and E04 are present at closing. Thus opening-cohort retention is 3 ÷ 4 × 100 = 75%.
E06 is a closing employee but was not an opening member. E05 was neither an opening member nor a retained member. Keep both outside the opening-cohort numerator and denominator.
Two tempting calculations answer the wrong question:
- Closing 4 ÷ opening 4 = 100% compares totals, not retained identities.
- 100% − separation rate 50% = 50% is not this cohort's retention, because period exits include E05, who joined after the opening snapshot.
The 50% separation rate and 75% retention can coexist. They use different event and membership definitions. Put the actual opening cohort and retained IDs beside the retention result so a reviewer can see why.
Hold unresolved records out of a final claim
For a real workbook, retain employee IDs, distinct event IDs, effective dates and snapshot rules. Check missing join/exit dates, duplicate active events and an exit before the recorded join. Keep exception rows visible; do not quietly repair or discard them to force reconciliation.
If those records affect membership or flow, hold the affected final rates unconfirmed until resolved. If average headcount is zero, the separation formula is not calculable; do not divide by zero or display a confident percentage. Confirm actual policies for rehires, transfers, effective-date boundaries and employee scope.
This tiny fictional dataset supplies no explanation for exits or evidence about morale, employee performance or future staffing. Use it to verify record movement and formula scope, not to make employment decisions.
Copy a membership-and-movement dashboard brief
Prepare an employee-turnover dashboard specification in US English.
Period boundaries, employee scope and effective-date rules: [enter]
Opening and closing employee IDs: [paste sanitized records]
Distinct hire/exit event IDs, employee IDs and dates: [paste]
Declared separation-rate denominator and retention cohort: [paste]
Policies for rehires/transfers and required Excel/CSV workflow: [enter or TO CONFIRM]
Reconcile opening + hires - exits to closing count and actual membership.
Retain both events for a same-period join/exit even if absent at snapshots.
Separate stock snapshots from period flows. Show each rate's period, scope,
numerator, denominator and included IDs. Opening-cohort retention excludes
later hires; it is not closing/opening totals or 1 minus the separation rate.
Flag missing dates, duplicates and invalid event order without silent repair.
Hold affected final rates unconfirmed; zero average headcount is not calculable.
Do not infer exit causes, morale, performance, forecasts or US benchmarks.
Return checked native records, calculations and labeled views. If requested,
specify a sanitized HTML movement-review copy with local cohort/flow views.
No HRIS integration, employee-data collection, saved edits or employment actions.
Trace E05's two events and E06's closing membership before sharing the percentages. NOS can save and display a sanitized HTML movement-review copy with the IDs beside the separate formulas. Keep authoritative private HR records and native Excel/CSV files in the approved workflow, and resolve event exceptions there before treating a reconciled count as a verified retention result.