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!

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

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

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

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.

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

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

Thank you very much for this lesson.

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

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.

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)))}

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.

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

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?

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

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

Thanks for the template! 😁

Good Explanation Learned a lot. thanks

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?

Thank you for this tutorial!

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!

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!

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.

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 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

more grateful for this video than you know

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

That was great thank you.

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.

Many thanks, good presentation

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.

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

thank you for your template.

Thank you very much.

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)

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 sharing this. it is very helpful to my small business

this is so clever! love!

Teacher you are the best

Interesting. Nice project!!

Whoever you are thank you my dear one

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!

Great ...thanks for your video

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.

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?

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?

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?

very useful and not very difficult, thanks

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.

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!

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

Wonderfully teach

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?

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.

Thanx my teacher

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

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

You are a genius! New subscriber here!

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.

Nice work bro. Thanks for the help.

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>??

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??

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

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?

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?

Thankyou you did my all problem is solved.

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?

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.

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.

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

you are amazing! tysm

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!

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!

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

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

Been watching your videos and applying stuff I learned for half the day, so thanks for that. Unfortunately, the one thing I've been searching the hardest for I can't find. Is there any way to input a formula somewhere as a "master" copy, such that I can use it in multiple places by referencing to it, but if I change the original, all the copies will also be changed? I've searched YT and Googled everything I can think but I can't find anything close to that, other than mentions of addons to Excel that can do such things.

Everytime i add drop down list in google sheet of Data, will it automatically populate new listed ??? What if the list change often, I tried adding list but it is not found on the form. Do i have to re-compile the same script after adding new list? Or should i create deployment first?? Sorry im newbie

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

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..

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

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

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

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.

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.

Hi, is there a way to send text messages /send messages on WhatsApp via contact numbers in Google sheets. Sort of bulk messaging.

is it possible to import data from an attendance machine connected to the same network using its IP?

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

Teacher thanks for your videos. I need a video how to send what's app messages with attachments via Google sheet.

Is it possible to for google sheet to do an entry in a specific and is automatedly entered to a different sheet, than clears it self for next entry? I am trying to build a sheet that would record information for several people that I can send out and they could enter the information and I get it in a centralized sheet. Please help thank you.

How can I incorporate lunches to this ? Please if anyone can help, that would be great!

What formula would you use if you instead wanted to keep track of your own hours by client. I created the google form by client for checking in and out and I followed your video to create and adjust the spreadsheet similar to yours. Now I want to add the durations for each client by date by summing all the durations in each day by a FORMULA into another tab which has a row for each day of the year. Would you be able to help me figure out that FORMULA?

by the way, anyone of you knows how to shorten my formula input from data processed 9: 19 to data coz mine is so long I cannot highlight from exactly the start of the cell