Create an Employee Timesheet Using Excel | Includes Overnight Shift

Are you using Microsoft Excel and you would like to record your employee’s working hours? Here’s a quick tutorial showing you how to do that, using Data Validation to make the process faster and easier.

You can use Excel as part of Office 365 or you can use the free online version. The free online version is good if you are a lighter user.

Here’s an example of a simple timesheet. This timesheet shows the date and time that employees have clocked in and clocked out, and shows the hours for each individual shift. It also shows the total hours for all the shifts.

Data Validation for Date Entries

This sheet has been set up with data validation to make sure that the data entered for the dates and times are valid. Let’s take a look at that now. Highlight the column that has been used for the date entries, navigate to the Data ribbon, and left-click on Data Validation to bring up the window.

In the Data Validation window, you can set up rules for the cells in this column. Accordingly, we have only allowed date entries, and we have put in a range for the dates that are allowed to be entered in these cells. By allowing only date entries, if your user doesn’t enter a valid date, they’ll get an error alert prompting them to correct it. We’ll look at that in a moment.

Next, click on the Input Message tab. After clicking, you will see that there’s a checkbox there to show input messages. This checkbox makes an input message pop up every time a user clicks on a cell to prompt them what to enter. However, after the first few times, this is probably going to be unnecessary. Therefore, we’ve unchecked that, but you can put a message there to prompt your user to enter a date.

Error Alert Tab

Click on the Error Alert tab. As you will see, we’ve checked the box to show an error alert when an invalid date is entered. As a result, the alert will show a message prompting your user to enter a valid date.

To test Data Validation for our date entries, we’ll go back to our spreadsheet and enter something that’s not a valid date to see what happens. For example, we’ll attempt to enter ‘June 1st, 2018’.

Excel won’t like this because it doesn’t recognize the ‘st’, and we’ll get an error alert.

To fix this, if you click ‘Retry’ and enter ‘6-1-18’, that’s fine and Excel will convert that to the correct format. Excel takes date entries in a variety of ways, but not every format will be recognised. Our error alert will make it clear when something is wrong.

Data Validation for Time Entries

We also have Data Validation for the time entries. To see this, highlight the time entry column and go to the Data ribbon. Left-click on Data Validation. Here, we’ve only allowed time entries, between midnight and midnight.

Click on the Input Message tab. Again, we’ve unchecked this as we don’t need to see the prompt telling the user to enter a time value every single time an entry is made.

Now click on the Error Alert tab. We’ve set it up here so that if the user doesn’t enter a valid time, it’s going to give an error alert prompting them to correct it.

In addition, to test the Data Validation for our time entries, we’ll enter an invalid time. We’ll try to enter ‘2pm’ which Excel doesn’t recognize as it wants a space between the ‘2’ and the ‘pm’.

As a result of the bad entry, we get an error alert prompting us to enter a valid time.

Consequently, if we try again to enter ‘2 pm’ or ’14:00’ (military time), Excel will recognise it and turn it into the correct format. There are a number of ways we can make time entries in Excel. However, not every format will be recognised. Our error alert will let the user know when something an entry is invalid.

Using Formulas with Data Validation

As a result, we have data validation for all of the date and time entries. This is important, because we’ll be using formulas to calculate various values in the Total column.

Before we get into that, note that this timesheet can also do overnight shifts. If we put an example of that at the bottom, with the shift starting in the afternoon of the first day and ending in the morning of the second day, we’ll get the correct total in column E.

After that, let’s double-click on cell E9 and see what we’re using to calculate the number of hours for the shift.

This is combining the date and time that the employee clocked out, and subtracting the combined date and time that they clocked in. This results in a fraction of a day for the length of the shift. Multiply by 24 to get the result in hours.

Next, to get the total for all the shifts, use a simple sum formula that sums all the hours for the shifts to get a total.

That’s It!

In conclusion, with this simple timesheet, we can easily see how many hours we can pay our employee!

Using data validation, it’s easy to create a timesheet for your employees that alerts the user when they have entered invalid data. I hope this tutorial has been useful for you and your business!