Prompt: Build an annual vacation schedule
Spreadsheets · Time: 40 min
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.Placeholders to fill in
{{rule}}
Works well in
- Gemini in Google Sheets $14/mo
- Copilot in Excel $20/mo
- ChatGPT $20/mo
How to check the answer
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
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.