If you’re using Microsoft Excel and you would like to record your employee’s working hours on a timesheet, 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 if you're a lighter spreadsheet user.
Here’s an example of a simple timesheet, showing the date and time that employees have clocked in and clocked out, and showing the hours for each individual shift as well as 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. Here we have allowed only allowing 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, which we’ll look at in a moment.
Click on the Input Message tab. There’s a checkbox there to show input messages, which would make an input message pop up every time a user clicks on a cell to prompt them what to enter. After the first few times this is probably going to be unnecessary, so we’ve unchecked that, but you can put a message there to prompt your user to enter a date.
Click on the Error Alert tab. We’ve checked the box to show an error alert when an invalid date is entered, and 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. Here 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.
If you click ‘Retry’ and enter ‘6-1-18’, that’s fine and Excel will convert that to the correct format. Excel can take date entries in a variety of ways, but not every format will be recognised, and our error alert will make it clear when something is wrong.
Data Validation for Time Entries
We’ve also got Data Validation for the time entries. Highlight the time entry column and go to the Data ribbon and 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.
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.
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’.
This gives us an error alert prompting us to enter a valid time.
If we retry and 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, but not every format will be recognised, and our error alert will let the user know when something an entry is invalid.
Using Formulas with Data Validation
So now 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, so if we put an example of that there 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.
So now, let’s double-click on cell E9 and see what we’re using to calculate the number of hours for the shift.
What this is doing 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, so we have multiplied by 24 to get the result in hours.
To get the total for all the shifts, we’ve just used a simple sum formula that sums all the hours for the shifts to get a total.
With this simple timesheet, we can easily see how many hours we can pay our employee!
Using data validation, it’s simple and easy to create a timesheet for your employees that alerts the user when they have entered invalid data. Hope this tutorial has been useful for you and your business!