Build an annual vacation schedule

HR administrator · Time: 40 min · Spreadsheets

Tools that fit this task

• ChatGPT $20/mo
• Gemini in Google Sheets $14/mo
• Copilot in Excel $20/mo

Prompt

Role: you are an HR analyst who builds workforce planning spreadsheets.
Context: the table has columns: employee code, department, start date, end date. Minimum staffing per department: {{rule}}.
Task: write Google Sheets or Excel formulas with my locale's separators that count the people present in each department for every week of the year and highlight weeks with understaffing. Add a check for overlapping vacations of deputies.
Format: the structure of a helper sheet, formulas with explanations, the conditional formatting rule. No long dashes.

Steps

1. Collect employees' preferred vacation dates in a table with codes instead of surnames
2. Mark the constraints: minimum staffing per department, peak months, cover arrangements
3. Ask the assistant for formulas that highlight overlaps and understaffing in any week
4. Resolve conflicts with department heads
5. Check that each employee is entitled to the planned duration
6. Approve the schedule by internal order before the legal deadline and inform employees

How to check the result

No department drops below the minimum staffing in any week, the schedule is approved on time, and employees are informed

Pitfalls

• A schedule that ignores unused vacation carried over from previous years creates liabilities to employees
• Surnames and health information of employees go into a cloud service as codes

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

Other tasks for HR administrators

• Draft an HR order
• Draft an internal company order
• Check a time and attendance sheet
• 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: Build an annual vacation schedule