Shift coverage: find every understaffed time window

Shift coverage checks whether enough distinct people are available for a service during every part of its operating window. Use this walkthrough to produce a saved list of every understaffed interval, its duration and the people actually available. The result is a question-ready coverage record, not a payroll calculation or an automatically approved roster.

Updated: 18 October 2026. All people, intervals and requirements below are fictional exercise inputs. The example does not establish a legal staffing minimum, determine break entitlements or measure a real team’s performance.

What must be known before counting coverage?

Imagine a small service desk with one kind of task. You have a list of people who can perform that task and their confirmed availability for it. You need to check an existing arrangement, not choose a headcount from customer demand. Keep those decisions separate: the owner supplies the required number before you assess whether the intervals meet it.

For this exercise, inspect 09:00–13:00 on one local day at one location. The supplied requirement is two available people throughout. Every person is stipulated to be qualified for the same role. An employee doing unrelated work elsewhere must not be treated as available merely because they are on the payroll.

• A is available from 09:00 to 11:00.
• B is available from 09:00 to 10:00, then 10:30 to 13:00.
• C is available from 11:00 to 13:00.
• D is available from 09:00 to 09:30.

B is explicitly unavailable for this service between 10:00 and 10:30. Do not infer a paid or unpaid break from that statement. If an end time, qualification, unavailable interval or required count is missing, label the affected conclusion unresolved. A blank is not permission to assume someone remains available.

If your actual task is calculating individual working hours for review, use the employee time-tracking guide instead. Coverage asks who is available simultaneously; a daily hours total cannot answer that question on its own.

Split the window wherever availability changes

List each time at which anyone starts or stops being available: 09:00, 09:30, 10:00, 10:30, 11:00 and 13:00. Sort them and keep each boundary once. Each adjacent pair defines a segment within which the available-person set remains unchanged. This gives five segments, including those that satisfy the requirement.

Use an explicit endpoint convention: the start is included and the end is excluded. A is therefore not counted at 11:00, while C is counted from 11:00 onward. Process simultaneous changes together. Removing A and checking the count before adding C would manufacture a gap that does not exist in this model.

Write distinct IDs before counting them. Two overlapping records for B still represent one person. The supplied intervals for each person do not overlap; if your real input does, resolve the duplication or form the unique-person set rather than counting rows. Names alone can also be ambiguous, so use a stable identifier in the working record.

Membership requires several conditions together: the correct service role, a start already reached and an end not yet reached. The AND/OR grouping audit explains a related logic check. The output here goes further than classifying one row: you need a complete time map and its changing sets of people.

Read every interval, not just the daily total

For each segment, count its distinct available IDs and compare the result with two. Shortfall is the required count minus the available count, with negative results replaced by zero. Duration is the difference between the two boundaries. Keep sufficient and insufficient segments in the record so another person can inspect the whole window.

The only deficient window is 10:00–10:30. A is the only available ID, the shortfall is one person and the duration is 30 minutes. At 11:00, B remains and C replaces A. There is no missing interval and no momentary extra person created by counting both endpoints.

Now make a separate arithmetic check. A contributes two hours, B three and a half, C two and D half an hour. That totals eight person-hours, meaning the equivalent of one person available for eight hours. The stated requirement also totals eight person-hours: two people across four hours. Equal totals have not prevented the shortage.

The first half-hour contains half a person-hour of surplus. The deficient half-hour contains half a person-hour of shortfall. They cancel in the total but do not cover one another in time. Report the 30-minute deficient window and the one-person shortfall separately. Calling the problem “half an hour missing” without a headcount would lose useful information.

Turn a shortage into a precise owner question

Send the record to the person who owns the requirement rather than silently adjusting the arrangement. A useful question is: “Our input shows only A available from 10:00 to 10:30. Are the two-person requirement and B’s unavailability both confirmed?” This makes the uncertain assumptions visible and gives the owner something specific to verify.

If the input is corrected, recalculate all affected segments and save a new version. Do not patch only the highlighted result: an earlier boundary or a different person’s availability may also change. Retain unresolved fields instead of replacing them with convenient guesses. A coverage check is only as defensible as its source information.

As a mathematical sensitivity check only, move D’s stipulated half-hour from 09:00–09:30 to 10:00–10:30. The total remains eight person-hours. The revised sets are A and B from 09:00–10:00, A and D from 10:00–10:30, A and B from 10:30–11:00, then B and C until 13:00. Every segment now has two people. This is not authority to change a real shift, and it does not demonstrate an optimal or legally compliant roster.

The logic resembles the running inventory balance audit: intermediate conditions can matter even when the final total looks fine. The distinct deliverable here is all deficient availability windows with unique IDs and durations, not the first negative event in an immutable transaction history.

Save a coverage record you can reopen

Create a plain-text record before considering a scheduling system. On Windows, open Start, launch Notepad and create a blank document. Copy the template below. Other text editors are suitable too; the important outcome is a file containing inputs, rules, complete results and the unanswered owner question.

Version: V1, fictional exercise
Location/role: one desk, one shared service role
Window: one local day, 09:00–13:00
Supplied requirement: 2 throughout
A 09:00–11:00
B 09:00–10:00; 10:30–13:00
C 11:00–13:00
D 09:00–09:30
B unavailable 10:00–10:30
Rule: start included, end excluded; distinct IDs only
09:00–09:30 | A,B,D | 3 | shortfall 0 | 30 min
09:30–10:00 | A,B | 2 | shortfall 0 | 30 min
10:00–10:30 | A | 1 | shortfall 1 | 30 min
10:30–11:00 | A,B | 2 | shortfall 0 | 30 min
11:00–13:00 | B,C | 2 | shortfall 0 | 120 min
Finding: 10:00–10:30; A only; one person short
Owner question: requirement and availability confirmed?
Unknown fields: retain explicitly until verified

Press Ctrl+S, choose Documents and save as service-coverage-V1.txt. Close the document. Open File Explorer, find Documents and reopen the saved file. Check that all five segments, the unavailable interval and the owner question survived. This readback distinguishes a reusable record from text left unsaved in an editor.

A suggested 15-minute exercise is to confirm the four-person input, construct the boundaries, check the five segments and save the record. That is a proposed small task, not an observed completion time from user research. If your input is more complicated, narrow the first check to one location and role rather than rushing unknown facts.

Know what a spreadsheet can and cannot establish

Google’s Workforce Scheduling documentation treats coverage requirements as counts for roles over time windows. That supports the distinction between a total and time-specific coverage. This walkthrough does not call its API, require an account or claim a solver result. Our fictional requirement and calculations are our exercise model, not a staffing recommendation from Google.

Microsoft documents COUNTIFS as counting rows that satisfy all supplied criteria. A row count is not automatically a count of distinct people. Nor does the function discover breaks, create every boundary or validate qualifications for you. No Excel formula has been executed or supplied as a tested solution here. Establish the manual answer before implementing anything larger.

Keep public opening hours separate as well. The special opening-hours guide concerns what customers see; this record concerns who can provide a particular service. Different locations, roles, overnight intervals and clock changes need their own clearly stated treatment rather than being hidden inside this single-day example.

Anar’s published Azerbaijani reflection on perfection and effectiveness describes project delays associated with pursuing perfection. The practical recommendation here draws on that principle: make one inspectable record first. It is not a claim that Anar ran a staffing project. For a broader example of separating timing questions from other resource assumptions, see the critical-path guide. Finish this check with a saved finding and an explicit owner question, not an unapproved schedule change.