Time Clock and Hours Worked in Excel

# www.prompthubai.com.br
# Encontre prompts, agentes e workflows testados para vender, programar e automatizar com IA em português.

# Time Clock and Hours Worked in Excel

## Cabecalho
- Tipo: Conteudo
- Categoria: Automação Comercial
- Modulos: 0
- Agentes: 0

## Escopo
This prompt turns real work schedule data, internal rules, and employee parameters into a complete time clock spreadsheet project in Microsoft Excel. The response provides a ready-to-build structure, including columns, tables, formulas in Brazilian Portuguese, validations, formatting rules, and management indicators.

It is intended for HR professionals, Personnel Department teams, managers, administrative analysts, and small businesses that need to track clock-ins, clock-outs, breaks, overtime, lateness, absences, and banked hours with traceability. It also includes an optional VBA automation alternative, when authorized by the user, and guidance to reduce entry errors.

## Prompt Principal
Act as a senior specialist in Microsoft Excel, Personnel Department, and time-tracking automation. Your mission is to design a robust time clock and hours worked spreadsheet, ready for direct implementation in Excel in Brazilian Portuguese. Deliver operational instructions, copyable formulas, sheet structure, and automation rules compatible with Microsoft 365 Excel; when there is an alternative for earlier versions, clearly flag it.

Before building the solution, request or use the data below. If any item is not provided, state the assumption adopted and make it parameterizable on the settings sheet:
1. Employee list: employee ID, name, department, manager, shift, and contracted working hours.
2. Calculation period, national/state/municipal holidays, and city/state, if applicable.
3. Planned schedules by shift: start, end, break, tolerance, daily working hours, and rule for shifts that cross midnight.
4. Internal rules: handling of lateness, early departure, absences, compensations, banked hours, overtime premium, daily/monthly limit, and need for manager approval.
5. Available recording method: manual entry, time clock import, CSV spreadsheet, or already structured records.
6. Technical environment: Excel version, whether macros/VBA are allowed or not, function language, and argument separator (semicolon or comma).
7. Additional desired fields: justifications, notes, cost center, project, approval status, or signature.

Then develop the solution following this framework exactly:

STEP 1 — File architecture: propose the sheets with clear names, purpose, responsible person for filling them in, and protection type. At minimum, assess these sheets: "Configurações", "Colaboradores", "Escalas", "Feriados", "Registros", "Apuração Mensal", "Banco de Horas", "Dashboard" and "Listas". Specify which ranges should be converted into Excel Tables and suggest technical names for each table, for example tblColaboradores and tblRegistros.

STEP 2 — Data modeling: detail, in table format, each required column for each operational sheet. For each column, provide: header name, data type, source/filling method, example value, recommended validation, and technical note. Structure Registros as a database format, with one row per employee per date, avoiding merged cells and totals inserted in the middle of the dataset.

STEP 3 — Ready-to-use formulas: provide complete, copyable formulas to calculate planned work time, first clock-in, break start and end, last clock-out, hours worked, actual break, normal hours, overtime, lateness, early departure, night hours when applicable, daily balance, and accumulated banked hours balance. Use structured Table references whenever possible and functions in Brazilian Portuguese, such as SE, SES, SEERRO, SOMASES, PROCV or PROCX, LET, MÁXIMO and MÍNIMO. Explicitly handle empty cells, incomplete punches, holidays, weekends, night shifts, and negative time values. Explain the correct number format, especially [h]:mm for accumulations above 24 hours.

STEP 4 — Controls and usability: define data validations, drop-down lists, locking of formula cells, freezing panes, filters, slicers, and conditional formatting. Include visual rules for lateness, missing punches, negative balance, overtime above the limit, and pending approval. Recommend a safe monthly closing and backup routine.

STEP 5 — Dashboard: describe a management dashboard with KPIs, filters, and charts. Include at minimum: planned versus actual hours, banked hours balance, total overtime, lateness, absences/incidents, employees with pending items, and weekly or monthly trend. Specify the source of each indicator, the required formula or measure, and the most suitable chart.

STEP 6 — Optional VBA: only if macros are allowed, provide a complete, commented, and testable VBA module for at least one useful automation: importing CSV records, consolidating a period, updating the dashboard, or generating an individual PDF report. Include where to insert the code, how to save it as .xlsm, execution instructions, and security considerations. Do not use destructive automations without confirmation.

Present the final answer in this exact order: (1) assumptions and pending questions; (2) sheet map; (3) detailed table structure; (4) formulas by column, in code blocks; (5) validations and formatting; (6) dashboard; (7) optional VBA; (8) step-by-step implementation roadmap; (9) test checklist with real scenarios, including normal shift, lateness, insufficient break, missing punch, holiday, negative balance, and night shift.

Keep the language technical, practical, and objective. Do not invent legal compliance: highlight that labor rules, collective agreements, and electronic recordkeeping requirements must be validated by HR/legal. Prioritize auditability, parameterization, protection against input errors, and formulas that can be applied directly in Excel.