Check a timesheet before payroll with Copilot in Excel

A timesheet error found after payroll is a correction, a recalculation and an awkward conversation. Found before, it is a two-minute fix. Copilot in Excel writes the formulas that check a monthly timesheet against your rules, and the check takes about 30 minutes: build the formulas once, filter the flagged rows, match them against documents. Next month, the same formulas run again.

Export with codes

Export the monthly timesheet to Excel with employee codes instead of surnames. Rows are employees, columns are days, and cells hold hours or absence codes. Format the range as a table with Ctrl+T and save the file in OneDrive or SharePoint with AutoSave on, since Copilot needs both.

Write the rules first

List what the check should enforce: standard hours for the month, night work and how it is marked, public holidays, sick leave and vacation by order. Include the absence codes your timesheet uses. The formulas are only as good as these rules, and writing them out often reveals that different departments mark the same absence differently.

The 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.

List the public holidays explicitly. A formula that does not know about holidays and moved working days flags half the company in a holiday month, and false alarms teach people to ignore the check.

Test the formulas

Pick two employees whose month you know: one with a clean month, one with a vacation or sick leave. Check that the formulas give the right hours and absences for both. Copilot confuses absolute and relative references, and a formula that is right in the first row can drift as it fills down. If you use the =COPILOT() function anywhere, save its results as values; it may return a different result on recalculation.

Match flags against documents

Filter the rows with deviations. For each one, find the document that explains it: a vacation order, a sick note, an order on overtime or a business trip. Three outcomes are possible. The document exists and the timesheet is right. The document exists and the timesheet is wrong, so correct it. No document exists, so ask the manager. Only the first outcome needs no action.

What to do

1. Export the monthly timesheet with employee codes and format it as a table in OneDrive or SharePoint.
2. Write the rules: standard hours, night work, holidays, sick leave and vacation by order.
3. Ask Copilot for the formulas with the prompt, listing absence codes and holidays.
4. Test the formulas on two employees whose month you know.
5. Filter the rows with discrepancies.
6. Match each discrepancy against orders and sick notes.
7. Pass the corrected timesheet to payroll before salaries are calculated.

A clean timesheet

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

Licenses

Copilot in Excel is included in Microsoft 365 Copilot at 30 USD per seat with annual billing, where company data stays in the corporate tenant, and in Copilot Pro at 20 USD per month for individuals with Microsoft 365 Personal or Family. Timesheets belong in the corporate tenant. See Copilot in Excel pricing, the HR administrator's version of this task and Copilot in Excel vs Gemini in Google Sheets.