Annual vacation schedule in Google Sheets with Gemini

An annual vacation schedule looks like a calendar exercise and behaves like a staffing problem. The hard part is seeing, for every week of the year, whether each department still has enough people, and whether a manager and their deputy are away at the same time. Gemini in Google Sheets writes the formulas that make those weeks visible. Building the schedule takes about 40 minutes plus the conversations with department heads that the formulas trigger.

Collect preferences with codes

Ask employees for their preferred vacation dates and collect them in one table: employee code, department, start date, end date. Use codes instead of surnames if you work in a cloud service, and keep the key in a separate file. Health-related reasons for timing, which people sometimes mention, stay out of the sheet.

Pull in unused vacation carried over from previous years as well. A schedule built only on this year's entitlement creates a hidden liability: people with large carried-over balances will take them eventually, usually at the worst moment.

Write the constraints down

Before asking for formulas, write the rules. Minimum staffing per department, such as "at least two people in accounts payable every week". Peak months when vacations should be limited, such as the month-end close in finance or the season in retail. Cover arrangements: who deputizes for whom. These rules are what the formulas check.

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

The helper sheet usually has departments as rows and weeks as columns, with a count in each cell. Conditional formatting turns the understaffed weeks red, and the whole year becomes readable at a glance.

Check the formulas

Make sure the date columns hold real dates; text dates break the weekly count without warning. Then test the formulas on a week you can count by hand. Gemini's formulas are usually correct in logic and occasionally off at the boundaries, for example counting someone as away in the week their vacation ends on Monday. Fix the boundary rule once and every week is right.

Resolve conflicts with people

The red weeks are a list of conversations. Take them to department heads, who know which requests are flexible. Then check that each employee is entitled to the planned duration, including carried-over days. Finally, approve the schedule by internal order before the legal deadline in your jurisdiction and tell employees their approved dates.

What to do

1. Collect preferred vacation dates with employee codes, department, start and end dates.
2. Add unused vacation carried over from previous years.
3. Write constraints: minimum staffing, peak months, cover arrangements.
4. Run the prompt in the Gemini panel and build the helper sheet.
5. Test the weekly count on one week by hand.
6. Resolve red weeks with department heads and check each entitlement.
7. Approve the schedule by internal order before the legal deadline and inform employees.

A schedule that holds

No department drops below minimum staffing in any week, the schedule was approved on time, and employees know their dates.

Plans

Gemini in Sheets is included in Workspace Business Standard at 14 USD per user with annual billing, where company data stays out of model training, or in Google AI Pro at 19.99 USD per month for a personal account. See Gemini in Google Sheets pricing, the HR administrator's version of this task and Gemini in Google Sheets vs Copilot in Excel.