Building a one-size-fits-all timesheet is next to impossible. Still, certain principles and features are present in most timekeeping applications. In this article, I’ll show you a simple construct for tracking hours for a single workweek. It’s flexible enough to use as a template from week to week and year to year. If a single week isn’t adequate, you can adapt the sheet to biweekly or even monthly.

In our example timesheet, Monday is the first day of each workweek. Overtime hours accrue daily after eight hours because it’s the easiest to accommodate in a simple structure. You can easily tweak the sheet to suit your needs, since it probably won’t be exactly what you need.

I’m using Excel 2016 (desktop) on a Windows 10 64-bit system. You can can use earlier versions of Excel. Users can also update their timesheet using the browser edition of Excel. You can create the timesheet from scratch or download our sample sheet. This article assumes that you know how to perform basic functions, such as entering and copying formulas, applying basic formats, and applying conditional formatting rules.

SEE: Cost comparison calculator: G Suite vs. Office 365 (Tech Pro Research)

1: Determine your needs

When preparing a template for distribution, you need to consider the following issues:

  • How to validate input to eliminate typos and other invalid data.
  • How users will access the template. You can distribute it or allow them to access the sheet via a browser or web-based application. This article doesn’t extend beyond a local or server file.
  • How to protect the template from misuse.
  • How to secure confidential information, such as social security numbers, if required. Our example doesn’t contain any confidential data, so this step isn’t necessary.

2: Enter labels

Figure A shows a simple weekly structure. Although the shell allows for seven days, our example assumes a Monday-through-Friday workweek. As such, working overtime by working more than five days isn’t a possibility. It’s a limited example, but it’s a good place to start. We’ll build on this simple example over the next few months.

Figure A

This simple structure tracks work hours for a single week.

Your sheet will contain some of the following information:

  • Employee information, such as name, social security number, or employee identification number.
  • Company information, such as the department and manager.
  • A time period, which can be the first day of the workweek or the first day of a fiscal month. However, your date formulas will rely on consistency.
  • Appropriate times, which include clock-in and clock-out times and other reported times, such as sick hours, vacation hours, and overtime.
  • Subtotals and grand totals.
  • Approval signature(s).

Your first step is to add meaningful labels that describe input values and totals and to apply simple formats, such as bold, center, and borders. We’ll add formulas and apply features as we go.

SEE: Make Office 2016 work your way by changing these default settings (free TechRepublic PDF)

3: Automate the dates

The example uses the first day of each week, Monday, to identify the pay period. To avoid input errors, we’ll use a data validation control that accepts only Monday dates as follows:

  1. Select B2.
  2. Click the Data tab, click Data Validation in the Data Tools group, and then select the Data Validation option from the dropdown list.
  3. In the resulting dialog, choose Custom from the Allow dropdown.
  4. In the Formula control, enter the following formula (Figure B):

    =WEEKDAY($B$2,2)=1

  5. You could define an input message and alert at this point, but we’ll skip those options to simplify the example. Click OK.

Figure B

Use a formula that accepts only Monday dates.

Figure C shows the result of entering a date that doesn’t fall on a Monday. If you’d like more information on using data validation with dates, read Four ways to specify dates using Excel data validation. You could also limit user choices to specific employee names, departments, and managers using data validation, but we won’t do so in this article.

Figure C

The data validation rule rejects a non-Monday date.

Next, we’ll add formulas to return the date and day values in columns A and B. First, select A7 and enter the following formula:

=IF(B2<>"",B2,"")

Then enter the following formula into cell A8 and copy it to cells A9:A13:

=IF(A7<>"",A7+1,"")

It isn’t necessary to supply the day of the week, but your users will probably find it useful. Enter the simple expression =A7 into B7 and copy it to cells B8:B13

To see how the formulas work, enter a (Monday) date in B2. If necessary, apply the Short Date format to A7:A13. Then, apply the dddd custom format to display the day of the week in column B, as shown in Figure D. As you can see in Figure E, the formulas reference the date in B2 and return a week’s worth of dates and days.

Figure D

Apply a custom format to display the day of the week.

Figure E

Columns A and B display the dates and days of the week for the pay period.

4: Straight-time formula

You could require your employees to track their own overtime according to your rules, but that’s asking for a lot of do-overs. In our example, straight time applies to the first eight hours worked in a single day. (It’s the easiest rule to implement in such a simple structure.)

Enter the following formula in G7 and copy it to cells G8:G13:

=IF((D7-C7)+(F7-E7)*24>8,8,((D7-C7)+(F7-E7))*24)

If the total hours worked in one day is greater than 8, the formula returns 8. If the total is equal to or less than 8, the formula returns that amount. Don’t worry about losing overtime hours; we’ll deal with that next.

5: Overtime formula

Now let’s enter a formula that calculates overtime in a single day. Enter the following expression in H7 and copy it to H8:H13:

=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)

6: Format in and out time values

Users will input in and out values, which means these values are prone to errors. Fortunately, there are tools to help keep things on track. The first step is to apply the Time format. We’ll specify the 12-hour format, as shown in Figure F. If your users are familiar with the 24-hour clock, you can use the hr/mm format instead.

Figure F

Apply a time format for the in and out time values.

Consider entering default time values before distributing the file. Once a user changes a default value, it’s gone, so they should receive rudimentary training on how to enter time values to avoid frustration.

As is, there’s nothing to stop a user from entering an out value that’s less than its corresponding in value. Another data validation rule can help. Select D7:D13 and apply a custom rule (as you did earlier) using the following formula, =D7>C7, as shown in Figure G.

Figure G

Use data validation to protect the validity of out time values.

To protect your second list of out values, select F7:F13 and use the expression =F7>E7 as the validation rule. As you can see in Figure H, I added a meaningful error message to the validation rule.

Figure H

Data validation requires an out value to be greater than its corresponding in value.

You could also add a validation rule to the in values in columns C and E. Specifically, combine a Time constraint with a Between setting that accepts a full 24-hour day time value. Doing so will reject anything other than a time value. Users can still enter the wrong time, but at least it will be a time value.

SEE: 50 time-saving tips to speed your work in Microsoft Office (free TechRepublic PDF)

7: Format sick and vacation time

Users will enter sick and vacation hours as digits. Apply the Number format with two decimal places to accommodate partial hours or 0 if hours must be in whole units. In addition, set a data validation rule that allows users to enter values no greater than your regular workday (Figure I).

Figure I

Limit the number of hours an employee can claim for sick or vacation time in a single day.

8: Daily totals

All the input cells are ready to go. Now, let’s enter a formula that calculates daily totals. To do so, enter the following function in K7 and copy it to K8:K13:

=SUM(G7:J7)

9: Weekly totals

Mostly likely you’ll need to calculate weekly totals for each category (straight, overtime, sick, and vacation). To do so, enter the following function in cell G14 and copy it to cells H14:J14:

=SUM(G7:G13)

As yet, there’s no formula in K14. This value should be the same whether you add the values in column K or row 14. You can catch mistakes by cross-footing the two sums. Cross-footing double-checks totals by comparing columnar and row subtotals. To validate the integrity of your data, use the following expression in K14:

=IF(SUM(G14:J14)=SUM(K7:K13),SUM(G14:J14),"Error!")

About the only way this formula will return the error message is if someone accidentally changes one of your formulas. That can’t happen if you protect your sheet. However, there’s nothing wrong with building this into your sheet, because that won’t always be the case.

SEE: Hackers are using recent Microsoft Office vulnerabilities to distribute malware (ZDNet)

10: A few extras

At this point, your sheet is functional, but there are some things you might want to add. For instance, blank cells might be problematic for your accounting system. If so, enter a conditional rule using a formula in the following form to highlight blank cells:

=ISBLANK(cellreference)

If you go this route, change the time format for the in and out columns to the 24-hour clock so you can also enter 0:00 time values. The am/pm format doesn’t support 0:00 time values.

Some companies don’t allow you to use sick or vacation time to earn overtime in the same day. The conditional format rule

=AND(SUM(G7:J7)>8, OR(I7>0,J7>0))

will highlight daily totals that are greater than 8 if the sick or vacation value is greater than 0.

There are many business rules you can incorporate into this sheet. For instance, this example doesn’t allow for comp time. Nor does it identify holiday pay. The example isn’t suited for shifts that span two days. As you can see, a simple example can grow complex rather quickly.

11: Protect formulas

Once the sheet is ready for distribution, protect your formulas from misuse. To protect cells, do the following:

  1. Right-click an input cell (a cell you don’t want to protect).
  2. Click the Protection tab (if necessary).
  3. Uncheck the Locked option and click OK.
  4. Click the Review tab and then click Protect Sheet.
  5. In the resulting dialog, enter a password.
  6. Check only one item: Select Unlocked Cells (Figure J).
  7. Click OK.

Figure J

Protect your sheet.

To simplify the process, you can create a multi-range selection and unlock all input cells at the same time. Just hold down the Ctrl key while you click the cells. Our sample timesheet isn’t protected. Once protection is enabled, users can select only input cells.

Using the sheet

Each pay period, the user will open the original file and rename it to reflect the current pay period. It’s not the only way, of course, but it’s the easiest way. Employees can submit a printed copy or the electronic file.

The eight-hour overtime rule is the easiest to implement in a simple structure. Over the next few months, we’ll look at a few more examples that use a different structure and apply different overtime rules.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

More Office how-to’s from TechRepublic