Prompt: Check a time and attendance sheet
Spreadsheets · Time: 30 min
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.Placeholders to fill in
{{absence codes}}{{hours}}{{dates}}
Works well in
- Copilot in Excel $20/mo
- Gemini in Google Sheets $14/mo
- Formula Bot Free
How to check the answer
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
Step-by-step versions by role
AI tools weekly for your role
One email a week: new tools, price changes and one tested prompt for your job. Free, unsubscribe in one click.