Team Calendar

In a business environment, team members and managers need to record and track days off, special staffing requirements, and risks such as understaffing and end-of-year vacations.

Team Calendar takes members’ entry of name and date, normally a day off but may also be used to record a day worked on a weekend or holiday. A policy setting can then return a comp day, or not. A day off that will result in a shortage is flagged, to be confirmed with the manager. Each entry updates the individual’s days used and remaining for the current year.

The manager maintains the policy and the team roster with hire date, which determines days due. They can then maintain special peak dates, shutdowns, or weekend and holiday coverage needed. A calendar is included that highlights all date entries, and even birthdays and milestone anniversaries.

Excel features: Advanced Boolean, Date and Lookup formulas; Name Manager named ranges and formulas; Data Validation rules and messages; Conditional Formatting; Tables linked for input, process and output; PivotTables and PivotCharts; Column Groups; Camera tool; VBA: none

Contents
Team Calendar.zip, with
- Team Calendar.xlsx (Excel Workbook), with sample data
- Team Calendar.xltx (Excel Template), blank
Usage
USAGE NOTES

Input: name and date; name must exist in Team table on Settings sheet. Input response:
shortage is highlighted red when a workday off is entered which will cause a staff shortage
coverage day is highlighted yellow when a non-workday is entered
vacation days used and left are updated including comp days if enabled
calendar: set or clear checkboxes to customize display (calendar is linked image of Report calendar)

Report: review Report table, input Special table
Start: select Today (default), or Jan 1 of this year or Jan 1 of next year
Report Flag column: review short and over flags
Report Used column: review aggregate vacation risk based on Alert start and level in Settings
Special table: enter date and ± staff required or 0 for shutdown
Calendar: set or clear checkboxes to customize display

Analysis: review vacation usage by team member and overall. Pivots refresh upon opening.

Settings: select icons in row 1 to expand/collapse column groups
Date settings: format, work week and holiday rules and/or manual holiday dates
Team settings: controls, vacation policy (include milestone years), team member list

Maintenance:
Input table: keep dates in past until new year starts and all carry-overs have been recorded
Special table: dates in past can be deleted
Team table: enter new hire placeholder for vacation planning purposes, maintain dates

TEMPLATE NOTES

Team Calendar can be used as a template to set up multiple teams, or once for a single team. There is no need to create a new file for each year. Once a team is set up, the file can be maintained as an ongoing Calendar from year to year. Past years can be deleted or kept, the input and analysis is always for the current year.

To use the template as a single file, follow Option 2 below, except Save-As Excel Workbook instead of Excel Template. To use the template as a template, first make it accessible.
Option 1: save the template file in your template folder, which you can find in Options, Alt-F,T,S, something like C:\Users\ (username) \OneDrive\Documents\Office\Templates.
Option 2: open the template, you'll see Team Calendar1 as the filename. Hit F12 (Save-As), remove the "1" in the name, and in Save as type:, select Excel Template. This selects your template folder automatically.

To apply the template, File-New and navigate to personal templates, Alt-F,N,Y,3, it should be there. You can pin it to the top to make it easier to find next time.

To change the template, follow Option 2 above; open, edit, then Save-As and overwrite the current one. For example, you might want to save it with some data that is standard or similar across teams.

$20.00

Team Calendar

Contents

Usage