If you work as an administrative professional in a small office, chances are that you may be responsible for tracking employee attendance. In this article, you'll learn to create an attendance workbook by using Microsoft Office Excel 2003 that not only provides a place for you to record attendance but that also summarizes critical information for a scannable year-to-date status report. Employees and their supervisors often need to know information about employees' time off, including how many vacation days they have left, how many sick days they've used, and if they have personal days remaining. This workbook will provide that data.
Note Some companies offer employees a specific number of vacation, sick, and personal days each year. Other companies combine all of those days into a combined-time-off (CTO) bank. This article focuses on the first scenario. If you use a CTO bank, you can apply the same techniques described here, and your workbook will be even easier to create.
Define the workbook
The first step in creating an attendance workbook is defining your needs:
- What information do you want this workbook to provide? Do you need individual totals of vacation, personal, and sick days, or or do you need just a total of all days off?
- Do you need weekly or monthly breakdowns, or is quarterly data sufficient? Perhaps you don't require any breakdown, as long as you have a total to date of days taken off.
Note Unfortunately, you can't create one worksheet that contains all of the workdays in a year. Even excluding weekends, a typical year has 260 workdays (52 x 5), but Excel has a limit of 256 columns. However, one worksheet for each 3-month or 6-month period should be sufficient.
Set up the workbook
The workbook in the following example contains a worksheet for each quarter and a worksheet that summarizes the year-to-date information.
To create this workbook
- Create a new workbook with a total of five worksheets.
- Name the worksheets: Year-to-Date Summary, 1st Q, 2nd Q, 3rd Q, and 4th Q.
- Select all five worksheets (hold down CTRL while clicking each worksheet tab), and enter column headings, starting in A1: Last Name, First Name, Vacation, Personal, Sick.
- On the Year-to-Date Summary worksheet, add other fields, such as SSN, Position, Supervisor, Hire Date, and Comments.
- On the 1st Q worksheet, fill weekday dates starting with 1/3/2005 in cell F5 and ending with 3/31/2005 in cell CQ5. (If you have people who work on weekends in your company, do a normal fill.)
- Repeat step 5 on the 2nd Q, 3rd Q, and 4th Q worksheets to fill 4/1/2005 to 6/30/2005, 7/1/2005 to 9/30/2005, and 10/3/2005 to 12/30/2005.
- On the Year-to-Date Summary worksheet, enter the employees' names and other data, and then sort by last name.
- Select the data in the Last Name and First Name columns, and click Copy.
- Switch to the 1st Q worksheet, and then select the other three quarterly worksheets.
- Click cell A6, and paste the names. The names now appear on all of the worksheets.
- Cancel the selection of all worksheets.
Important You will create formulas in the next section. For them to work correctly, the first five columns on all five worksheets must be identical.
In order to have the workbook count the number of days off and then provide a summary, you'll need to create two types of formulas. The first uses the COUNTIF function to count the number of days off in each quarter. The second uses 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.) to sum the totals from the four quarterly worksheets.
Create the counts
The COUNTIF function counts the number of cells marked with:
- A "v" and displays the number in the Vacation column.
- A "p" and displays the number in the Personal column.
- An "s" and displays the number in the Sick column.
On the 1st Q worksheet, paste the following formula for vacation days near the top of the Vacation column, in cell C6:
This formula looks at the range of cells for the entire first quarter and counts any cells that contain a "v".
The formula uses mixed cell references, so you can fill the formula into other cells and it will still search for the correct date range. Drag to fill the formula into all the other employee rows in column C.
Because the formula uses mixed cell references, you can also fill the formula into the Personal and Sick columns, in cells D6 and E6. Make sure that you change the "v" to a "p" for the Personal column and to an "s" for the Sick column. Or insert these formulas:
On each of the other three quarterly worksheets, paste the formula into cell C6, modify the range so that it reflects the cell containing the last weekday date in that quarter, and then fill the formula into all employee rows in the column. Also fill the formula into the Personal and Sick columns, making sure to first change the "v" to a "p" or an "s" as needed.
Note Depending on the number of weekdays in the particular year that you are in, you might be able to enter the formulas by selecting multiple worksheets. Before you do, however, verify that the worksheets contain the same number of columns.
Enter 3-D references
The summary worksheet pulls the data from each of the other four worksheets by using 3-D references, which refer to a cell range that spans two or more worksheets in the same workbook.
To create the vacation days summary for the first employee
- On the Year-to-Date Summary worksheet, click cell C6.
- Enter the following:
- Click the 1st Q worksheet tab, and then click cell C6.
- Hold down SHIFT while clicking the 4th Q worksheet tab.
- Click the end of the formula in the formula bar, and type a closing parenthesis.
The resulting formula should look like the following:
=SUM('1st Q:4th Q'!C6)
Fill this formula into the Personal and Sick columns and into every row that contains an employee record. The workbook is now ready for you to start entering data.
Unless people in your company work on holidays, go through and enter an "h" for each holiday date through the year.
When someone takes a day off, enter a "v" for vacation, a "p" for personal day, and an "s" for sick day. The workbook automatically counts the number of days off and records it in the appropriate columns on the quarterly worksheets. It also records the total number of days off on the Year-to-Date Summary worksheet.
Add other features
The workbook is fully functional at this point. However, here are a few more ideas for adding functionality.
Add conditional formatting
Some supervisors love to monitor day-off patterns. Is someone calling in sick every Monday or is someone taking a vacation day on the last day of each month when reports are due? One way to help distinguish such patterns is to add conditional formatting to highlight each type of day off in a different color.
Select all four quarterly worksheets and then create three formula-based conditional formats, such as the following ones, in cell C6. Then use the Format Painter to copy the formatting to all the other cells on the worksheet.
Important When you create a formula-based conditional format, Excel automatically applies absolute cell references. In order to have the formulas work correctly, you must delete the $ in each formula that you created in the Conditional Formatting dialog box before copying the formula to other cells.
Calculate days remaining
If you would like to calculate how many days each employee has left for the year, on the Year-to-Date Summary worksheet, to the right of the Comments column, enter two additional columns: Vacation Days per Year and Vacation Days Remaining. Enter a formula in the Vacation Days Remaining column that subtracts the value in the Vacation column from the value in the Vacation Days per Year column.
Prepare for printing
Some supervisors will want a printed version of the data, so it's helpful to set up the worksheets for printing at the outset. You can select all five worksheets before changing most Page Setup options. Here are some things you might want to change:
- Margins and page orientation To get the maximum amount of data on each printed page, set the margins to .25 all around and the page orientation to Landscape.
- Header and footers Because this is year-to-date data, you always want to know what date a printed copy refers to. You can create a header or footer that includes a date placeholder.
- Rows and columns to repeat To make it easier to track the data from page to page, set the worksheets to repeat the first row and the first two columns. You must set this option for each worksheet individually.
Insert new employees
To insert a row to add a new employee
- Select all five worksheets, and add the row to all worksheets at the same time.
- Select the Year-to-Date Summary worksheet, and fill the formulas into the new row.
- Select the four quarterly worksheets, and fill the formulas into the new row.
- Conditional formatting should be copied automatically, but you should test it by entering a "v" in a cell in the new row. The color that you chose for "v" should appear.
To avoid inadvertently overwriting the contents of a cell that contains a formula, set protection on each of the worksheets so that entries are restricted to only those cells that are date-related. This is especially important if other people will be entering data in the workbook.
Save workbook as a template
By making a few minor alterations, you can reuse this workbook year after year. Save it as a template after you enter the employee list but before you enter any days-off data. When the new year rolls around, create a new workbook based on the template, enter new dates, make any changes in the employee list, and you're ready to go.
Use with confidence
Although this workbook takes a little effort to set up, tracking attendance has never been easier. Employees will appreciate how easy it is to get an answer to that burning question, "How many vacation days have I used?" and supervisors will appreciate your quick response when they ask, "How many sick days has he taken this year?"
About the author Annette Marquis is a partner of TRIAD Consulting, the premier Microsoft Office System training firm for the International Association of Administrative Professionals (IAAP).