Employee timesheet example

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.

Employee timesheet example

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.

data validation

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.

data validation settings

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.

input message

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.

error alert

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’.

invalid date

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

date error

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.

time data validation settings

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.

input message

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.

validation error alert

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’.

invalid time

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

invalid time error alert

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.

overnight shift

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

formula

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.

sum

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!

Data validation menu option

Create an Employee Timesheet Using Google Sheets Includes Template

If you want to track your employees’ time, there is an easy way to do it using Google Sheets that does not involve complicated formulas or macros. This technique will also have the ability to calculate an overnight shift. The linked file can be copied into your own Drive and used as a template.

Data validation

One of the most difficult things when working with dates and time in Sheets is getting them in the cells correctly. A protection against that, data validation, can be used to reject invalid inputs.

Click on the Data menu option and then Data Validation.

Data validation menu option
Menu option for Data validation

Once the Data validation window pops up, complete it with the parameters in the following pictures including choosing Date from the criteria drop-down box and rejecting invalid input with a message.

Is valid date 
Choose date and is valid date

This will set data validation to show the message “Enter a valid date or time” if an invalid date is entered. Times are covered under the date formatting as card card-body bg-light so this data validation is also going to work if it’s a time. Now, if a user enters a date or time that Sheets doesn’t recognize as such, they will get an error message letting them know. There are several different ways to enter a date using a valid format, but there’s infinitely more ways to screw it up. You could do 2-20-18. You could type out February. But, as long as it’s valid, this timesheet will accept it.

The same thing is going to happen with time. If it’s 5:43 and the person types 5 43 in hits enter, it’s not going to work. You have to have a number, a colon, and a number. If you want seconds, do another colon and two digits after that such as 5:43:22.

The formula

Once the validation of the dates and times is setup correctly, the spreadsheet is ready for a start day and time and an end day and time. This formula is going to calculate the hours. So this formula is going to look a little complicated at first, but it’s really not too bad.

Copy and paste this code for column D

=((DATEVALUE(B5)+TIMEVALUE(C5)-(DATEVALUE(B4)+TIMEVALUE(C4)))*24)

Is valid date 
Timesheet showing formula

As seen in the shared sheet, this formula takes the row it is on and subtracts it from the row above it. To do arithmetic with these dates and times, Sheets has to convert them into a value first. That is what the DATEVALUE and TIMEVALUE functions are doing. The result will be a fraction of a day so multiply that by 24 and that gives you the number of hours worked. Every other row, you want to put in this formula.

If this person takes breaks, just repeat the same formula. If you want to make this longer, just copy and paste it down as many times as you want.

Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you'll like that.

Conclusion

After following this tutorial, you should be able to create a time sheet to track the hours of your empoyees. If you have any questions, feel free to leave a comment below.

Follow image below for the live Google Sheet with this data

docs share icon