This file is available in our store. Contents:
Settings to control work calendar, vacation policy and team roster
Input Sheet for team members to enter days off, with feedback
Report Sheet for the team manager to monitor staffing risks
Analysis Sheet to monitor vacation usage
Purpose
There are several reasons for having a robust team calendar. HR usually provides either formal or informal team calendars to ensure that managers are accountable for their team’s vacation entitlements. Managers also have a need to manage staffing around peak demand periods, and the “hockey stick” effect of end-of-year vacations.
For capacity, we are concerned with routine work that requires a certain level of staffing to operate machines, answer phones, etc. There may also be special needs to cover weekends and holidays, or promotional periods when full staffing is required. Finally, there might be occasional shutdowns that are considered mandatory vacation days. The manager should have control of such events, and visibility to staffing shortages.
This should be a transparent, interactive tool for team members to enter their vacation days, sign up for non-workday coverage, and track their own balance. It must also serve the manager to track individual and overall team days off and coverage requirements.
Structure
There are four sheets, each with a purpose; Input for member entry of vacation and coverage days, Report for management tracking of staffing needs and coverage, Analysis for pivot-chart views of member and team days, and Settings for configuration. The Startup File Calendar is not copied in, but several elements of it are reapplied. For these I will briefly describe them here, refer to the Startup Calendar for detail.
Settings
Settings are collected on the Settings sheet in two groups, Date Settings and Team Settings. As a standard, the sheet background and all calculated cells are shaded, so only entry cells are white.
Calculated holidays and vacation day entitlements are given for the current plus the next four years.
Date Settings
Date settings are mostly one-time setup. There are two column groups to hide them when not in use. The first column group includes individual selection cells, followed by three stacked tables, all with the same number of columns to enable insertion and deletion of rows. These are HolidayRules, HolidayCalc and HolidayInput.
The second column group contains the Weeks table, which is a static lookup table used for workday selection and formulas.
Date Format (see Startup Calendar Date Format)
Date format is set by two indicators, DateOrder and DateSeparator. DateOrder is one of {dmy,mdy,ymd} and DateSeparator is one of {.,/,-}. The nine possible formats are applied to all full dates.
Work Week (see Startup Calendar Work Week)
Work week is selected from the Weeks lookup table. A standard five-day work week can start any day of the week, or a custom weekly pattern can be defined. Whether standard or custom, the week is defined by the seven-binary-digit string format used in international workday functions. This string is then dissected to find out if the reference date is on a weekend.
(is a weekend) = ... VALUE(MID(WkString,WEEKDAY([@date],2),1)) ...
This can be used as a date type index (1=weekend) or as a Boolean (is weekend).
Holidays (see Startup Calendar Holidays)
Holidays can be calculated from rules, or entered in a table, or both. Manually entered holidays must be entered for each year. Then a holiday is identified if the reference date exists in either the HolidayCalc table or the HolidayInput table, here as example applied to the Report table:
(is a holiday) = OR(COUNTIF(HolidayInput[date], Report[@date]),COUNTIF(HolidayCalc[date],Report[@date]))
Now with Booleans for both weekend and holiday, they are combined in a date type formula for each table.
Date Type (general form): =IF(is a weekend, 1, IF(is a holiday, 2, 0))
Date Type then evaluates as a Boolean itself, “is a non-workday”, and also +1 as an index for CHOOSE.
Boolean example =IF(OR(DateTypeReport,ScheduleShutdown),0,StaffMin) Index example =CHOOSE(DateTypeReport+1, "workday","weekend","holiday")
Holiday tables have error checking that flags the unlikely condition that a holiday falls on a weekend. This can occur during configuration when changing week or holiday selections, but once they are set up correctly will not recur. It is a matter of principle to alert the user of any error that can occur based on their input.
Team Settings
Controls
The first group of team settings is a set of management controls. The term “controls” is used here in its general sense; there is only one checkbox as an actual “form control”.
Alert start and alert level work together to flag the risk of low vacation usage. Report[Used] is the % days taken of days entitled for the team as a whole. If vacation usage is on track, the value will approach 100%. If it is low, there is risk of an end-of-year spike, which should prompt the manager to remind members to take their vacation. The result of these settings is presented in the Report table section.
The third control setting is for comp days. If a member works on a weekend or holiday, comp days means they are compensated with a day of vacation. This setting is displayed on the Input sheet and is used in the calculation of days taken and remaining, making it transparent to the user.
The fourth control cell MinStaff is to set a minimum staff level as a percent of the total roster. A certain percentage of the team is expected to be off work at any time. To allow for such normal absences, MinStaff sets the level below which the manager is alerted of a shortage. This can occur due to special requirements or due to vacations.
Vacation Policy
The policy table is two columns for years of service and vacation days granted. Some years do not change the days granted, but are used to define milestone anniversaries. For clarity, [Service] is formatted 0" y", and [Days] is formatted 0" d".
Team Table
The Team table serves two purposes. First, it gives the vacation days due each member, based on their start date and the policy. This is used to determine and track the usage of vacation days, at the member level in the Input table, and at the team level in the Report and Analysis sheets. The second purpose is to determine milestone anniversaries, and provide these and birthdays to the Calendar.
Inputs are Name, Birthday, Start date, and End date if known, such as a planned retirement date. Outputs are years of Service, now and at the Next milestone, the date of the next Milestone, and vacation days granted, this year (Yr0) and for the next four years.
[Service] =IF([@Start],ThisYear-YEAR([@Start]),"")
The policy is implicitly based on the calendar year in which the service milestone is reached, not full years. Days granted is then indexed to the policy. The default MATCH type finds the largest Policy[Service] value which is less than or equal to the Team[@Service] years, which returns the days granted.
[Days] = IFERROR(IF(AND([@End]>0,ThisYear>YEAR([@End])),0, INDEX(Policy[Days],MATCH([@Service],Policy[Service]))),0)
Another input column [Add] allows adjustment for carry-over days, which are added to the current year.
[Yr0] = [@Days]+[@Add]
The five-year horizon is for planning purposes. Table headings cannot be formulas, so above the column headings Yr0, Yr1, etc. are the actual years, hybrid C$R name VacYear. For each future year, as members reach a new milestone, their days granted are indexed to the policy. If [@End] is given, there are no more days due as of the year after that.
[Yr1], [Yr2], etc. =IFERROR(IF(AND([@End]>0,VacYear>YEAR([@End])),0, INDEX(Policy[Days],MATCH(VacYear-YEAR([@Start]),Policy[Service]))),0)
The next milestone years of service uses exact MATCH to find if a milestone occurs this year, then adds 1 row if it has already passed. If this is not a milestone year for the member, exact match will return "#NA". In that case, the default “less than” MATCH type is used as with Grant, and adds 1 row to get the next milestone row.
[Next] =IFERROR(INDEX(Policy[Service], IFNA(MATCH([@Service],Policy[Service],0)+IF(StartMDD< TodayMDD,1,0),MATCH([@Service],Policy[Service])+1)),"")
StartMDD and TodayMDD are defined in Name Manager =100*MONTH(date)+DAY(date), to determine if a date in the current year has passed yet. This logic is also used in the Startup File.
Finally, the date of the next milestone is determined for display on the Calendar.
[Anniv] =IFERROR(EDATE([@Start],12*[@Next]),"")
Member exits are highlighted in red fill. New hires can be entered as placeholders with future start dates. These are highlighted in two fill colors, green for the current year, as imminent or recent hire, and blue for future years. The future vacation days are calculated based on the start year entered.
Lastly, to the right of the Team table is a single hidden column, with a single formula in the cell named Active. This will provide the list of active members to the Input sheet, filtering out any future placeholders.
Active =FILTER(Team[Name],Team[Start]<=TODAY()).
Input Sheet
The Input sheet is the retail shelf of the tool, where the team members interact with it. The Input table has three data entry columns, each with an Input Message. Calculated columns give feedback to the user and feed the Report table. Error checking and Conditional Formatting are applied. To the right of the table is copy of the Calendar from the Report sheet.
Input Columns
Required entries are minimal, just Name and Date. Name is a Data Validation list using the source =Active#, which returns the spilled array from the FILTER formula above. Note, for the list to reject invalid names, there cannot be any blank names in the Team table.
Date is as described above; entry format is per computer setting, display format is per DateOrder and DateSeparator settings as one-time settings. If the team members are in different countries with different conventions, take care to avoid confusion.
The third entry column is optional, to enter a half day. Lower case x cuts the day in half. The implicit policy is that half day is the smallest increment for days off.
Calculated Columns
There are six calculated columns; two are for process control, four give user feedback. Except for [E] for error tracking, each formula begins by displaying blank until a date is entered. This is a way of being kind to the user.
=IF(ISBLANK([@date]),"", etc.
[Year] of course can be derived from [Date], but is explicitly displayed as the basis for vacation tracking. If someone begins entering vacation for next year, it is tracked against next year’s vacation.
[Year] =IF(ISBLANK([@Date]),"",YEAR([@Date]))
[Off] is not helpful in itself, but exposes the key input to [Used] and to the Report[Have] column. It is the product of two conditions. First, DateTypeInput is the Input table’s version of the DateType logic discussed above, resulting in 0 (FALSE) for workdays and >0 (TRUE) for non-workdays, specifically 1 for weekend and 2 for holiday. The first IF statement assigns -1 to non-workdays and +1 to workdays. The second condition is to allow half-day increments. Any entry in [Half] multiplies the first conditional result by 0.5.
[Off] = IF(DateTypeInput,-1,1)*IF(ISBLANK([@Half]),1,0.5)
Remaining columns give feedback to the user. [Type] displays the date type of the entry, and helps clarify the +/- value of [Off] by adding “off” or “on”.
[Type] =IF(ISBLANK([@Date]),"",CHOOSE(DateTypeInput+1,"workday off","weekend on","holiday on"))
[Used] calculates how many days have already been recorded for the member for the [Year]. The first step sums the positive [Off] days. The second step adds the sum of negative [Off] days, if CompDays is enabled.
If CompDays are enabled, the day is subtracted (the -1 day is added) from Input[@Used], for tracking the individual’s days, and from Report[Used], for tracking team usage. If CompDays are disabled, there is no effect on usage.
[Used] =IF(ISBLANK([@Date]),"",SUMIFS([Off],[Off],">0", [Name],[@Name],[Year],[@Year])+IF(CompDays, SUMIFS([Off],[Off],"<0",[Name],[@Name],[Year],[@Year]),0))
If CompDays are disabled as a policy yet certain individuals are due comp days, the Team[Add] column can be used to adjust the days per member for the current year.
[Left] finds the days due the member for the input year and subtracts [Used]. Here INDEX looks in the full table for MATCH on both row and column. VacationYears is the range one row above the Team table headers that contains the actual years.
[Left] =IF(ISBLANK([@Date]),"",IFNA(INDEX(Team,MATCH([@Name], Team[Name],0),MATCH([@Year],VacationYears,0))-[@Used],""))
Error Tracking
Possible errors are not always obvious while building the tool, but hopefully are exposed as you begin to use it. Here I track three types of error; date with no name, duplicate entry, and days over policy (negative [@Left]).
Data Validation is a great way to prevent errors and give helpful feedback. It might not be the most practical method to trap every error. For example, some errors are based on the combination of two fields. This is worth a brief elucidation.
How would you trap a duplicate entry? In a single column, duplicate entries can be prevented by Data Validation rule. In this example, the value in each column must be unique. The same value can exist in the column A range and the column B range, but not within the same column.
Allow: Custom Formula: =COUNTIF(relative column range,relative cell)=1 Example: =COUNTIF(A$2:B$21,A2)=1
Here, a duplicate Input record is when two rows have the same name and the same date. Theoretically, this could be checked with COUNTIF, by concatenating the two columns into one, or by COUNTIFS, as shown below. This can become a messy point to design around, and to get the table and Data Validation to play well together.
The method I use here is the “traffic cop”: let the offense occur, identify it by formula, and expose it to the user on the sheet. I use the small column [E] to flag errors, assigning them each a number:
Name is blank and Date is not
Name and Date are duplicated
[@Left]<0.
[E] =IF(AND(ISBLANK([@Name]),NOT(ISBLANK([@Date]))),1, IF(COUNTIFS([Name],[@Name],[Date],[@Date])>1,2,IF([@Left]<0,3,0)))
The column is formatted to hide zeroes (0;;). Conditional Formatting turns error rows red font, and a conditional message appears above the table, only for the current year. Two adjacent cells deliver the message in red font:
=IF(COUNTIFS(Input[E],">0",Input[Year],ThisYear),"Errors found:","") =IF(COUNTIFS(Input[E],1,Input[Year],ThisYear),"Err 1: missing name; ","")&IF(COUNTIFS(Input[E],2,Input[Year],ThisYear),"Err 2: duplicates; ","")&IF(COUNTIFS(Input[E],3,Input[Year],ThisYear),"Err 3: over policy","")
This is a “soft validation” and is a reasonable and adequate solution for the need.
Conditional Formatting
Conditional Formatting is always a key element of elegant user feedback. Here, in addition to the nine date formatting conditions and the red font error condition, there are four conditions that set fill color for the entire row.
First, previous years are shaded dark gray, and past dates in the current year are lighter gray. Records must be kept until the year is closed out, that is, any days left at the end have been recorded as carryover days in the Team table. This is a January maintenance task.
Past dates should not be deleted until the year is closed out. At the end of the year, the manager and member align that all days due were taken, with comp days if relevant, or that there are unused days to carry over to the next year. Once all carry days are recorded, the old year’s records can be deleted. They don’t need to be, but they have no value once the new year has begun.
Then there are two user-relevant conditions.
When a member wants to take a vacation, it may be approved or denied based on the needs of the team. This feedback of possible denial is given immediately. When the manager diligently maintains staffing requirements, then as vacation requests are entered, the Report table determines which days will be short-staffed. This condition then shades the Input rows for those dates, and the team has to work it out to maintain capacity. Name Manager defines the condition:
ShortInput =INDEX(Report[Net],MATCH(Input[@Date],Report[Date],0))>0
There may be an implicit expectation of “first-come, first-served”, but in reality the table cannot tell which entry first caused the shortage. Thus, resolution is by honor system, voluntary compromise, or seniority.
Finally, another color highlights non-workdays. This gives added exposure to the secondary purpose of the table, to track non-workday coverage. The condition is =DateTypeInput.
There is one condition that cannot be shown on the Input table. The Report table has every date, so when there are too many people working, it shows there. The Input table only has the dates entered, so over-staffing is not visible. This can occur in a shutdown or slow-down scenario. The manager then needs to work it out with the team to take mandatory vacation or some other measure.
Calendar Copy
Next to the Input table is a linked image of the Calendar (see Camera Tool in Chapter 3, Formatting). The actual Calendar table resides on the Report sheet. Display of special dates on the Calendar is co-controlled by two sets of checkboxes, one on each sheet, linked to the same cells. Here members use the Calendar to see upcoming events as they plan and make their entries.
Report Sheet
The Report sheet is mainly for the manager, and may occasionally be of interest to members. The sheet has one selection cell, StartSelect, to set the start date of the table. There are three start date options: This year, Today, Next year. This short list is maintained in Data Validation, and in Name Manager as the array StartList for use in defining the start date:
StartDate =CHOOSE(MATCH(StartSelect,StartList,0), DATE(ThisYear,1,1),TODAY(),DATE(ThisYear+1,1,1))
Most of the time, starting today is most useful for tracking the immediate future. Occasionally it may be necessary to start from the beginning of this year or next year.
Special Table
Special is an input table in which the manager can list any special event requirements for either plus or minus staffing. It has three input columns; [Date], [Need] and [Comment]. Positive [Need] is for non-workday coverage, or special events when the full staff is required. Negative [Need] is for workdays that are shut down or reduced staff. This can be used to balance days that are short, such as the day before a holiday. When [Need] is 0 it indicates a shutdown.
[Type] labels the entry with one of four strings based on DateType described earlier. The difference from other tables is when it’s a workday, if [Need] has a value it is called “± help”, and if it’s blank or 0 it calls it “shutdown”.
[Type] =IF([@Date],CHOOSE(DateTypeSpecial+1,IF([@Need],"± help","shutdown"),"weekend","holiday"),"")
[Net] then goes to the Report table to see if the need has already been met or not. This will be described in the next section.
[Net] =IFERROR(INDEX(Report[Net],MATCH([@Date],Report[Date],0)),"")
Conditional Formatting shades rows gray to be ignored when they are in the past, or when there is no [Net] requirement.
Report Table
The Report table summarizes the work requirements and staffing day-by-day. This is a list of every date from the selected start date for about a year and a half, and can be extended as needed by copying the bottom row down.
[Date] =IF(N(Above),Above+1,StartDate)
Date header columns to the left of [Date] give the [Wk], [Mo] and [Year], formatted to hide duplicates as previously demonstrated. To the right of [Date] is [Type] = workday, weekend or holiday.
The heart of the table is the next four columns which present the staffing numbers.
[Min] is the base staff requirement for normal days. For weekends and holidays (DateTypeReport = TRUE) or if there is a shutdown defined in the Special table, [Min] is 0. For workdays, [Min] is StaffMin, based on StaffFull, (the current roster) and MinStaff (the % configured in Settings).
StaffMin =ROUND(StaffFull*MinStaff,0) StaffFull =COUNTIF(Team[Start],"<="&StartDate)-COUNTIF(Team[End],"<="&StartDate) [Min] =IF(OR(DateTypeReport,ScheduleShutdown),0,StaffMin)
[Extra] is the plus or minus [Need] from the Special table.
[Extra] =SUMIF(Special[Date],[@Date],Special[Need])
[Have] is 0 for non-workdays and full staff for workdays, minus days off entered in the Input table. Because Input[Off] is negative on non-workdays, this becomes a positive in [Have].
[Have] =IF(DateTypeReport,0,StaffFull)-SUMIF(Input[Date],[@Date],Input[Off])
[Net] then compares demand and supply and shows how many more are needed. Net requirements can occur when a lot of people take off, or when there are special needs. The short condition is when there’s not enough staff, [@Min]+[@Extra]>[@Have]. The over condition is when there’s unneeded staff on non-workdays, or staff during shutdown or above slowdown requirement. In any of these cases, the difference is given. On normal workdays if there is more staff than required, [Net]=0. [Flag] converts any non-zero [Net] to text.
[Net] =IF(OR([@Min]=0,[@Extra]<0, [@Min]+[@Extra]>[@Have]),[@Min]+[@Extra]-[@Have],0) [Flag] =IF([@Net]>0,"short",IF([@Net]<0,"over",""))
The final column [Used] is for the purpose of tracking total vacation days taken versus days entitled. Days taken is the sum of Input[Off] for the year, reduced by comp days if CompDays is turned on. Entitled days is the sum of the current year’s column in the Team table.
[Used] =IFERROR((SUMIFS(Input[Off],Input[Off],">0", Input[Date],"<="&[@Date],Input[Year],[@Year])+IF(CompDays,SUMIFS(Input[Off],Input[Off],"<0",Input[Date],"<="&[@Date],Input[Year],[@Year]),0))/INDEX(Team[#Totals],1,MATCH([@Year],VacationYears,0)),0)
The alert condition to turn the font red in Report[Used] is the product of three conditions; ① the month of the date is greater than or equal to the alert start month, ② the cell value is below the alert threshold level, and ③ the date is in the current year.
=①AND(MONTH($E6)>=MATCH(AlertStart,Mons,0), ②$L6<AlertLevel, ③$B6=ThisYear) (active cell in row 6)
This formula uses cell references because it is entered directly into Conditional Formatting, and thus cannot use structured references.
In theory, each quarter should have about 25% of the total vacation days, but in reality there are peak seasons, and the procrastination effect. The more days off a person has, the more likely they will take days at the end of the year because they have to “use them or lose them”. When people leave many days unscheduled until late in the year, it poses the risk of an empty workplace.
Above the Report table are six informative cells in three groups.
Analysis Sheet
The Analysis sheet presents two sample pivot tables and accompanying charts.
The first pivot is sourced from the Input table. [Name] is in the Rows area, [Used] and [Left] are in the Values area, renamed “taken” and “due” respectively, [Year] is in the Columns area, filtered for this year. The chart shows the team members with how many days they have taken and how many they have left as horizontal stacked bars. The manager can use this to remind certain individuals to schedule their days off.
The second pivot is sourced from the Report table, with [Wk] in the Rows area and Average of [Used] in the Values area, Year in the Columns area and filtered for this year. The chart shows the total team days taken as a percent line graph over the weeks of the year, or remainder of the year, if StartSelect is today. This highlights how big the hockey stick risk is.
Comments