Google Sheets - Timesheet, Formulas, Time Calculations - Tutorial
HTML-код
- Опубликовано: 14 окт 2024
- In this tutorial we'll build daily & weekly timesheet templates in Google Sheets. You'll learn how to do time calculations, how times are stored in spreadsheets, formulas necessary to know in order to work with time-sheets, regular and overnight shift calculations and more.
Functions Covered: MOD, ROUND, MROUND, IF, SUM, COUNT, AVERAGE, SUMIFS.
Support the Channel:
/ chicagocomputerclasses
Final Spreadsheet:
docs.google.co...
#googlesheets #timesheet #formulas
I love the way you break each formula down and show us how each part is developed. Great video.
the most straight forward tutorial ive seen in years
Thanks!
Love the way you explain things bro, I mean' breaking down all formulas and explaining all of it manually, Of all the videos I've watched in RUclips? This is by far the best from the others! thank you for making this video it helps me a lot
The simple explanation of how time is calculated helped me solve my issue. Thank you so much!
I was attempting to create a sheet that could calculate my working hours with a flexible schedule. My company requires that I work only 40 hours no more no less, but allows more flexibility throughout the week. Every week on Friday I am able to go home early but I must calculate how many hours I need to work.
The problem was that my start time varies and my lunch varies in length. I would normally calculate how many hours I needed for the entire day when I started work, and then I would subtract my lunch and recalculate to determine what time I'm supposed to clock out.
I created a simple sheet that tracks my hours throughout the week, then I simply punch in My start time plus my lunch in/out, and my sheet shows me exactly what time I need to leave to hit 40 hours.
Oh my god thank you so much, you are amazing. I've been trying to solve the "crossing midnight" problem for two days, looking at tons of videos and explanations. Yours is the first I was able to truly follow, and gave me all the guidance I needed to get my spreadsheet working. Fantastic video.
👍
Great Video! Eagerly looking forward for the clock in and clock out through web apps tutorial!
I was never interested in google sheet or excel. However your videos are so good that I start learning and using more google sheet at work. Thanks a lot🙏🙏
Great to hear!
Great way of teaching ❤ love from india
Holy cow man, I am right in middle of this very project right now! I'm interested in any videos you'd like to make on this topic as your content is excellent every time. In fact if there's anything I can do to help please don't hesitate to reach out to me.
Dude!! I made my previous comment before getting through the first minute of your video. I'm still not even half way through and you have already made my timesheet better. I love finding out that I'm not as Sheets-savvy as I think I am when someone shows how to use a formula I haven't used yet. Thanks again for sharing and please keep 'em coming.
Teacher you are the best, just wondering if youcould do a video which includes breaks and lunches during the shift.
Overnight solved! Yeah. Now I can track my sleep hours. Thank you!!
Thank you for this. This is exactly what I needed lol I’m a dinosaur finally getting into computing and programming😄
BOOM! and my all doubts are cleared,thanks.
Massive thanks for this. Incredibly well explained. Following you for sure
This is amazing video. Step by step, thank you soooo much!!
Bless your heart for all the hard work you've done to teach those of us that wasn't given the opportunity in the past! May the Lord continue to bless and keep your safe during this pandemic! Keep it coming!! 🙏🙏👏👏
👍
very helpful video
No bullshit, only value that u are providing.
Thanks a lot
Thank you this makes life so much easier
:)
Works great! Thanks for sharing!
You are a wonderful teacher, keep the videos coming!
You are awesome. Thank youuuuu
Hi there! :)
Did you finally get to do the Clocking Web App you comment at the beginning of this video, is it planned for the near future or was there not enough interest in it?
You have my vote for it! ;)
+1
Thank you! but, I have a question How do I do if I want to add an hour lunch ?
I think you have to add an "IF" function - IF the time worked is greater than 0, -1 hour the total time worked.
This also ensures you don't take an hour off the total time worked for days you didnt work.
Very useful video! Thank you!
Do you have a recommendation on how to set up a sleep schedule on sheets so that it can be made into a graph after?
This is one of the most helpful videos ever. Thanks so much!
I've been working on a similar but way more intense weekly timesheet for employees to submit, but they only list hours they work per day (no time in or out) but I have a data validation list of the type of work per hours each day and have a list of 4 matching cells for hours to type of work per day. Thus the hours per week for every employees is figured plus the hours (total hours) per the type of work (project) for each employee. Each employee has their own timesheet for the year they fill in per week and the accountant gets the information by way of Importrange into a seperate sheet with individual sheets and one massive combined sheet with total hours per person as well as hours per type of work (project) done.
I would post a link to a copy but it has gotten to massive and complicated so how would you go about doing something like this?
PS- I find your videos very good and informative and many thanks, I've learned much watching.
I want to expand why the above. I want to have where each employee doesn't have to submit their timesheet. It automatically shows up for the accountant and much of the math for deductions is done at the same time. Also charts are updated per projects going on as well as hours and the title of each employee so management can track each project. Thus everything is updated real time as employee inputs data.
CONGRATS THE ONLY TRUE HELP!
Thanks for this tutorial!
awesome stuff so glad I found your videos
Welcome aboard!
Nice video. Thank you! How would you make a stop watch?
This helped a lot. Thanks
I was watching this video (actually a lot of videos in your channel I love it!) But I noticed that calculations of the minutes was actually wrong....it happened the same to me when I was trying to repeat your process, basically it calculates the hours correctly but not the minutes.... do you know how to fix it?
Thanks for this vid. Great content
Good video! This was very helpful. What I’m trying to figure out now is I want to make sure that the employee has eight hours off before he/she comes back on duty. Can you make a video showing this example?
That's pretty cool knowing if we put the hours into numbers we get a percentage of the day. How ever I noticed on the total hour calculation on your video at 7:25 min that the Tuesday 9:00AM - 4:30PM total calculation is wrong? The result is 7.50 but shouldn't be 7.30? Sorry if I'm wrong but I was just curious.
Hello, I have a question.
How do you get a formula so it doesn’t round up your total pay for the week. For example if the hours rate was $18.50? It automatically rounds up to $19.
Great video, helped with alot
Glad to hear it!
Such great instruction! Thank you for the incredibly helpful explanation!
Glad you enjoyed it!
really was educating..thank you so much..
Very Helpful, Thanks
its really great formula thank you
dear friend
Excellent tutorial! I am creating my timesheet following your instructions exactly. My pay rate is complicated though and Im hoping you can tell me how to calculate on the timesheet: I get paid a different rate for 20 hours out of the total I have worked. For example, if I work 60 hours, 20 of those is paid at a different rate, and the remainder 40 hours at another rate. How and where should the formulas on the spread sheet calculate this?
This is tremendously helpful. Thank you very much.
Glad it was helpful!
I like it❤️The explanation is very clear.
How can we do a biweekly time sheet I really need one. And you are great love the videos
Hi, thank you for a excellent tutorial! I have tryed to follow it on my google sheet . step by step. But when i try to convert from clok to numbers. Nothing happens. Any idea whats wrong?
American don't have a shift over 24 hours even 36 hours, but some asian workers does with normal time and overtime period, hence make this issue very complicated. I try to deal with it by date time format.
How about if less breaktime/lunchbreak?
Is there a way for me to add weekly hours? Everything that I'm finding relates to calculating hours after clock in and clock out. I just need the total weekly hours calculated for every week.
Thank you Thank you Thank you I have save this video you are awesome
Thank you so much! This is just what I wanna know!
My non math brain thanks you!!!
Thanks for the video. I want to be able to add time durations. Let's say I know the durations of 5 videos and want to add them up to give me mm:ss and if the total is more than an hour show hh:mm:ss? I'm not sure exactly how to format the input cells as well as the output cell.
Depends on how you enter duration. It could be just a simple SUM(cells) or it could be SUM(cells)*24
Gracias por compartir tus conocimiento
Thank you! Wondering if there is a way to restrict the data entry for a cell to be in a time format only. Even when a cell is formatted as hh:mm, for example, an end user can still enter "8" as a valid entry. Trying to either have the cell automatically convert the 8 to a time (which would be 12:00AM), or have the cell indicated that "8" is not a valid entry. There is nothing under Data Validation to compel an end user (as there is for Date, etc.). This was possible in Excel. Have not been able to accomplish this in Sheets. Soooooooooo frustrating!! Thanks for your time and expertise.
Thanks for the video, I just have an arithmetic question. You use 1 as a divisor in mod to obtain the difference of 100% of the time if the number is negative. How does it work? I mean if you divide a number by 1, you obtain the same number, but here we obtain the difference. Probably is something really simple, but I just don't get the logic, yet jajaja. Could you please explain? :D
Hi! thank you for the tutorial. QUESTION: What if I wanted the time to be determined by duration? I'm working on a spreadsheet for a show, and need to calculate when a show segment is 2 min, how to add 2 min to 3:00 PM (when the show starts for example). So the answer would be 3:02 pm. Then have the following cells automatically calculate the following show segments based on the duration assigned to those segments adding to the previous 3:02 pm calculation. Makes sense? Thanks in advance for any help with this!
hey great video
i know it is old but but could you help me to do minutes to for example if i came to work at like 10am and left 12.23
Please do more on Google Sheets,Slides for purpose of clerical positions..thanks
10:59 11:00AM - 2:00PM isn't overnight but I get the idea 😂🙏
What about when you have "time in, time out, time in, time out." Is there a formula that can go into one cell?
Thanks for the video, you really help me out
This is a great video, helped me understand quite a bit--but I'm needing to create a time sheet with 2 or 3 Ins and Out times, to reflect lunch hour taken, or work cut up throughout the day. How do I create a time sheet that will calculate total hours over two or three chunks of time worked in one day?
i can do it for you but at a little price. check my video here.
ruclips.net/video/1abjBrSqTnk/видео.html
then click the below link to download sample file and make your own timecard. though its not exactly what you are looking for. but just to show u i can make it
drive.google.com/file/d/1v86XROD5CUSXRRsNxgOaFo2nQxHzfDBn/view?usp=sharing
Really helpful and understandable ..thanks 💛
Great video. I walked through your steps at least 10 times but the formula equals out to zero??? Am I doing something incorrectly.
Hello sir, I've been watching your videos. Just wondering if you have similar video for converting time from specific time zone to utc and for time with backward timezone. Hoping for your response.
Depends on format of your data. But either way, you would have to use some JavaScript to make it happen.
Hi thanks for this tutorial, how would I round down to 60 min or the hour, e.g if the total is 6:70 how would I round it to 7 hours and 10 minutes?
Dude, I think I figured out a way to do it but it's messy. It requires 3 squares.
I learned it from another guy teaching how to do this on Excel. I'm sure there's a shorter way to do this in the Goog, but I don't know all the functions well enuf yet. Here's the video title:
*Convert decimals to time in Excel by Chris Menard*
NEW IN EXCELL WATCHING FROM QATAR
I want the "hours" cells to be blank when no time in and outs are entered, so I did =(IF( COUNT(E10:I13)=2,MOD(E12-E10,1)*24,"") and that works.
But if I also want it to ROUND and I do =ROUND(IF( COUNT(E10:I13)=2,MOD(E12-E10,1)*24,""), 0) it now adds the 0.00 into cells if I leave the time in and time out cells blank. How do I use the round function and have the "hours" cells remain blank for the days I don't enter the time ins and outs?
Also is there a way that to get the hours to display without the decimal point? Ex: 8 instead of 8.00.
EDIT:
I actually wanted to round up for any portion of an hour that I work as this is the rule for the union I'm in.
So I figured out that I can use the "CEILING" function instead of "ROUND".
=CEILING( IF( COUNT(E10:I13)=2, MOD(E12-E10,1)*24,""),1)
The ,1) at the end of the function rounds the up to the next hour.
So if I work from 6:00 AM to 2:20 PM it now calculates to the next hour and makes it a 9 hour day.
If you want it to round down then use ,0.5) to round to the nearest 1/2 hour. This works better that "ROUND" in my opinion.
But using "CEILING" or "ROUND" or "MROUND" still makes it display 0.00 for days I didn't work and left the time in and out blank.
It's ignoring the "" double quotes in the formula.
Add round around MOD. not around IF
Hi!
I am trying to write a code on Scripts to create a timesheet for Night Hours. I am having a lot of problems trying to get values from cells with the time. On the spreadsheet, the value is given by a number from 0 to 1, but on the script is different... Any ideas ?
This is great and very helpful. The problem I'm running into is when I use the same formula =IF( COUNT(C9:D9)=2,MOD(D9-C9,1)*24,0) in my excel 365 it populates an error. Is there something that needs change? Because the calculation of hours is correct its just gives an error and I can't figure the problem. Of course the values match my cell values.
No, but you want to make sure you have proper times in Excel. For example typing 10AM will not result in proper time in Excel, While 10 AM would be OK.
@@ExcelGoogleSheets I'm getting the error in the hours counted column. I even changed the custom time format to what is in your spreadsheet and still the error populates. Can't figure out why.
@@Powercat808 Enter time ins and time outs again.
@@ExcelGoogleSheets I did and still get the error. Maybe we could PM or email?
@@ExcelGoogleSheets Ok, I think the problem was I tried adding the formula with old data. Once I deleted the old times, re-added the formula the retyped times then it worked, thanks.
Good tutorial 😍
How can I apply the arrayformula with total columns? It’s not easy
My formulas are refusing to calculate my times if I am using colon, and I really want to use colon in my times but get duration in hours and minutes.
Like this:
Time in
18:45
Time out
23:35
Elapsed Time (duration)
4h 50 min
But I am forced to write 18.45 and 23.35.
How can I make the times with : to be accepted when I am using Time calculations?
Great video, but I need some help. So for my employees, once they work over 20:00pm I need a format to work with the Clock format to show me the Hours / Minutes they overdo past 20:00pm.
I need to track accrual of sick time for employees based on actual hours worked. For every 40 hours worked, they accrue 1 sick hour. How do I do this in google sheets?
Thank you so much for this, I'm actually working on an AppSheet Timesheet.
I "imported" data from Sheet A to other Sheets and then Filtered as per Employee Name to achieve displaying one Employee per sheet.
How can I achieve calculating Overtime + special weekend and holiday rates?
No easy answer. I would have to know what qualifies as overtime or special, holiday rate and how your data is presented. Most likely some sort of IF statement.
@@ExcelGoogleSheets normal working hours would be 8. I think the Holiday work would need it to be integrated into a Calendar so that it pulls days from there
is there any way I can add lunch break to the weekly timesheet?
can you do a video for terps of a spread sheet for cents per minute instead of dollars per hour?
I want to copy a row value that is changing wrt time. I want to create series of data with time in another sheet in google spread sheet. How it is possible
Hi thank you for your videos but im trying to do something but cant find nothing on it here it is so say i have (102000 in a column) and then divided by 200 give me (8hrs 30min) in different column how do i do that in in google spreed sheet can you help me plz?
I don't understand the question.
102000/200/60 => 8.5
Do we have to use google docs here, can we use it in local pc Excel.
If not all, most of it should work in Excel.
Thank You!! So good!
How do I add late fee after a certain date? Say I have columns balance, amount due, amount paid, remaining balance, date paid and late fee... If I pay say after the 6th of the month how so I add a late fee of say $35?? Thanks!
Thank you for your excellent tutorials.
Would it be possible to create custom functions like this:
A1 Is a Checkbox (True or False)
A2 Has a custom function that shows the email of the user that checked A1 ("True") for the last time. Something like this: A2 = IfTrueShowUseremail (A1)
A3 Has a custom function that shows the timestamp of the last time A1 checkbox was checked ("True"). Something like this: A3 = IfTrueShowTimestamp (A1)
These functions should work for any Google Sheet owned by all the members of the organization under the same Google Suite domain. Only members of the organization will be checking the checkboxes.
How can this formula be included in a timesheet that includes lunch breaks?
Hello.
Google spreadsheet per entry data automatically time change enter data .
Deference time and date sheet enter
.. time formula I your video watching and s'pose that your entry 1 yesterday and 1 entry today deference. But this formula auto full detail mach .
Plz answer
Thank you so much
How do you calculate elapse time with day, hour and minutes excluding weekends?
What if there's a difference in pay? Like with my company they get 13USD before midnight, but from 12am -> 6am, they get 16USD?
does this formula work for more than 12hrs shift? overnight? like for example 12hrs shift, time in is 6 pm and time out is 6am or even 8am (with 2hrs overtime)? mine keeps on getting negative digits.. :(
It should work for short overnight shift, but in all other cases you will have to enter both date and time in the cell, instead of just time.
Ex.
Time in: 9/11/2021 8:00 PM
Time out: 9/12/2021 11:00 PM
couldn't you put, Monday, time in 18:00, time out 24:00, then do Tuesday, time in 00:00, time out 06:00. Or obviously 12 hour clock instead. but I think that would work because you are technically working till the next day
more than great
can we convert now() function to number when i give in google sheet ,it will give the numberof date+time.any solution ,i want only time to number but converting to time+date to number please help sir
I'm very new to the google apps. I have still yet to figure out how the format short cuts work. Such as I never could select a cell (at the beginning of this video) how to select and drag the 9am through all the days. It kept adding an hour to each downward cell. And the copy function isn't working for me..I'm sure it's me...just don't know the shortcuts that seem to be a bit different than Excel.
Add 9 AM for 2 rows, then select them together and drag.
THANK YOU!
The issue I've ran into in creating a weekly time card/pay stub is to automatically calculate the hours up to 40. And then anything over 40 hrs being calculated into a different cell. Been working on this for hours now. Driving me crazy.
ruclips.net/video/Ymli-WDXvAs/видео.html
If someone earned $100 per shift, how do you calculate the dollars per hour rate?
How can you add over time pay ? Can you help
Very usefull bro,,,but overtime also we can add,,,