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

Embed a Google Doc into a Microsoft Sway Presentation

If you’re using Microsoft’s Sway and you want to embed a Google Doc, there’s just a couple easy steps to do it.

Sway with Google Doc
The title of your Sway

Create a new Sway and give it a title. I’ve called this one Embedding a Google Doc.

Media group
Choose the media group

Next, click the plus sign to add more content. Choose the Media group then click the Embed option.

Embed
Choose the embed option

Video explanation

https://www.youtube.com/watch?v=dQT1h2Ph0CY”

Publish to the web
Publish your Doc to the web
Publish button
Click the publish button

Sway is now waiting for the embed code. You need to get the code from your Google Doc by going to the document’s File menu and choosing Publish to the web. Once in the Publish to the web page, you need to click the blue Publish button one time to open up the Doc. After you have clicked Publish, you can highlight the embed code and copy it to your clipboard.

Embed code for Docs
Embed option in Google Docs

Go back to your Microsoft Sway. Go to the box waiting for your input, and paste the code.

Embed card with code
Sway card with embed code

You’ve embedded that Google Doc. This is live. If you were to update this in Docs, this document that’s showing here would be updated. You can also interact with this document to a certain extent with the icon lower right hand corner. You can download it or print it.

Finished sway with Google Doc
Sway with embedded document

Embed a Live Excel Spreadsheet into a Microsoft Sway Presentation

If you’re using Microsoft’s Sway and you want to insert an Excel spreadsheet, there’s just a couple easy steps to do it.

Title of Sway
The title of your Sway

Create a new Sway and give it a title. I’ve called this one Embedding an Excel Spreadsheet.

Media group
Choose the media group

Next, click the plus sign to add more content. Choose the Media group then click the Embed option.

Embed
Choose the embed option

Video explanation

Create new Excel workbook
Create a new Excel workbook
Upload Excel workbook
Upload an existing Excel workbook

Sway is now waiting for the embed code. You need to get the code from Excel after you’ve uploaded it to OneDrive (or created it online to begin with). So, assuming you’ve uploaded your Excel flle, we’re going to step forward from that point. If you haven’t done that, you need to open up OneDrive and upload it.

Embed option in Excel
Embed option in Excel online

In the online Excel workbook, go to Share and embed and the code is ready at the bottom of the next window. You will be presented with a screen to select different parts of your spreadsheet, leave it at the default for now. Copy the embed code from your clipboard and go back to your Microsoft Sway. Go to the box waiting for your input, and paste the code.

Embed card with code
Sway card with embed code

You’ve embedded that Microsoft document. This is live. If you were to update this in Excel, this document that’s showing here would be updated. You can also interact with this document to a certain extent with the icon lower right hand corner. You can download it or print it.

Sway with embedded document
Sway with embedded document

Embed a Live Word Document into a Microsoft Sway Presentation

If you’re using Microsoft’s Sway and you want to insert a Word document, there’s just a couple easy steps to do it.

Title of Sway
The title of your Sway

Create a new Sway and give it a title. I’ve called this one Embedding a Word Document.

Media group
Choose the media group

Next, click the plus sign to add more content. Choose the Media group then click the Embed option.

Embed
Choose the embed option

Video explanation

https://www.youtube.com/watch?v=9Cnb9eqSvnA”

Create new Word document
Create a new Word Document
Upload Word document
Upload an existing Word document

Sway is now waiting for the embed code. You need to get the code from your Word document after you’ve uploaded it to OneDrive (or created it online to begin with). So, assuming you’ve uploaded your Word document, we’re going to step forward from that point. If you haven’t done that, you need to open up OneDrive and upload it.

Embed option in Word
Embed option in Word online

In the online Word document, go to Share and embed and the code is down here waiting for you. It’s all highlighted. Copy the embed code from your clipboard and go back to your Microsoft Sway. Go to the box waiting for your input, and paste the code.

Embed card with code
Sway card with embed code

You’ve embedded that Microsoft document. This is live. If you were to update this in Word, this document that’s showing here would be updated. You can also interact with this document to a certain extent with the icon lower right hand corner. You can download it or print it.

Sway with embedded document
Sway with embedded document

Google Sheets – Compare two lists for matches or differences

If you’re using Google Sheets and you have two separate columns of data in which you’re looking for matches, there’s a pretty easy way to highlight the matched items. You can also do it the other way around and highlight items that don’t match. You’re typically doing this because your lists are long and you can’t do it visually. However, for this example the lists are purposely small so it’s easy to see.

Two columns with matches highlighted
Matches between two columns are highlighted

Conditional Formatting

We’re going to do it through Conditional Formatting. Go to your menu, then to Format, and select Conditional formatting. The Conditional formatting menu options will pop up on the right-hand side and there’s everything you need right here.

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Conditional formatting menu option
Menu option for conditional formatting
Rules for conditional formatting
Rules for conditional formatting

First, apply the conditional formatting to the range which, in this shared spreadsheet (which is also embedded near the end of this page), is C1 to C10. Note that the range is different in the video but the process is the same. You can select the range with your mouse or type it in as C1:C10.

At first, the range should be highlighted because it is set to highlight if it’s not empty. Change this to a custom formula, which is at the bottom. Click on Custom formula is and then it gives you a box that’s waiting for the formula.

Custom formula
Custom formula

You are going to be typing in a formula just like you’re typing into a spreadsheet cell. One of the differences though is now Google Sheets is not going to help you. Once you start typing the function, no helper text will pop up to explain the function. If you want help, type the formula into a cell in the spreadsheet and it’ll help you build it. Then, you can paste it into the custom formula box.

Video explanation

MATCH formula

You want to use a function called MATCH. MATCH is going to look at each cell in the range and check to see if it exists in the other range that you specify. The first parameter that you want, it’s going to do this one cell at a time, is C1. Now type a comma and Sheets will be looking for the range that you want to try to look up C1 in to see if it’s a duplicate. You don’t want this range to shift down every time it evaluates a cell on the right., so use dollar signs before the row numbers to fix the range as such A$1:A$7.

Next, your spreadsheet wants to know if this is going to be an exact match. Enter a 0 (zero) for yes so if it finds exactly Yellow. Now close it off with the parentheses. Your code should look like this:

=MATCH(C1,A$1:A$7,0)

The box that contains this function should have changed from red to blue meaning that the function is now a valid function. It should be working after the box turns blue. You can see it highlighted the color red in green because red is on the list on the left but it didn’t highlight orange because orange isn’t in that list. If you click done, you’ve highlighted everything that exists in the other list.

=ISNA(MATCH(C1,A$1:A$7,0))

If you want to do this the opposite way, and highlight the items in this list that aren’t in the list on the left, wrap your formula in the function called ISNA. The ISNA function is saying – look, if the MATCH function doesn’t work, highlight it. Not if it does work. After the ISNA, put a parenthesis and then go to the very end and close it with the parenthesis.

View of Sheet with ISNA applied

Conclusion

After following this tutorial, you should be able to highlight matches or differences between two columns. 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