Clock In, Clock Out System w/ Google Sheets & Google Forms, Spreadsheet Template for Small Business 

Wow! Just wow! I thought I was proficient with spreadsheets, but you are in a different class entirely. I've got lots of studying to do. Thank you for your work.

Hi, thank you for this tutorial! I have learned a lot with all your videos! For those who are interested in another way (without using vlookup) to get the information you need based on the conditions you set up, you may use this formula: Index( data range , match ( 1 , ( condition 1) * (condition 2) * (condition 3) * ….. , 0 ) , 1) For example: Index ( A2: C , Match ( 1 , ( B2:B = “ 1234”) * (C2:C = “Clock Out”) , 0),1) Where condition 1 = the employee code and Condition 2 = the time when the employee clock out Hope this helps for someone

I stumbled onto this when I was looking for something else, and I'm so glad I did. So many great tips and tricks in one place. I greatly appreciate your clever work! Thank you!

Teacher...you always think of everything!!! Whew!

awesome. I am always stunned about your solutions AND way to solve such "problems". Even at 11pm!!! (record time) I would have ended up with multiple "helper" columns. thx for sharing

I really don't know how to use such long formula. You really explained it well. Thanks!

Amazing. Thank you so much. You've helped me save so much time and mitigate the exposure to errors.

I was working on something like this, to use with students logging in/out for bathroom breaks. But then I thought: someone must have done this before. Awesome job! Your VLookup trick for matching clock in times with clock out times is pretty cool.

Very nice tutorial, I'm actually using it for some of my employees, Just one more thing that I need to do is get another tab in the same sheet with individual clock in/out because sometimes they don't remember if they have clock In/Out and they would like to check, only clock in/out, But other than that it works perfect to me.

This is very useful. Thank you for the clear step by step tutorial!

You are Amazing! You even provided us with a template, thank you!

Just amazing, I was looking for this information only, and got your video. Thank you so much! Appreciate your efforts and hard work. 👌😊👏

Thank you very much for this lesson.

Great tutorial, thanks for good insights. Used a lot of same functionality shown here in my own custom time reporting form.

Thank you for this tutorial. It helps me a lot as it has all the information I needed.

i was wondering upon this topic and u cracked it for my small business

Amazing. Thank you. What if I wanted to include options for lunch breaks and Location-for field officers. How do I include this? Also can the hourly rate be adjusted to flat monthly rate without the editing formula?

This was so very useful thank you for the time you took to create this.

Outstanding as always :) Lately I've been putting my ArrayFormulas along with my header in the top row. That keeps the formula in one cell per column. Like ... ={"Year"; ArrayFormula(IF(F2:F="","", YEAR(F2:F)))}

My friend you are truly amazing! Thank you so much for this!

Thanks. It's amazing! However, is there any way to restrict the employee to submit multiple times such as some employees will submit for their colleagues? If the Employee ID and Verification Code isn't the same, can it trigger a notification to the employee? And after they submitted, can they get to know this response is recorded as check in or check out?

Thank you for this tutorial!

Good Explanation Learned a lot. thanks

Thanks for the template! 😁

great video! Hey, one question, there is a way that the formula of column "Time Out" can be set it automatically to the whole column, I mean, apply the formula once a new row is created? Because I see that "ArrayFormula" could not be applied because the formula get the cell reference of above current row, so there is a way to automatically set it to the whole column to not be dragging down manually the formula?

Thanks your tutorial in this video Clock In Out lession. Can you make a video tutorial on calculating the total employee leave, without including Saturdays and Sundays of the week, month and year? Thank you so much. Your tutorials is the best.

Thank you for sharing this. it is very helpful to my small business

more grateful for this video than you know

Great tutorial. I have a clock on/off system working, however I need a foolproof, in the case an employee forgets to clock on or off. Which seems to happen quite a bit in our organisation. So finding the next time stamp for the next clock off may be a different day. Any ideas?

Thanks a lot for the instructions. I just have one question: Is there any other way to use array formula for the Time-out Column since my data is very large? :((((

Thank you so much.. It helps me alot.. Thanks.

Hi! Can you make a video on how to manage a live FIFO inventory automated? I want to register using Google Forms what comes in and what comes out but I want the unit price of the first badge I bought instead of always taking the most recent or average unit price, in this case, I want to grab the unit price of the first badge in I bought and so on, then grabbing the next most recent unit price once the previous stock is done. I want to run my warehouse with real unit prices but I'm not able to find the logic to do so. I think a lot of people can identify themselves with this problem so it would mean a lot if you can help us!! Thanks for this channel, I've learned SO MUCH!

Thank you for this video! I have a question and hope you/someone can help. We are using this same model in an Afterschool setting where students will sign-in and out for each activity they attend on that day. Some choose to stay in one activity the whole time while others may choose 2, 3, or 4 different activities to go to that day. Our problem is the students do not always sign themselves out from an activity. Is there a way that if there is another sign-in to an activity it will then automatically put a sign-out time for the previous activity?

Excellent! Thank you!

Hi! Thank you for this tutorial! I am new with Google sheets so I just need a little help in adding Lunch in and Lunch out. I tried using the same formula with a little tweak but it does not capture the timestamp in the same row where the clock in is. Help.

Love this tutorial! I'm new to Google Sheet formulas...Question: maybe I missed something, but is it possible to remove the Clock Out row that is not calculated so the only info is Clock In/Out on the same row? I see the Clock In row with both calculations, but the Clock Out row is blank. Hope this makes sense.

Many thanks, good presentation

thank you for your template.

That was great thank you.

Great tutorial. One question for the 'Time Out' column, is this the only way to drag down the formula? It would work by inserting the 'arrayformula' or other command in front to duplicate the codes to all rows in the 'Time Out' column?

Awesome Great Job !! Finally a good teacher ..👌

Thank you for the video it is excellent. I am having a problem with the vlookup formula on the time out. it is saying Error I would appreciate it if you have any idea of why is that happening. I am doing it exactly as you in the video. The only difference is that I do not have ID for the employees, so the Vlook up is based on the names. Thank you

Great video! Thank you so much for that one. I'm stuck at some point, because, for some reason, the syntax of the formulas usse ; instead of a regular , - and this mess up with, for example, the VLOOKUP searching by 2 different column ranges (right when you try to get the clock out times, for example). I can't make this work. Could some one shed some light? Thanks!

Hello! Is it possible that google form validate the verification code. In other words, if the employee enter wrong code then it will not allow you to time in or out. Love your videos. More power!

Thank you very much.

How would you add a subtraction for lunch? For example our employees clock in at the beginning of the day and then clock out at the end of the day. If they don’t write on their time card that they didn’t take a lunch , 30 minutes is automatically subtracted. I actually created a spreadsheet based off your videos (SUPER HELPFUL) but the only thing I am missing is the lunch aspect. Look forward to hearing from you how to do this!

very useful and not very difficult, thanks

Thank you for this! I was wondering how I check and display if someone who clocked out didn't actually clock in? What would the formula be like?

This is very helpful, I’m just curios is it possible to include breaks and lunch in this clock in / clock out system? I hope to hear from you soon Thank you very much!

Teacher you are the best

Great ...thanks for your video

Seems like you could build a library/equipment inventory system using with forms for check in/out using concepts from this video. Fantastic as always.

Hi! This is an awesome tutorial! This has worked great for me so far until recently my "Time Out" column stopped auto populating. Any advice?? I dragged formulas down and made sure they continue but no luck.

Thankyou you did my all problem is solved.

Thanks for your great video. Can you make a video that staff can check-in and check - out by QR code?

Whoever you are thank you my dear one

Nice video , as always. Can you make a video on how to integrate appscript with google cloud project, and than use it as an app without exposing actual code.

Hello! I've been using my time clock for months now and it's been absolutely great! But I have a question. It's no longer transposing the clock out times, and I remember there was a formula that needed to be dragged down however many cells. Well I think I've come to the end of those cells. Can someone tell me where to go in and fix it? (Trying to avoid having to watch the whole 40 minutes again)

Cool... I am trying to do a Visitor Management Google Form that just needed to see compile the time in and time out of contractors. Maybe on the same line. I thought the initial part of this project would help but I have been hitting dead end. can assist?

this is so clever! love!

Interesting. Nice project!!

I'm very new to sheets and loving this channel, I haven't been able to find any other channel with such simple and easy to understand explanations. My first ever question on youtube goes to you! Can the ID and verification code on the forms be changed to FIRST NAME & LAST NAME? So instead of an employee using a numerical code they instead use their name to sign in/out. If so what would be the method to use? Regards, Martin.

Thank you for your content - I learned a lot :-)

How would you create/set up a scan QR code for log in and log out? Also, is there a way to have it record geographic location? Finally a method that would stop somebody from logging in or out a co-worker??

Looking to take a bus roster in google sheets so that it can be updated daily and then have the bus monitors scan the students barcode ID's to check them in as they get on the but and then again when they get off, if little Johnny is checked in but not out the bus driver can do a search of the whole bus to make sure he is sleeping in the back or whatever the case is....The google sheets updated roster would be good if a student accidently tried to get on the wrong bus. Is this even possible, I have been watching a bunch of videos but haven't found one that can do everything I want it to... Any Advise>??

Hi. I was wondering if you could show us how to add, delete, search, update buttons in google form to perform changes in each response that is a standard format instead of using CRUD.

Wonderfully teach

Nice work bro. Thanks for the help.

can i use it with tasker or ifttt to add rows to the data tab?

Hello this is a great video. How would you calculate overtime? Please and thank you!

Great video. Thanks. It was helpful but my concern is when done following the employee id restriction and i'm on the viewerform the employee id show enter valid id which I put the patterns /d(8) and when it's type to try I enter sample id 20220102 or 12345678 can't go thru? Can you help me resolve this issue?

Is there a tutorial on parsing HTML data to sheets? I have a task where I have the link for a youtube channel about page and I need to get a description, links from that page to the sheet. if possible please help.

Thank you for fabulous explanations! I only have one person whose time I need to track and calculate. I have spent over an hour trying to figure out how to make what you show beginning at 14:03 work for one employee with no id in column c. I even added an ID column and entered the code just like you show and all I got was the total time on the first check in/out. No other check out times appeared. :( .... update: realized I had not dragged the formula down as you demonstrate. Once I this, the other clock out times appeared.) 1) Please, what is a code to have the TIME IN and TIME OUT appear on the same line so it is easy to calculate the TOTAL HOURS? or... 2) What is the code to get the total hours worked if the TIME IN is on line E2 and the TIME OUT is on F3? Thank you!

What if you have to edit time if an employee forgot to clock out/in? Or any adjustment need to be made

Thanx my teacher

So I’m trying to make it change only when there’s new info checked and color it differently time stamp but everything I have tried to do changes the date even when there’s no new total check boxes

Thanks for this great tutorial. One question, I would like to have a column that shows if a person forgot to clock out on a specific day. How is this possible?

You are a genius! New subscriber here!

Hi there, This is brilliant 👏 I have a question if you don't mind. I have set up a form that will log: Timestamp. Job number. In/out. Candidate name. Candidate name. Candidate name. Candidate name. Candidate name. Candidate name. I dont want the supervisor to log multiple entries for each candidate (worker) as it would take him to long. Can you suggest how I can format my data so it appears line by line. So the data would look like this: Timestamp. Job number. In/out. Candidate name. Timestamp. Job number. In/out. Candidate name. Timestamp. Job number. In/out. Candidate name. Etc. The Timestamp, job number and in or out would be the same for each line based on the original form entry. Hope that makes sense and thanks for all your videos!

Can we get Time in in time format continuously for future entries>. In this method we have to change the format after every new form entry

I made an intern clock-in and clock-out work perfectly! Thank you! I have a question, though. Let's say my interns clocked-out twice by accident, or they clocked-out, but realized they weren't done with work and then when they were done, clocked-out again. The way the formula is written right now, the first clock-out (after the clock-in) is registered and the second one is ignored. I'd like the formula to recognize the last clock-out as opposed to the first clock-out. Is this possible to do? How?

For the timeout dragged down formula, is it possible to make it automatic?

As usual, your video was very good. I would have very much liked to see the verification of the employee number and verification code take place in the New Google Forms form before letting the user clock in or clock out. This would have set this video apart from all of the others. I am sure that Appscript would be needed in the form. Will you show how to access the spreadsheet from the form in a case like this?

Is there a way to have the processed data be presented as a date starting on a Monday instead of a week number? Ex Week 10 presented as 3/4/2024? Thanks in advance for the help

Do you have a video on how to add a geo location for employees location during clock in or out

Is there a way to open or close an employee"s spreadsheet when this one clock in or clock out?

you are amazing! tysm

how should my vlookup formula look like if my employees have other activities in a day like coaching or training? how can i compute their hours for the day? thanks!

Hello, I make an attendance sheet looking at your video. but I have a problem. if someone clocks out after 00.00am that not added to clock out time. can you help me

I want to do check in and out by scanning a QR code and record the information in a spreadsheet so that I can keep a track on my inventory. Can you make a video on that??

hi, i just want to ask for help, is there a way to prevent data from processing if the employee is no longer associated.. like if it has resigned .. how can the sheet prompt and prevent clock in and clock out to be recorded and a remark will appear that this employee is no longer associated

Hi, this is so great esp for my startup small business. I only have a problem with the week of the year. The Philippine set-up is we get paid up according to specific cut-off dates, i.e. salary date of September 30 has a cut-off days between September 11- September 25 and Oct 15 has a cut-off dates between Sept 26 - Oct10. Can you help me with this? Thank you very much, hope you can help me with this.

Sir, I want to say, For last few days, I got stuck creating a VBA Code where I've to compile all the Data from Different Files. For your information, all the files are in the same folder. But, there are some catches, - 001) Each File has some Common Headers and some Uncommon Headers. I've to compile the Data Header wise. 002) Each File has more than 80,000 Data. And there are 28 to 31 files in that Folder depending on the number of days in that specific month. Now, during the compilation, if the row of the Master File exceeds 1 Million, then save it and close it. Create a new Excel Workbook, where compilation begins again. But, this time compilation should start next after where it ended in Last master file.

Idol,thank you for a wondeful content..Also, that would be flawless if there is a way to check the verification code from the Google Forms in the first place.. is there.. a way?? I remember you had this video about google forms executing validation from sheets to forms.. cant remember..

So I am using this for a Police Department Roleplay group. So every 2 weeks there are promotions. How would I make it I can see each period separately?

Thanks for this Learn Google Spreadsheets. I just wanna ask how can I reuse this and clear all the data so that I use it to our employees? When I try to delete all the list from the Data sheet. it works but It also gives me a gap. Blank columns appears on the Data Processed sheet when I clock in a new employee. how to avoid those gaps? I also tried to Copy the form and the sheet but I am having a hard time with the connection. Thanks in advanced. :)

Hey I need to apply this to a department im running but instead of using a clock in/out system, On the google form I get them to submit what time they started their shift, what time they ended it and then how long they where on duty for. I tried to apply the information in the video to my situation but sadly it did not work, So if Anyone can help that would be great

Hey! So if I were to do a roster to count up every bodys hours, how would I? So, when somebody clocks out, all the hours go to one specific cell for that person? So every time they clock out and so and so, all the hours will round up on that specific row that has the name of the person?

Hi sir your so brilliant 👏😀 can I ask you a favor sir can help me how to improve may grading sheet using Google sheet..

How to give access for out of Organization emails. I have created in Organization mail but i have shared link to my employee(He had GMAIL only) that time link asked access from Admin. So please clarify how to give access for out of my Organization mails?. So please clarify.