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
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)))}
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!
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.
I used this for a bathroom pass sheet as well. I am struggling with the column that is not an array as I have to drag it down every time someone enters a new Google Form submission. Did you find anything for that?
I'm a small engine shop teacher. I'm setting it up for students to clock in their time for projects so we can track and evaluate their efficiency similar to flat rate work in an actual job setting.
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
So, I have on working with a Form and a Spreadsheet with 2 tabs. The form asks for two pieces of info: Item number and Checking In or Checking Out. The Sheet has a "Form Responses" Tab and an "Inventory" tab. I'm using ArrayFormula and Lookup on the Inventory tab to check the last instance that an Item was checked in or checked. Each item on the Inventory Tab gets its own Formula. Will this strategy run into issues like to many Lookup formulas or slowing down as the number of items increase? Here is the formula: =ArrayFormula(LOOKUP(2,1/('Form Responses 1'!$B$2:$B=A2),'Form Responses 1'!$C$2:$C))
@@mystreteacher With google sheets "slow" is common once you pass a certain number of rows. So it really depends on how large these tables need to grow.
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??
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?
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.
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?
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?
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!
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?
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? :((((
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.
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!
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?
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??
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!
1 and 2 answer. If u do it as shown in the video the TIME IN appears in E line and TIME OUT appears in F line and total worked time is calculated in line G lline. Total time worked per week or month u can see in REPORT
Hello, I learned a lot to your video. Furthermore, would you please create a tutorial about Check-out and Check-in? Specially, multiple entries of an item, meaning in a day, an item will be out and in for several times. Thank you and more power!
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.
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..
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?
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!
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!
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.
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.
Couldn't you just add the *24 to the duration worked column cells so that it calculates the actual time worked all in that one column? Instead of having to have 2 separate columns for that? I'm still learning how to do this sort of thing and wondering if there's a reason that you did this in 2 separate columns instead of adding onto that formula a wee bit more?
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.
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.
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!
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?
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!
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
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
What if EMP ID: 12345 forgot to clock out, and then clock in again making his time ins duplicate before actually clocking out? I am currently stuck on this scenario :(
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.
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.
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?
Until Google decides to provide such API, it's currently impossible to make Google Forms to not submit. The only solution for this is to use a Web App instead of Google Forms.
hey bro, can we do this with live location of the employee, where in and out comes with live location address in google sheet, if it is possible then please suggest.
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.
@@ExcelGoogleSheets Yea that is true. Right now I am trying to add two columns indicating Regular hours and OT. then have two hourly rates (regular and OT) calculate into wages.
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?
For anyone wondering how to turn the formula into an array you can use thins formula =ArrayFormula(LET(a, A2:A, b, B2:B, c, C2:C, s, SORT(IF(b"Clock Out",{"",""}, {a, c}), 1, 1), IF(b"Clock In",, MAP(a, c, LAMBDA(x, y, XLOOKUP(1, (INDEX(s,,1)>x)*(INDEX(s,,2)=y), INDEX(s,,1),, 0))))))
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
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!
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?
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
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)))}
Nice. I make one that very similar with this in my chann*l. See if you curious with keyword "oneliner input and output".
Hi, do you have a template sheet. Many thanks
Teacher...you always think of everything!!! Whew!
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!
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.
I used this for a bathroom pass sheet as well. I am struggling with the column that is not an array as I have to drag it down every time someone enters a new Google Form submission. Did you find anything for that?
I'm a small engine shop teacher. I'm setting it up for students to clock in their time for projects so we can track and evaluate their efficiency similar to flat rate work in an actual job setting.
I really don't know how to use such long formula. You really explained it well. Thanks!
Thank you for this tutorial. It helps me a lot as it has all the information I needed.
Thank you for sharing this. it is very helpful to my small business
Glad it was helpful!
You are Amazing!
You even provided us with a template, thank you!
Only the template of the spreadsheet but not with the form and the codes
Just amazing, I was looking for this information only, and got your video. Thank you so much! Appreciate your efforts and hard work. 👌😊👏
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
Whoever you are thank you my dear one
Amazing. Thank you so much. You've helped me save so much time and mitigate the exposure to errors.
more grateful for this video than you know
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.
So, I have on working with a Form and a Spreadsheet with 2 tabs. The form asks for two pieces of info: Item number and Checking In or Checking Out. The Sheet has a "Form Responses" Tab and an "Inventory" tab. I'm using ArrayFormula and Lookup on the Inventory tab to check the last instance that an Item was checked in or checked. Each item on the Inventory Tab gets its own Formula. Will this strategy run into issues like to many Lookup formulas or slowing down as the number of items increase? Here is the formula: =ArrayFormula(LOOKUP(2,1/('Form Responses 1'!$B$2:$B=A2),'Form Responses 1'!$C$2:$C))
@@mystreteacher With google sheets "slow" is common once you pass a certain number of rows. So it really depends on how large these tables need to grow.
Great tutorial, thanks for good insights. Used a lot of same functionality shown here in my own custom time reporting form.
Excellent!
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??
that would be a good thing
Awesome Great Job !! Finally a good teacher ..👌
👍
Thankyou you did my all problem is solved.
Great!
This was so very useful thank you for the time you took to create this.
i was wondering upon this topic and u cracked it for my small business
Teacher you are the best
My friend you are truly amazing! Thank you so much for this!
Many thanks, good presentation
Thank you very much for this lesson.
This is very useful. Thank you for the clear step by step tutorial!
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?
very useful and not very difficult, thanks
Thank you for this tutorial!
thank you for your template.
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.
Good Explanation
Learned a lot. 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?
Wonderfully teach
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?
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?
You are a genius! New subscriber here!
Thank You welcome aboard!
this is so clever! love!
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!
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?
1
Thank you so much.. It helps me alot.. Thanks.
Great work
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? :((((
That was great thank you.
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 have to choice, but to subscribe.
Great ...thanks for your video
Thanks for the template! 😁
Nice work bro. Thanks for the help.
👍
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!
Thanx my teacher
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?
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??
Excellent! Thank you!
Thank you very much.
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!
1 and 2 answer. If u do it as shown in the video the TIME IN appears in E line and TIME OUT appears in F line and total worked time is calculated in line G lline. Total time worked per week or month u can see in REPORT
Hello, I learned a lot to your video. Furthermore, would you please create a tutorial about Check-out and Check-in? Specially, multiple entries of an item, meaning in a day, an item will be out and in for several times. Thank you and more power!
Why would it be different?
@@ExcelGoogleSheets as a teacher, I clock in and out multiple times a day and for different reasons like subjects/projects/admin work
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.
Interesting. Nice project!!
Hi sir your so brilliant 👏😀 can I ask you a favor sir can help me how to improve may grading sheet using Google sheet..
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..
Would love this answered as well.
you are amazing! tysm
Thank you!
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?
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!
Hi Learn Google Sheets & Excel Spreadsheets!
I hope you can toturial that has breaks and lunch schedule :)
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!
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.
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.
Thank you for your content - I learned a lot :-)
Couldn't you just add the *24 to the duration worked column cells so that it calculates the actual time worked all in that one column? Instead of having to have 2 separate columns for that? I'm still learning how to do this sort of thing and wondering if there's a reason that you did this in 2 separate columns instead of adding onto that formula a wee bit more?
could it be work with qr code?
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.
Thanks for your great video. Can you make a video that staff can check-in and check - out by QR code?
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.
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!
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?
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!
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
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
Hello this is a great video. How would you calculate overtime? Please and thank you!
What if EMP ID: 12345 forgot to clock out, and then clock in again making his time ins duplicate before actually clocking out? I am currently stuck on this scenario :(
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.
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.
Assuming you don't have repeating first names then yes. I don't think you need to change anything.
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?
Until Google decides to provide such API, it's currently impossible to make Google Forms to not submit. The only solution for this is to use a Web App instead of Google Forms.
@@ExcelGoogleSheets Thanks for the quick response. I thought that the new forms had the API's available to them.
hey bro, can we do this with live location of the employee, where in and out comes with live location address in google sheet, if it is possible then please suggest.
For the timeout dragged down formula, is it possible to make it automatic?
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.
great
Hello. When I created the project, I used test entries. Now that I want to release it, how do I clear to entries? Thanks!
Just delete them from form responses sheet.
@@ExcelGoogleSheets Thank you! Kudos to be still replying to comments!
Hi may I know if we could include overtime
new subscriber here, how can we expand the data form so we can calculate for Over time?
Depends on many variables, there is no one solution.
@@ExcelGoogleSheets Yea that is true. Right now I am trying to add two columns indicating Regular hours and OT. then have two hourly rates (regular and OT) calculate into wages.
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?
Hi, Thanks for your great video.
The timestamp for this attendance sheet is just showing the same date. Could you please help me to get it solved
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
For anyone wondering how to turn the formula into an array you can use thins formula =ArrayFormula(LET(a, A2:A, b, B2:B, c, C2:C, s, SORT(IF(b"Clock Out",{"",""}, {a, c}), 1, 1), IF(b"Clock In",, MAP(a, c, LAMBDA(x, y, XLOOKUP(1, (INDEX(s,,1)>x)*(INDEX(s,,2)=y), INDEX(s,,1),, 0))))))
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
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!
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?
Did you find an answer?