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.
@DanKulibert
3 жыл бұрын
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)))}
@ConsulthinkProgrammer
3 жыл бұрын
Nice. I make one that very similar with this in my chann*l. See if you curious with keyword "oneliner input and output".
@22khoa
Жыл бұрын
Hi, do you have a template sheet. Many thanks
@charlespouliot7322
9 ай бұрын
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!
@InsideMyWall
3 жыл бұрын
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??
@onyx7116
4 ай бұрын
that would be a good thing
@Mr.G_ASD
Жыл бұрын
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.
@brookehollingshead9071
6 ай бұрын
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?
@mattdoughty5695
Ай бұрын
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.
@iladremo96
3 жыл бұрын
I really don't know how to use such long formula. You really explained it well. Thanks!
@aadityagoenka4234
2 жыл бұрын
Whoever you are thank you my dear one
@10minutesDIY
3 жыл бұрын
Thank you for sharing this. it is very helpful to my small business
@ExcelGoogleSheets
3 жыл бұрын
Glad it was helpful!
@blockwest2379
3 жыл бұрын
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
@dannychavarria8507
3 жыл бұрын
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
@RashmiGore-j9x
Жыл бұрын
Just amazing, I was looking for this information only, and got your video. Thank you so much! Appreciate your efforts and hard work. 👌😊👏
@MaksimLuzik-mxmz
2 жыл бұрын
Great tutorial, thanks for good insights. Used a lot of same functionality shown here in my own custom time reporting form.
@ExcelGoogleSheets
2 жыл бұрын
Excellent!
@richardsmith3614
3 жыл бұрын
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??
@FernandoHernandez-yt4ff
Жыл бұрын
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?
@MsClever26
3 жыл бұрын
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?
@gulshansharma4469
2 жыл бұрын
Thankyou you did my all problem is solved.
@ExcelGoogleSheets
2 жыл бұрын
Great!
@quickclean-ninja
Жыл бұрын
Awesome Great Job !! Finally a good teacher ..👌
@ExcelGoogleSheets
Жыл бұрын
👍
@junialley1232
2 жыл бұрын
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? :((((
@CableGuy9207
2 жыл бұрын
Many thanks, good presentation
@jax.8.10
3 жыл бұрын
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?
@FloriDiaz-c3g
Жыл бұрын
This was so very useful thank you for the time you took to create this.
@krakenfx3332
2 жыл бұрын
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 :(
@mayankmodi1219
3 жыл бұрын
i was wondering upon this topic and u cracked it for my small business
@AmirrezaAhmadi
3 жыл бұрын
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?
@vickieouellette3585
3 жыл бұрын
1
@waiyantun5949
Жыл бұрын
Thank you for this tutorial!
@jonasvertusjay-v7474
Ай бұрын
I have to choice, but to subscribe.
@user-jxnc
Жыл бұрын
very useful and not very difficult, thanks
@nguyenphutho9503
3 жыл бұрын
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.
@Chaelantonio2
3 жыл бұрын
Teacher you are the best
@fazalrehmanahmadzai6376
Ай бұрын
Great work
@bqrinno
3 жыл бұрын
Good Explanation Learned a lot. thanks
@meoraziz4591
2 жыл бұрын
could it be work with qr code?
@timelesstimesgoneby
Жыл бұрын
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
@EricScorpion65
7 ай бұрын
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?
@tinawoman
3 ай бұрын
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?
@danielgm98
3 жыл бұрын
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!
@cricket2046
2 жыл бұрын
Hello. When I created the project, I used test entries. Now that I want to release it, how do I clear to entries? Thanks!
@ExcelGoogleSheets
2 жыл бұрын
Just delete them from form responses sheet.
@cricket2046
2 жыл бұрын
@@ExcelGoogleSheets Thank you! Kudos to be still replying to comments!
@ezhacks9430
4 ай бұрын
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
@murtaza4646
2 жыл бұрын
Wonderfully teach
@heraldreforma525
3 жыл бұрын
Hi sir your so brilliant 👏😀 can I ask you a favor sir can help me how to improve may grading sheet using Google sheet..
@stephen.morales17
Жыл бұрын
Hi Learn Google Sheets & Excel Spreadsheets! I hope you can toturial that has breaks and lunch schedule :)
@RajeevSah-w5r
Ай бұрын
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.
@sewlikeapro1
2 жыл бұрын
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!
@sarunasbutkus6441
Жыл бұрын
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
@dntsub
5 ай бұрын
For the timeout dragged down formula, is it possible to make it automatic?
@GraceLudovice
Жыл бұрын
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
@designographycanada6853
2 жыл бұрын
Nice work bro. Thanks for the help.
@ExcelGoogleSheets
2 жыл бұрын
👍
@michaelaustinealvarez7578
3 жыл бұрын
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..
@justbelit
3 жыл бұрын
Would love this answered as well.
@mariannehislop5158
Жыл бұрын
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?
@chuie18
Жыл бұрын
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!
@marcoandrade2265
Жыл бұрын
How can I make sure my employees will not copy the link and clock in elsewhere? Is there a way to make it private to an specific Google account so it can only by answered from there? Thank you!
@marinellbates199
2 жыл бұрын
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.
@RltyproHr
Жыл бұрын
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.
@jppiproduction9786
2 жыл бұрын
How can I incorporate lunches to this ? Please if anyone can help, that would be great!
@legalrctg
Жыл бұрын
this is so clever! love!
@malachydonaghy
3 жыл бұрын
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!
@mwKanj
3 жыл бұрын
Thank you very much.
@maninthemiddleground2316
3 жыл бұрын
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!
@malloryunger5649
Жыл бұрын
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!
@josephjohnrobles8074
2 жыл бұрын
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!
@ExcelGoogleSheets
2 жыл бұрын
Why would it be different?
@whatamistilldoinghere
Жыл бұрын
@@ExcelGoogleSheets as a teacher, I clock in and out multiple times a day and for different reasons like subjects/projects/admin work
@GamalielLeddaIII
3 ай бұрын
Hi may I know if we could include overtime
@JudithFisher-qu2wz
5 ай бұрын
how would you do this using the employees name instead of a numeric ID
@raffaelazevedo1512
2 жыл бұрын
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!
@elvisbaloyi1176
Ай бұрын
I tried time in as per formula but it returns blank on the column
@digigoliath
3 жыл бұрын
Interesting. Nice project!!
@dgreatezjoeg
2 жыл бұрын
new subscriber here, how can we expand the data form so we can calculate for Over time?
@ExcelGoogleSheets
2 жыл бұрын
Depends on many variables, there is no one solution.
@dgreatezjoeg
2 жыл бұрын
@@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.
@sheenamarie92
Жыл бұрын
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?
@yoeran3732
6 ай бұрын
can somebody help me out, i want 2 names that clock in on the same time en time out. because i have more employees that work on the same time
@RoboRangers4325
2 жыл бұрын
Is there a way to add a "Success" message? I use this for a student club, they often clock in twice because they think it didn't work the first time. then they see the error and whine that they didn't get clocked in when they actually are clocked in.
@MiguelJimenez-wr5qj
7 ай бұрын
I cant seem to understand the need for a Verification Code. Can anyone explain why use this?
@conradrojas2223
Жыл бұрын
Do you have a video on how to add a geo location for employees location during clock in or out
@PKVenturesSelfCare
Жыл бұрын
Hello this is a great video. How would you calculate overtime? Please and thank you!
@meorazhadmeorzulkarnain804
2 жыл бұрын
hi why cant i use the array formula it keep giving me "Array result was not expanded because it would overwrite data in F3."
@MadsHasserisOlesen
3 жыл бұрын
this says REF!. =vlookup(C2;{C3:C,B3:B};2;0) Danish version use ; in stead of , how do i fix that?
@ExcelGoogleSheets
3 жыл бұрын
=vlookup(C2;{C3:C\B3:B};2;0)
@ronniecacayuan3142
2 жыл бұрын
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. :)
@zahidakram8096
2 жыл бұрын
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
@hiennguyenmusic
3 жыл бұрын
Thanks for your great video. Can you make a video that staff can check-in and check - out by QR code?
@ex-oc8acc1ltdumaspaf67
3 жыл бұрын
Sir, how about if clock out will be the first to be inputted then clock in? We it but generate an error of computing the works hours ,how to solve this sir.
@Aarya2020
2 жыл бұрын
How creat qr code scanner in web app with script Google form
@nitinsingh.2107
3 жыл бұрын
What is the use of a verification code if it does not validates or restricts the correct user. If a restriction is added, it can become awesome. But surely, a good video. Thanks
@playermartian
2 жыл бұрын
you are amazing! tysm
@ExcelGoogleSheets
2 жыл бұрын
Thank you!
@lahirurajapaksha3389
3 жыл бұрын
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
@wernerzurowetz6198
3 жыл бұрын
Thank you for your content - I learned a lot :-)
@kartickchakraborty9135
3 жыл бұрын
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.
@RaviSingh-yy4kt
3 жыл бұрын
What if you have to edit time if an employee forgot to clock out/in? Or any adjustment need to be made
@davidkahn7794
2 жыл бұрын
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?
@d1gm7m101
2 жыл бұрын
Did you find an answer?
@kumarsview1
2 жыл бұрын
This whole sheet can change into mobile application. If can pls tell me how do it
@Shahayar
2 жыл бұрын
is it possible to import data from an attendance machine connected to the same network using its IP?
@alissonprimo
3 жыл бұрын
isn't possible to use array formula to calculate timeout?
@eftihisbotonakis7204
Жыл бұрын
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))))))
@dcc4208
2 жыл бұрын
is there a way I can make the google form show some kind of error OR won't let them clock in (or submit the clock in entry) IF their verification code won't match? is that even possible in google forms?
@ExcelGoogleSheets
2 жыл бұрын
Not possible with Google Forms, but you can do it with a web app kzitem.info/news/bejne/rH2erYurjZx7qpg
@azhariemudakir9050
3 жыл бұрын
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?
@phunghm
2 жыл бұрын
I tried to re-deploy on my account: Google said "This app is blocked This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access." Tried to work around but no luck. Thanks for any help!
@larriebetongga1846
2 жыл бұрын
Hello, just wondering what would be the formula if you want to make sure that the duration of hours between 2 timestamp will not calculate excess hours in 8 hours shift. Thank you.
@ExcelGoogleSheets
2 жыл бұрын
I would need a better example of what you mean.
@trooperjoshua9487
2 жыл бұрын
What if I wanted to add letters and number like Josh1234
@larriebetongga1846
2 жыл бұрын
Please make sample that includes Lunch and break please. Thanks.
@RahulGupta1981
3 жыл бұрын
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.
@ogcombatacademy
2 жыл бұрын
Hi there, really nice tutorial. However I encountered an issue on the Data Processed sheet, columns H & K, REF error saying "array result was not expanded because it would overwrite data in H3". Cloumn L gives "array result was not expanded because it would overwrite data in L3". How do i fix this please? Thanks
@ExcelGoogleSheets
2 жыл бұрын
check video description.
@erikm33
3 жыл бұрын
Can you add a location, Huge favor. Your video was very informative s
@ingewll
3 жыл бұрын
I am impressed. How would you handle an employee with 2500 usd a month for 40 hours work a week
@stacyanthony43
3 жыл бұрын
The correction to the response validation code, I am still struggling to get it to work. Can anyone help?
Пікірлер: 252