Google Sheets - Timesheet, Formulas, Time Calculations - Tutorial
Вставка
- Опубліковано 6 бер 2019
- 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.com/spreadsheets/...
#googlesheets #timesheet #formulas
I love the way you break each formula down and show us how each part is developed. Great video.
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.
Great Video! Eagerly looking forward for the clock in and clock out through web apps tutorial!
You are a wonderful teacher, keep the videos coming!
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 UA-cam? This is by far the best from the others! thank you for making this video it helps me a lot
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.
👍
Massive thanks for this. Incredibly well explained. Following you for sure
Thank you so much! This is just what I wanna know!
This is one of the most helpful videos ever. Thanks so much!
Such great instruction! Thank you for the incredibly helpful explanation!
Glad you enjoyed it!
Works great! Thanks for sharing!
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!! 🙏🙏👏👏
👍
This is amazing video. Step by step, thank you soooo much!!
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!
BOOM! and my all doubts are cleared,thanks.
Thanks for the video, you really help me out
Really helpful and understandable ..thanks 💛
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.
Thank You!! So good!
the most straight forward tutorial ive seen in years
Thanks!
I like it❤️The explanation is very clear.
Overnight solved! Yeah. Now I can track my sleep hours. Thank you!!
Thanks for this vid. Great content
Gracias por compartir tus conocimiento
This is tremendously helpful. Thank you very much.
Glad it was helpful!
Thank you for this. This is exactly what I needed lol I’m a dinosaur finally getting into computing and programming😄
Thanks for this tutorial!
Very useful video! Thank you!
Thank you!!!!! 🙏🏻
THANK YOU!
really was educating..thank you so much..
CONGRATS THE ONLY TRUE HELP!
very helpful video
No bullshit, only value that u are providing.
Thanks a lot
This helped a lot. Thanks
Very Helpful, Thanks
You are awesome. Thank youuuuu
Thank you very much
thank you!!!
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?
its really great formula thank you
dear friend
Thank you Thank you Thank you I have save this video you are awesome
Teacher you are the best, just wondering if youcould do a video which includes breaks and lunches during the shift.
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!
Thank you this makes life so much easier
:)
awesome stuff so glad I found your videos
Welcome aboard!
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?
Thank you so much
Great video, helped with alot
Glad to hear it!
Nice video. Thank you! How would you make a stop watch?
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 ?
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.
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
Very usefull bro,,,but overtime also we can add,,,
How about if less breaktime/lunchbreak?
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
more than great
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
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?
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.
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.
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!
10:59 11:00AM - 2:00PM isn't overnight but I get the idea 😂🙏
My non math brain thanks you!!!
Please do more on Google Sheets,Slides for purpose of clerical positions..thanks
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?
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
Good tutorial 😍
How can I apply the arrayformula with total columns? It’s not easy
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*
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.
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.
What if there's a difference in pay? Like with my company they get 13USD before midnight, but from 12am -> 6am, they get 16USD?
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.
NEW IN EXCELL WATCHING FROM QATAR
How do you calculate elapse time with day, hour and minutes excluding weekends?
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
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?
How can we do a biweekly time sheet I really need one. And you are great love the videos
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 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?
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
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.
ua-cam.com/video/1abjBrSqTnk/v-deo.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
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?
Can you make a list with whole hours without having to type every entry? Like 08:00, 09:00, 10:00 and so on.
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.
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.
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.
can you please look at the video run time 7.30min? and the cell no D8? why 9:00 AM to 4:30 PM is 7.50? should not it 7.30 min?
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.
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
How can put break/luch time in this?
Great video. I walked through your steps at least 10 times but the formula equals out to zero??? Am I doing something incorrectly.
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
How can this formula be included in a timesheet that includes lunch breaks?
What about when you have "time in, time out, time in, time out." Is there a formula that can go into one cell?
This would be amazing if it added the in and outs for lunch for overnight shift 😩😩😩
How can you add over time pay ? Can you help