Check a time and attendance sheet

HR administrator · Time: 30 min · Spreadsheets

Tools that fit this task

• Gemini in Google Sheets $14/mo
• Copilot in Excel $20/mo
• Formula Bot Free

Prompt

Role: you are an HR administrator checking the timesheet before payroll.
Context: the timesheet is in Excel: rows are employee codes, columns are days of the month, cells hold hours or absence codes {{absence codes}}. Standard hours for the month: {{hours}}. Public holidays: {{dates}}.
Task: write formulas with my locale's separators that calculate hours worked, days absent for each code, deviation from the standard, and flag employees with a deviation.
Format: a table "column, formula, what it checks", and the conditional formatting rule. No long dashes.

Steps

1. Export the monthly timesheet with employee codes instead of surnames
2. Write the rules: standard hours, night work, holidays, sick leave and vacation by order
3. Ask the assistant for formulas that check each rule
4. Filter the rows with discrepancies
5. Match discrepancies against orders and sick notes
6. Pass the corrected timesheet to payroll before salaries are calculated

How to check the result

Each employee's total hours match the standard or are explained by a document, and sick leave and vacations match the orders and sick notes

Pitfalls

• A formula that ignores moved working days produces false discrepancies in months with holidays
• Keep timesheets with surnames and diagnoses out of public services

Data that stays out of public AI tools

• Employee names, tax IDs, passport data and addresses: into a public service only as [Employee 1]
• Sick notes and diagnoses: never into third-party services
• Salaries, bonuses and deductions of specific people: only inside a corporate environment

• Check a timesheet before payroll with Copilot in Excel

Other tasks for HR administrators

• Draft an HR order
• Draft an internal company order
• Build an annual vacation schedule
• Write a formal role description
• Prepare for a labor inspection
• Build an HR answer guide for employees
• Draft a personal data processing policy
• Summarize changes in legislation for colleagues
• Turn a scanned document into editable text
• Write a business letter

AI tools for HR administrators · Prompt: Check a time and attendance sheet