Google Sheets & Google Forms: Clock In and Clock Out System – Spreadsheet Template for Small Businesses

Alright, in this post, we will be creating a straightforward clock-in and clock-out system using Google Forms and Google Sheets. First, let’s create a form. To access Google Forms, you can go to forms.google.com. Once there, click on “Create a new form.” I will quickly name this form “Clock In/Out.”

Create a New Form

So, what I’m going to do here is create a simple multiple-choice question. The first option will be to clock in or sign in, you can name it as per your preference. The second option will be to clock out. We need to assign a name to this question, so I’ll call it “Action.” This will be a required field.

In addition to this, I’ll add a couple more short answer questions. The first one will be for the employee ID, and it will also be a required field. We’ll apply some validation to ensure that the employee ID is in the correct format. For response validation, I’ll set it to accept numbers. Let’s say the employee ID should be a five-digit number.

To enforce this, I’ll use a regular expression pattern. The pattern will be “\d{5}”, which means it should consist of five digits. For the custom error text, I’ll set it to “Enter a valid ID.”

I’ll also add another field called “Verification Code.” This field is optional and can act as a password to prevent unauthorized form submissions.

That’s pretty much it for the form setup. All fields, including the verification code, will be required. I won’t be adding any other elements to the form. So, if we preview the form now, it will look like this:

[Form Preview]

When we submit the form, it will prompt us to fill in all the required fields. We’ll need to select either “Clock In” or “Clock Out,” and then enter our five-digit employee ID. If we try entering a four-digit ID, it won’t be accepted.

Now, the final step is to ensure that the form responses are stored in a Google Sheet.

Create a Google Sheet

Go to the form builder and navigate to the “Responses” section. Click on the option to connect the form to a Google Sheet. You can either select an existing sheet or create a new one. If you choose to create a new sheet, you can keep the default name or rename it as per your preference. This will generate a spreadsheet where the form responses will be stored.

Once the Google Sheet is set up, you can go back to the form itself. To share the form with others, click on the “Send” button, which will provide you with a link to the form. You can share this link with others, and when they open it, they will see the form interface.

Now, to demonstrate the functionality, let’s imagine we have a person clocking in by entering their employee ID as “12345” and a verification code as “56789.” After submitting the form, if we go back to the spreadsheet, we will see that the time of clocking, action (clock-in), employee ID, and verification code have been automatically recorded.

If another person uses the same form, they can clock in with their own employee ID and verification code. The spreadsheet will update accordingly, showing multiple entries with different details.

To illustrate clocking out, we can enter “22222” as the employee ID and the respective verification code for the clock-out action. Similarly, if someone enters incorrect verification code, it will still be accepted, but we can use it for verification purposes later.

In the spreadsheet, the clock-out entries will include the clock-out time, verification code, and employee ID.

You can continue the process by having other individuals clock in and out, entering their respective details. The spreadsheet will populate with the relevant information for each entry.

Now, we can work with the spreadsheet to analyze and process the data further.

Create another Google Sheet

To analyze the data being entered, we can create another worksheet called “Employees.” In this worksheet, we’ll store employee IDs, verification codes, names, departments, or any other relevant information about the employees. You can copy the employee ID and verification code data from the form responses and remove any duplicates to create a clean list.

Next, we’ll create another worksheet called “Data Processed” where we’ll perform calculations and formulas. To start, we’ll copy the data from the form responses to this new worksheet. You can manually copy the names, but for the rest of the data, we’ll use a formula.

In the “Data Processed” worksheet, enter the following formula in the first cell under the “Time In” column:

excel

={Data!A2:A}

This formula will create an array and copy the timestamps from the “Data” worksheet only for clock-in entries. It will leave the cell blank for clock-out entries. Press Enter and apply the formula to the entire column.

To format the “Time In” column, click on the cell containing the desired format, use the paint format tool, and apply it to the entire column.

Next, we’ll create a “Time Out” column.

Time Out

To determine the time out for each employee, we need to find the next occurrence of their employee ID. If the next occurrence is a clock out, it will be considered as the time out for that employee.

To accomplish this, we can use the VLOOKUP function. Let’s modify the formula to include the necessary conditions. Here’s the updated formula for the “Time Out” column:

excel

=IF(C2="Clock In", IFERROR(VLOOKUP($A2, OFFSET(Data!$A$2, ROW()-2, 0, COUNTA(Data!$A:$A)-ROW()+2, 2), 2, FALSE), ""), "")

Explanation:

  • The outer IF statement checks if the current action is “Clock In.” If it is not, the formula will return an empty string.
  • The VLOOKUP function searches for the next occurrence of the employee ID in the “Data” worksheet, starting from the current row and moving down. The OFFSET function is used to create a dynamic range for the VLOOKUP function.
  • If the VLOOKUP function returns an error (indicating that the employee ID was not found or the next occurrence is not a clock out), the formula will return an empty string.

Copy the formula and apply it to the entire “Time Out” column. This will give you the clock out times for each employee, based on the next occurrence of their ID.

If the cell next to B2 is equal to “Clock In,” we want to perform certain actions. Otherwise, we leave it blank. We can achieve this using an IF statement. Here’s the updated code:

excel

=IF(B2<>"Clock In", "", IF(VLOOKUP($A2, OFFSET(Data!$A$2, ROW()-2, 0, COUNTA(Data!$A:$A)-ROW()+2, 2), 2, FALSE)="Clock Out", VLOOKUP($A2, OFFSET(Data!$A$2, ROW()-2, 0, COUNTA(Data!$A:$A)-ROW()+2, 3), 3, FALSE), ""))

Explanation:

  • The outer IF statement checks if the cell next to B2 is not equal to “Clock In.” If it’s not, the formula returns an empty string.
  • If the cell next to B2 is “Clock In,” the formula proceeds to the inner IF statement.
  • The VLOOKUP function inside the inner IF statement searches for the next occurrence of the employee ID in the “Data” worksheet, starting from the current row and moving down. The OFFSET function is used to create a dynamic range for the VLOOKUP function.
  • If the VLOOKUP function returns “Clock Out,” the formula retrieves the corresponding timeout value using another VLOOKUP function, and that value is displayed.
  • If the VLOOKUP function does not return “Clock Out,” the formula returns an empty string.

Copy the formula and apply it to the entire range where you want to display the timeout values. This will populate the cells with the appropriate timeout values based on the “Clock In” and “Clock Out” actions.

Duration

Now that we have the timings and timeouts, we can calculate the duration of their work. We will perform a subtraction, but only for the cases where both timeout and time in are available. For the cases where there is no timeout, we will leave the calculation blank.

Here’s the updated code:

excel

=IF($D2<>"", $D2-$C2, "")

Explanation:

  • The IF statement checks if the timeout column (column D) is not blank. If it’s not, the formula proceeds with the subtraction.
  • The subtraction is performed by subtracting the time in (column C) from the timeout (column D).
  • If the timeout column is blank, indicating no clock out time, the formula returns an empty string.

Copy the formula and apply it to the entire range where you want to display the duration worked. Format the cells as duration to display the calculated duration properly.

By using this formula, you will be able to calculate the duration worked only for the cases where both the timeout and time in are available. For the other cases, the cells will remain blank.

Hours Worked

If you would like to represent the durations in hours instead of percentages of the day, you can multiply the duration by 24. To accomplish this, you can use the following formula:

excel

=IF($D2<>"", ROUND(($D2-$C2)*24, 2), "")

Explanation:

  • The IF statement checks if the timeout column (column D) is not blank. If it’s not, the formula proceeds with the calculation.
  • The calculation multiplies the duration (timeout minus time in) by 24 to convert it to hours.
  • The ROUND function is used to round the result to two decimal places.
  • If the timeout column is blank, indicating no clock out time, the formula returns an empty string.

Copy the formula and apply it to the entire range where you want to display the hours worked. Format the cells as a number with the desired decimal places.

After applying this formula, you will have the hours worked for each entry. You can further enhance the spreadsheet by adding additional information about the employee, such as their name or other relevant details.

Employees

I will accomplish this task by utilizing the employee table. To extract the employee’s name, we will perform a VLOOKUP operation. Here’s how it will be done:

  1. Begin by entering the formula “=VLOOKUP” to initiate the VLOOKUP function.
  2. Specify the employee ID as the value to search for.
  3. Exclude the final reference comma.
  4. Indicate the range where the employee IDs are located, which is the employee table spanning from column A to column C.
  5. Include a comma to separate the range from the column number.
  6. Specify the column number from which we want to retrieve information, which in this case is column three (the column containing the names).
  7. Add another comma.
  8. Indicate an exact match by including a zero or false.
  9. Press Enter to execute the formula.

To convert this into an array function, double-click on the cell containing the formula and press Control+Shift+Enter.

To avoid displaying #N/A errors, we can check if the employee ID exists before performing the VLOOKUP. Here’s how:

  1. Modify the array formula by incorporating an IF statement.
  2. Check if the employee ID is equal to blank.
  3. If the employee ID is blank, leave the values blank.
  4. Otherwise, perform the VLOOKUP as previously described.
  5. Ensure that all parentheses are appropriately closed.
  6. Press Enter to execute the formula.

To apply the formula to multiple rows, manually drag the formula down. Use the shortcut Command+Shift+Down (or Ctrl+Shift+Down on Windows) to select all the cells, and then use Command+D (or Ctrl+D on Windows) to fill the formula down.

If you wish to add more rows, make sure to drag the formula accordingly. By following these steps, the system will calculate the values automatically as clock-ins are added.

Clock Out

To ensure that the calculations are updated for clock-outs, let’s add an example for a person who is signed in but didn’t clock out. Here’s what you can do:

  1. Locate the person who didn’t clock out, for example, with the employee ID “55555.”
  2. Enter the clock-out time for that person.
  3. The calculations should automatically update with the new clock-out time, providing the total hours worked and other relevant information.

Regarding the “0.59” value, which represents half an hour, it is essential to note that if you have a specific rate that needs to be multiplied by the hours worked, such as $40 per hour or $10 per hour, you should multiply it by the hours worked and not the “0.59” value. Let’s add this functionality:

  1. In the employee table, enter the rate at which they are paid, such as “$40” or “$10.”
  2. To retrieve the rates and incorporate them into the table, perform a lookup similar to the previous VLOOKUP operation.
  3. However, this time, we will only perform the lookup if there is a duration or hours worked. To achieve this, use an IF statement.
  4. Check if the column for hours worked is blank.
  5. If it is blank, leave the rate blank as well.
  6. Otherwise, perform the VLOOKUP operation to retrieve the rate.
  7. Use the VLOOKUP formula, searching for the ID in the employee table, starting from the ID column and going to the rate column.
  8. Specify “0” or “false” for an exact match.
  9. Press Ctrl+Shift+Enter (or Command+Shift+Enter) to convert the formula into an array formula.
  10. The hourly rates should now be populated in the table.

To calculate the total amount based on the hourly rate and hours worked, follow these steps:

  1. Use another IF statement to determine if the hours worked exist.
  2. Check if the column for hours worked is blank.
  3. If it is blank, leave the total blank as well.
  4. Otherwise, multiply the hours worked by the hourly rate.
  5. Use the multiplication sign (*) to perform the calculation.
  6. Press Ctrl+Shift+Enter (or Command+Shift+Enter) to convert the formula into an array formula.
  7. The totals should now be calculated based on the hours worked and hourly rates.

These calculations can be used in payroll or similar applications.

Week Number

To further enhance our system, we may want to determine the week number to which each entry belongs within the year. To achieve this breakdown, we can utilize the “WEEKNUM” function, which allows us to specify a date. Here’s how we can implement it:

  1. Identify a relevant date to use for the week calculation. In this case, let’s use the “timeout date” column.
  2. Apply the “WEEKNUM” function to the timeout date column to obtain the week number. The function will take the format “=WEEKNUM(timeout_date, type)”.
  3. Specify the timeout date as the input for the function.
  4. Select the appropriate type for the week calculation. Choices may include starting on Sunday, Monday, or other options.
  5. Press Enter to execute the formula.

To ensure that the week number is only displayed when there is a timeout date, we can incorporate an IF statement. Here’s how:

  1. Modify the formula by adding an IF statement at the beginning.
  2. Check if the timeout date is blank.
  3. If it is blank, leave the value blank as well by using double quotes (“”).
  4. Otherwise, perform the “WEEKNUM” function to retrieve the week number.
  5. Convert the formula into an array formula by selecting the range of cells where the week numbers will be displayed (e.g., F2:F) and then pressing Ctrl+Shift+Enter (or Command+Shift+Enter).
  6. The week numbers for each entry should now be populated in the respective cells.

By following these steps, we can determine the week number to which each entry belongs within the year.

Pivot Table

Looks good to me. At this point, using this data, we should be able to select all of these columns, go under “Data,” and build a pivot table. Here’s how:

  1. Select all the relevant columns.
  2. Under the “Data” tab, choose “Pivot Table” and select “New Sheet.” Name this sheet “Report.”
  3. In the pivot table, you can add values such as wage, duration worked, or hours worked as a number. Choose whichever one you prefer.
  4. To break down the data by rows, start with the employee name. If there are no duplicate names in the company, using just the employee name is sufficient. However, if there can be multiple people with the same first and last name, it’s advisable to add the employee ID as well. In this case, remove the “Totals” row for employee names.
  5. To remove the empty cells, apply a filter for the “Timeout” column. Filter by condition and select “Is not empty.” This will exclude the blanks from the pivot table.
  6. If desired, you can also add a row to break down the data by the week of the year. Place it above the employee names section.
  7. Additionally, you may want to include the year as a filter. Move the “Timeout” column to the beginning of the pivot table.
  8. Right-click on one of the dates, choose “Create Pivot Date Group,” and select “Year.” Now you can filter the data by year.
  9. To filter by a specific week, drag the “Week of Year” column to the filters section. However, if it doesn’t allow you to add both filters, it might be due to the date grouping. In that case, remove the grouping and try again.
  10. If you encounter difficulties with filtering, you can also consider putting the week and year columns in the filters area instead of listing everything.
  11. To demonstrate, add the “Timeout Year” column to the filters section.
  12. If needed, create a new column called “Year” in your data and use the YEAR function to extract the year from the timeout column. Apply an array formula and include an IF statement to leave it blank if the timeout is blank.
  13. Now you should be able to use the year in your pivot table. Update the “M” column in the report to include the new “Year” column.
  14. Finally, you can filter the pivot table by year and week to view specific totals.

By following these steps, you should be able to generate reports with totals based on various criteria and filter them as needed using the pivot table.

Verification Code

When reviewing my data, I need to compare the entered verification codes with the correct verification codes. If they don’t match, I want to indicate the mismatch with a warning sign, such as marking it in red. To accomplish this, I will work with the processed data rather than the original data. Here’s what I’ll do:

  1. To check if the verification code matches the corresponding employee’s verification code, I’ll use a logical test.
  2. I’ll perform a VLOOKUP function to retrieve the verification code from the employees table. The VLOOKUP formula will search for the employee ID in the table and return the corresponding verification code.
  3. After obtaining the retrieved verification code, I’ll compare it with the verification code entered in the form.
  4. To reverse the truth value (i.e., switch true to false and false to true) and identify the cases where the codes don’t match, I’ll use the NOT function.
  5. Since the formula contains a reference to another worksheet, I’ll modify it to work with conditional formatting. I’ll convert the reference to a text format and wrap it with the INDIRECT function to reconvert it back to a range.
  6. I’ll copy the modified formula and apply it to the range of verification codes using conditional formatting. This will highlight any mismatches with a dark red background and white text.
  7. After verifying the conditional formatting works correctly, I can delete the true/false values since they are no longer needed.
  8. To test the functionality, I’ll add another clock-in entry with an incorrect verification code. If everything is set up correctly, the corresponding verification code should be marked in red in the processed data, indicating the mismatch.

By following these steps, you should have a starting point for identifying and flagging verification code mismatches in your data.

Clock In & Clock Out Spreadsheet Template: https://docs.google.com/spreadsheets/d/1fbdAMpJwIJpC7EnkFvTwSQ0Sk-RfqGw8U934zypfqCw/edit?pli=1#gid=1960937856

In this video we’ll build a clock in and clock out system using Google Sheets & Google Forms. This basic system should work for a small business.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top