---
name: vardiya-kapsama
description: "Use when a shift staffing needs and uncovered hours analysis is requested from shift roster, actual clock-in/clock-out and the department's staffing requirement plan by time slot. Produce a formula-based Excel file and a Word summary."
---

# Shift staffing needs and uncovered hours

In which time slot did the team actually on duty fall below the required level?

## Input and scope

Shift roster, actual clock-in/clock-out and the department's staffing requirement plan by time slot.

Use the attached Excel/CSV files as the source. Read each one without modifying it. Identify the sheets, report scope, periods, units and required fields. Match synonymous column headers to the sample. If a missing field affects only a specific metric, still produce the other metrics. If a critical definition can be interpreted in more than one way, ask one focused question. Trace subtotals and duplicates back to the source row. Remove fields containing personal data from the output. Use the sample only when the user wants to try it with the sample data.

## Analysis

1. Match department, role and time slot. Keep breaks, leave and assignments to other departments out of actual coverage. For shifts that cross midnight, use the full date and time.

2. Split partially overlapping shifts at each clock-in/clock-out or change in requirement. Do not count the same employee twice in the same time slot. Use hours for slot length and headcount for requirement.

3. Uncovered person-hours = max(0, requirement − actual coverage) × slot length. Do not offset a surplus in one slot against a shortfall in another. If the staffing standard is missing, do not assign a performance assessment to staff.

## Delivery and checks

On the first sheet of Analiz.xlsx, show the rows that answer the question and the calculated indicators. Keep the sources on a separate sheet with anonymised fields. Write Excel formulas for derived measures. On the Checks sheet, add the date range, column mapping, scope, missing/unmatched records and the reconciliation result. Create the Word file as Yonetim-Ozeti.docx. On one page, write the period, the main result, at most three evidenced findings and the work the responsible team should check. Reopen both files; independently check the numbers, the denominators of the ratios and the source totals. Do not report causes you cannot verify as findings. Deliver both files as downloads.

Visible fields in the Excel analysis: Time slot, Required staff, Actual staff, Staff short, Uncovered person-hours.

## Try it with the sample

Use assets/ornek-girdi.xlsx. The expected Excel and Word examples are in the ornekler/ folder. Check specific to this sample: Front office has a total of six uncovered person-hours. Four of those person-hours fall between 14:00 and 16:00.
