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!

Add an Email from Gmail into Google Keep – No Extensions

If you have a Gmail email and like to take notes in Google Keep, there have probably been times where you would like to add an email to Google Keep so that you can refer to it later. There’s a way to do this now without extensions or plugins, in just a few easy steps.

First, go to your Gmail and find the email that you’d like to store in Google Keep, and open it. On the far right-hand side of the screen you’ll find a panel with a Google Keep icon. Left-click on this icon to open Google Keep.

keep in gmail

With Google Keep open, you’ll find on the far right at the top there’s a button with a yellow plus sign which gives you the option to create a new note. Left-click on this button to create a new note for the email.

take note in keep

This brings up a window where we can add a title and text to the note. You’ll see that the note already contains a link to the email.

email link note

Add a description to the note, in this example we’ll clarify that it’s an email about time management software. Then click ‘Done’ to add the note to Google Keep.

keep note description

Now, let’s go to Google Keep. Our note is here!

note in keep

We might want to color all our Gmail notes the same color to make them easier to spot. Click the Color palette icon at the bottom of the note and select a color for this note. We’ll make it red.

keep note color

We can click on the link inside the note to bring up the email in Gmail.

keep note link email

It’s important to remember not to delete the email, as our note does not make a copy of anything – it just acts as a link-back to the original email. However, you can archive the email in Gmail to get it out of your inbox, and the link will still work fine.

gmail archive email

You can also have Google Keep remind you about the email later, for example next week. Left-click the Reminder icon at the bottom of the note in Google Keep, and select when you would like to be reminded.

keep note reminder

You can then archive the note in Google Keep as well so it doesn’t clutter the page, and you’ll still get a reminder notification, so you can deal with it at the right time. To do this, left-click the Archive icon at the bottom of the note.

archive note keep

That’s it! We’ve added our email to Google Keep. The email is archived (out of our Gmail inbox), the Google Keep note is archived (out of our Google Keep home page) and we’ll be getting a reminder about the email next week.

Hope this tutorial has been useful for you and your business!

Tutorial Video

Google Sheets – Group Rows and Columns with Linked Example File

When working with spreadsheets of data in Google Sheets, you’re often switching between different levels of information, and you might find yourself wishing you could control the visibility of data you don’t need so that you can make the presentation clearer. There is a way to do exactly this, by grouping rows and columns so you can easily collapse and hide them or expand them into view.

Grouping Rows

In the example below and in this linked example Sheet, we have individual sales data for a range of products, with quarterly subtotals, and an annual total. To see the quarterly sales information more clearly, we would like to hide the data for the individual products.

Rows not hidden yet

To do this, we’ll create groups for the data we don’t need to see. Begin by selecting some rows that you would like to hide.

First row selected

With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 9’ (or whatever row numbers you have selected) from the menu.

Right-click menu

This will create a group for the selected rows, which you will see depicted by a thin bracket on the left of the selected rows, along with a small button with a minus sign inside it. This button lets you collapse or expand the group.

Minus sign

If you click the minus button, the group of rows will be collapsed and hidden from view. The button remains visible (this time with a plus sign) so that you can click on it again to expand the rows.

Arrow pointing to minus sign

Repeat these steps for each of the other quarterly product data so that you end up with only the quarterly sales figures showing.

Collapsed rows

Now we’re showing only quarterly sales figures, and the data is much easier to read!

Step-by-Step Video

Layered Groups

Let’s say we want to be able to show only annual sales figures as well. We can create another group, alongside the groups we just created, to toggle on and off everything except the annual sales data.

Select all of the rows corresponding to individual product data and quarterly sales figures. Do not include the row with the annual sales figures at the end, or the header row at the top.

Expanded rows

With the rows selected, right-click anywhere inside the selection and select ‘Group Rows 2 – 31’ (or whatever row numbers you have selected) from the menu.

Right-click group rows

This will create a new group that allows us to collapse all the quarterly sales data and only show annual sales data, alongside the groups that we created before.

Second level minus

Now if you click the button for the new group, only the annual sales figures will be showing.

Parent and child rows collapsed

Grouping Columns

It is also possible to create groups for columns, using the same steps as we used for rows. Select the columns that you would like to hide.

Columns selected

Right click anywhere inside the selection and click ‘Group Columns C – D’ (or whatever columns you have selected) from the menu.

Right-click group columns

This creates a group for the columns, depicted above them by a thin bracket and a toggle button, which we can click to collapse or expand the columns as we did with the rows.

Minus columns

Group Options

Right clicking on the group bracket brings up a menu that gives us some options about how to display the group. For example, if you would like the toggle button to appear at the bottom of the group, right click on the group bracket on the left of the rows, and click ‘Move +/- button to the bottom’ from the menu.

Move plus/minus to bottom

You can see there are several other useful options on the menu that give you control over how your data is being displayed.

That’s it!

Grouping rows and columns in Google Sheets gives you control over what is displaying in your spreadsheet, giving you the ability to highlight different levels of information at different times without the view being cluttered with data you don’t need to see. Hope this tutorial has been useful for you and your business!

Follow image below for the live Google Sheet with this data

docs share icon