This article looks at managing holiday using Excel 2010 and making use of the following Excel features:
- INDIRECT function
- NETWORKDAYS function
- Format as Table
- Conditional Formatting
The finished file is available here (Holidays). However this article explains how to use the finished article and how to create it, so read on (or bookmark me for future reference).
Using the Holidays Workbook
The workbook is made up of 5 sheets (or tabs). We’ll look at each tab in turn.
This tab pulls the data from the individual employee holiday sheets and totals holiday taken as well as remaining.
To use your employee names in the summary:
- Change the name in column A of the table
- AND also the matching sheet tab. The name must be the same in both the Summary table and on the sheet tab, including capitalisation and punctuation
To add more employees into the summary:
Extend the table by clicking and dragging the table extension handle down the required number of rows from the little blue marker in the bottom right hand corner of the table
Figure 2: Table Extension Handle
- Next you need to create a new holiday sheet for each employee. The Template sheet has been set up for this purpose
Right click on the Template sheet tab
Figure 3: Sheet Tab Menu
- Choose Move or Copy… from the sheet tab menu
The new sheet can be positioned as you choose, but should be before Reference. Figure 4 below shows that the new sheet will be positioned before the Reference sheet and as a duplicate of Template
Figure 4: Move or Copy Sheet Dialog Box
- Tick the Create a copy box
- Click OK
- Right click on the Template(2) sheet tab and choose Rename from the sheet tab menu. Enter the name of the employee
- The newly created record can be summarised by adding the employee name to the Summary sheet (as described above). Remember that the name must be the same in both the Summary table and on the sheet tab, including capitalisation and punctuation
- Repeat this process until you have sufficient sheets for your employees and they have been added to the Summary table
An exact replica of the employee sheets, but named Template for use in creating extra employees.
Each employee sheet works in the same way and is used as shown below.
The Reference sheet contains two sets of information:
- List of bank holiday dates 2012 to 2015 for use in calculating working days between two dates
- A table for calculating holiday allowance for part time employees
Bank Holidays List
This list of bank holidays is taken from http://www.direct.gov.uk/en/employment/employees/timeoffandholidays/dg_073741 and represents English and Welsh bank holidays as current at May 2012. It is simply a list of dates and may be over typed if these are not appropriate for your company.
The list of bank holidays is a named range (group of cells) and this name is used in the calculations of working days. Details of how to create and modify the named range of cells is in the second part of this article.
Part Time Holiday Allowance Calculation
Figure 5 Part Time Allowance Calculation
The orange filled cells in this table are for you to input the correct information, whilst the light grey filled cells are calculated. The calculation calculates the percentage of full time working days they will work and then works out what that percentage of a full time holiday allocation is. These are based on the number of working days in the allowance period, taking account of bank holidays.
Creating the Workbook
This part of the article takes you through step-by-step recreating the exact same workbook as the one you can download. You do not need to use the same words or the exact same layout. However if you are not confident creating Excel formulas you may find it easier to follow each step. However feel free to have a go at changing things around if you want. After all trial and error is a big part of how we learn.
The first sheet to create is the Reference sheet.
Rename the sheet as Reference, by right clicking on the sheet tab and choosing Rename, then type in Reference and press Enter.
To create a list of bank holidays and name the cells for easier reference in working day calculations:
- Look up the bank/public holidays for your location
- In cell A1 enter Bank Holidays
- In cell A2 enter the first date
- Format the column as short date. I select the column (click the letter A on top of the sheet) and open the Number Format drop down box in the Number group on the Home tab and choose Short Date, however the method used is not important
- Repeat this until all the required dates are listed
- Now to name the range. Select all the cells containing dates
- Click into the Name box in the Formula bar
- Type in Bank_Hols and press Enter
NOTE: When naming ranges you can’t use spaces and the capitalisation is important
Now to create the holiday allocation calculations. This is my preferred method of calculating part-time allowance but please ensure it meets your company policies before implementing:
- In cell C1 enter Part Time Holiday Allowance Calculation
- In cell C2 enter Allowance End Date
- In cell D2 enter Allowance Start Date
- In cell E2 enter Full Time Allowance
- In cell F2 enter Avg. Days worked per week
- In cell G2 enter Full Time Working Days
- In cell H2 enter Part Time Working Days
- In cell I2 enter % Of Allowance
- In cell J2 enter Holiday
- Select cells C3 to F3 in order to apply the cell background and font formatting in the next step
- In the Home tab, from the Styles group, choose Cell Styles and apply the Input style
- Select cells G3 to I3 in order to apply the cell background and font formatting in the next step
- In the Home tab, from the Styles group, choose Cell Styles and apply the Calculation style
- Select cell J3 in order to apply the cell background and font formatting in the next step
- In the Home tab, from the Styles group, choose Cell Styles and apply the Output style
- Select cells C3 to J3 in order to add the borders in the next step
- From the Home tab in the Font group, click the Borders button and choose All Borders
- In cell C3 enter the start date of the holiday allowance period, I used 01/01/2012
- In cell D3 enter the end date of the holiday allowance period, I used 31/12/2012
- In cell E3 enter the full annual holiday allowance, I used 25
- In cell F3 enter the average number of days your part-time employee works per week, I used 3
- The following steps will create the formula in Cell G3. It uses the NETWORKDAYS formula to calculate the number of working days between the start and end dates of the period and does not include public holiday dates as working days, by referring to the bank holiday list
- On the Formulas tab, in the Function Library group, from the Date and Time button, choose NETWORKDAYS. The Function Arguments dialog box opens
The arguments to be entered are shown below in Figure 6, you can enter the cell reference C3 and D3 by clicking into the appropriate box and then the cell on your sheet or typing the cell reference into the box (capitals are not imporant here). Bank_Hols can be entered by typing (capitals and underscore must precisely match how you named the range); OR click on the Use in Formula button in the Defined Names group of the Formulas tab and choosing the range
Figure 6 Cell G3 Formula
NOTE: the Holidays argument in the NETWORKDAYS function is optional, if left blank the list of dates are not taken into account when calculating the working days between two dates
- The formula in the formula bar will now read =NETWORKDAYS(C3,D3,Bank_Hols). Click OK
- To calculate the anticipated working days for the part-time employee we now need to work out what three fifths of the total is. However I want to be able to change the part-time working days so I’m not going to use that number in the calculation. In cell H3, enter the formula =G3/5*F3NOTE: this assumes a 5 day full-time week, as that is the same assumption made by the NETWORKDAYS function.
- In cell I3 the % of Allowance is calculated using the formula =H3/G3 and formatting the cell as a percentage using the Percent Style button in the Number group on the Home tab.
- In cell J3 the part time allowance is calculated by calculating the number of days which are the calculated percentage of the full time allocation. i.e. using the formula =I3*J3
The template sheet sets up the individual’s holiday tracking and is then in turn used to create each employee sheet as detailed in Employee Sheets on page 3. Rename the sheet as Template, by right clicking on the sheet tab and choosing Rename, then type in Template and press Enter.
- In cell A1 enter Allowance
- In cell B1 enter annual allowance in days
- In cell C1 enter Taken
- In cell D1 calculate the total of column E using =SUM(E:E)
- In cell E1 enter Balance
- In cell F1 calculate the difference between B1 and D1 using the formula = B1-D1
- Apply cell style Input to B1
- Apply cell style Calculation to D1
- Apply cell style Output to F1
- Select cells A1 to F1 and apply All Borders to these cells. All Border can be found on the Home tab, in the Font group by selecting the drop down next to the Borders button and choosing All Borders
- In cell A3 enter Start Date
- In cell B3 enter Start Time (if not full day)
- In cell C3 enter End Date
- In cell D3 enter End Time
- In cell E3 enter Days
- In cell F3 enter WARNINGS
- Select row 3 and wrap the text using Wrap Text in Alignment group of the Home tab
- Adjust the height of row 3 and column widths to fit text, either using the tools in the Scale to Fit group on the Page Layout tab
- In cell E4 create a formula to calculate the number of working days between the start and end date using the NETWORKDAYS function, which will be =NETWORKDAYS(A4,C4,Bank_Hols) or as shown in the box below
- In cell F4 create a function which will warn about part days using the IF function, which will be =IF(ISBLANK(D4),””,”Includes Part Days – Please Calculate Total Manually”) or as shown in the box below
To make the WARNINGS column turn red, if part days have been taken, use conditional formatting from the Styles group on the Home tab
- Use the option to highlight cells containing text
- Enter the text Includes in the condition dialog. The default is Light Red Fill with Dark Red Text, which is the option used
- Select cells A3:F4 and Format as a Table using the command in the Styles group on the Home tab
- From the extension handle in the bottom right hand corner of cell F4 , drag down to extend the table and copy the formulas and formatting down to row 25
See Employee Sheets on page 3 for details and create enough sheets so that each employee has a sheet.
- Rename the sheet as Summary, by right clicking on the sheet tab and choosing Rename, then type in Summary and press Enter
- In cell A1 enter Team Holiday Summary
- Select cells A1 to D1 and from the Alignment group on the Home tab, click Merge and Centre
- Format the merged cell using Heading 1 from the Cell Styles command in the Styles group on the Home tab
- Starting in cell B3, create a table like this one, where you have an entry for each employee in the employee column that precisely matches the sheet name, including capitalisation and spacing. The cell colour and borders will be added later
Now to create the formulas, which transfer the data from the sheets into this summary sheet. There are several methods, which would all work. I chose to use a formula that can be copied down the table and so works with the Format as Table feature. This method uses the INDIRECT function to build the reference to the sheet and cell, rather than it having to be changed manually.
In cell B4, I want to see the contents of cell B1 on the sheet with the same name as the employee listed in A4. Therefore to make the cell reference be Employee1!B1, I need to link together the contents of A4 and the text !B4 and then use the INDIRECT function to extract the contents of that cell. However if there is no entry there I do not want to see an error, so I need to encase the lot in and IFERROR function. So starting from the outside, and last function to be calculated by Excel:
- Create the IFERROR function giving =IFERROR( ……. ,””), where …. will be the rest of the formula
- Create the INDIRECT function inside the IFERROR giving =IFERROR(INDIRECT(….),””), where the …. will be replaced by the cell reference
- Create the cell reference inside the INDIRECT function, giving =IFERROR(INDIRECT(“””&$A5&”!B1″),””)
Note that the cell reference is made up of 3 components linked together with &’s and where text is identified by being enclosed in “speech marks”. The 3 components are:
- “ which needs to be enclosed in “” – hence there are 3 in a row
- $A5 refers to cell A5, the $ in front of the A, fixes the reference to the column, allowing for the formula to be copied down and across to populate the rest of the table. This component gives the sheet reference to the INDIRECT function
- !B1 adds the rest of the cell reference in, including the ! to divide sheet and cell portions of the cell reference. This needs to be enclosed in “”
- Copy the formula from B4 to C4 and change the B1 to D1 to return the total of holidays taken/booked to date
- Copy the formula from C4 to D4 and change the D1 to F1 to return the total of holidays remaining to be taken or booked
- Click into any cell in your table and format as a table using the Format as Table command in the Styles group on the Home tab
- On the Table Tools Design tab in the Table Style Options group, tick the Total Row tick box and add in totals to the columns required
You’re done! Don’t forget to save!!!