Google Sheets - Timesheet, Formulas, Time Calculations - Tutorial

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

[@isaiahlopez51]

I love the way you break each formula down and show us how each part is developed. Great video.

[@Finsproutcom]

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

[@ExcelGoogleSheets]

Great to hear!

[@undercovers2699]

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 KZbin? This is by far the best from the others! thank you for making this video it helps me a lot

[@nickernator4788]

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.

[@jonathanbaluyotfrancisco7885]

the most straight forward tutorial ive seen in years

[@ExcelGoogleSheets]

Thanks!

[@coconutsnap]

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.

[@ExcelGoogleSheets]

👍

[@halodoing]

Great Video! Eagerly looking forward for the clock in and clock out through web apps tutorial!

[@yukonakasone5481]

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!! 🙏🙏👏👏

[@ExcelGoogleSheets]

👍

[@dalejones872]

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.

[@dalejones872]

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.

[@MannyBernabe]

Overnight solved! Yeah. Now I can track my sleep hours. Thank you!!

[@tfalani3861]

Thank you for this. This is exactly what I needed lol I’m a dinosaur finally getting into computing and programming😄

[@elbilbeisi]

You are a wonderful teacher, keep the videos coming!

[@rachelhaslam-gibson7837]

Massive thanks for this. Incredibly well explained. Following you for sure

[@shadowwalker2455]

BOOM! and my all doubts are cleared,thanks.

[@Chaelantonio2]

Teacher you are the best, just wondering if youcould do a video which includes breaks and lunches during the shift.

[@mixahl100]

CONGRATS THE ONLY TRUE HELP!

[@ultraviolet7741]

This is one of the most helpful videos ever. Thanks so much!

[@jenniemiron6225]

Works great! Thanks for sharing!

[@kasiaferenczuk6543]

This is amazing video. Step by step, thank you soooo much!!

[@ameliegifford1477]

Thank you this makes life so much easier

[@ExcelGoogleSheets]

:)

[@md.rashedrayhan5518]

very helpful video No bullshit, only value that u are providing. Thanks a lot

[@corissab]

You are awesome. Thank youuuuu

[@SideHustler365]

Very useful video! Thank you!

[@rabidralph]

awesome stuff so glad I found your videos

[@ExcelGoogleSheets]

Welcome aboard!

[@sophiamauldin2208]

Such great instruction! Thank you for the incredibly helpful explanation!

[@ExcelGoogleSheets]

Glad you enjoyed it!

[@CardsharkMr]

Thanks for this vid. Great content

[@ro01dri87go]

Thank you! but, I have a question How do I do if I want to add an hour lunch ?

[@frasersweeney9666]

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.

[@aliciamesa23]

Thanks for this tutorial!

[@JohnDeir]

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.

[@JohnDeir]

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.

[@martincrisbuenafe6761]

This helped a lot. Thanks

[@latazz4908]

Great video, helped with alot

[@ExcelGoogleSheets]

Glad to hear it!

[@sukhdavsingh4216]

This is tremendously helpful. Thank you very much.

[@ExcelGoogleSheets]

Glad it was helpful!

[@homechefschem]

I like it❤️The explanation is very clear.

[@jaumearagaybadia3357]

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

[@happyfamilylifescreations]

really was educating..thank you so much..

[@sea_hous]

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?

[@Zoe-gp3ps]

Thank you so much! This is just what I wanna know!

[@rsmart7034]

Very Helpful, Thanks

[@surajsingh-qk5uf]

its really great formula thank you dear friend

[@cuba8636]

Gracias por compartir tus conocimiento

[@dorisspivey1391]

Thank you Thank you Thank you I have save this video you are awesome

[@aahogsbgone]

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?

[@coffeeinsanity9712]

Nice video. Thank you! How would you make a stop watch?

[@annandae.kurotsuchia.b.2820]

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.

[@anonymoushacker6411]

Really helpful and understandable ..thanks 💛

[@walterpaiva719]

Thanks for the video, you really help me out

[@marylunaneale9537]

How about if less breaktime/lunchbreak?

[@kphilipps55]

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?

[@PrDannyRomero]

10:59 11:00AM - 2:00PM isn't overnight but I get the idea 😂🙏

[@josephbarredo3]

NEW IN EXCELL WATCHING FROM QATAR

[@alexislassman4138]

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?

[@christinaosborne6304]

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.

[@eriksonderlon]

Thank You!! So good!

[@khhanssen79]

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?

[@madelinegrudens]

My non math brain thanks you!!!

[@tracylishaedwards7600]

Great video. I walked through your steps at least 10 times but the formula equals out to zero??? Am I doing something incorrectly.

[@balcazar83]

How can we do a biweekly time sheet I really need one. And you are great love the videos

[@JuanAguilar-Cardon]

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.

[@MsLadyReignicornfrvr]

THANK YOU!

[@markuswinter-cdps3008]

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.

[@SunilKumar-ox1kt]

Thank you so much

[@DavidPerez-uj5ui]

What about when you have "time in, time out, time in, time out." Is there a formula that can go into one cell?

[@paulloup5210]

Thank you very much

[@420GratefulHippie]

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.

[@ExcelGoogleSheets]

Add round around MOD. not around IF

[@otrobusiness3700]

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

[@frankcklin]

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.

[@royrobles8653]

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!

[@Lukenbergsky]

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.

[@gabybarrientos2956]

Thank you!!!!! 🙏🏻

[@littlenose4085]

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?

[@dougferguson2384]

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.

[@ExcelGoogleSheets]

Depends on how you enter duration. It could be just a simple SUM(cells) or it could be SUM(cells)*24

[@happyfamilylifescreations]

Please do more on Google Sheets,Slides for purpose of clerical positions..thanks

[@abbacohen9857]

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

[@silviasoria2374]

thank you!!!

[@jordanoliphant7420]

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.

[@H-do8tr]

Good tutorial 😍 How can I apply the arrayformula with total columns? It’s not easy

[@alicekelly5676]

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?

[@peteradeteacher1380]

i can do it for you but at a little price. check my video here. kzbin.info/www/bejne/Z5LFm3WoiNaHpM0 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

[@user-ey1ow9te6r]

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?

[@lasseclausen5876]

What if there's a difference in pay? Like with my company they get 13USD before midnight, but from 12am -> 6am, they get 16USD?

[@jhonny001]

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 ?

[@jpgcams]

How do you calculate elapse time with day, hour and minutes excluding weekends?

[@morozco829]

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!

[@RoyStevenUngKengSoon]

Hi, need a bit of a help with Google Sheet. I am uncertain if this is the appropriate place to ask, so please remove if it is not.. Question: A1=now(), which shows the current date and time How do I do conditional formatting on cell A1 to show the Text Color Bold+Green if the time is between 9:30am until 4:30pm (shop is open) AND Text Color Red if the time is between 8:29am until 4:31pm (shop is closed) Thanks for the help

[@hazemali382]

more than great

[@thekingmodular7184]

is there any way I can add lunch break to the weekly timesheet?

[@kaylabaroo5400]

How can this formula be included in a timesheet that includes lunch breaks?

[@abdullah-al-raihan1351]

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?

[@SubodhKumar-fb4nh]

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

[@GershamCharles]

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?

[@DarkMoonDroid]

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*

[@ajmaljohnson8836]

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

[@gopaljat4158]

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

[@clavosdelacruz]

can you do a video for terps of a spread sheet for cents per minute instead of dollars per hour?

[@Gayiza]

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?

[@ExcelGoogleSheets]

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.

[@Gayiza]

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

[@amandamartinez7922]

This would be amazing if it added the in and outs for lunch for overnight shift 😩😩😩

[@Powercat808]

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.

[@ExcelGoogleSheets]

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.

[@Powercat808]

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

[@ExcelGoogleSheets]

@@Powercat808 Enter time ins and time outs again.

[@Powercat808]

@@ExcelGoogleSheets I did and still get the error. Maybe we could PM or email?

[@Powercat808]

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

[@juanpauloalmazan2997]

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.

[@ExcelGoogleSheets]

Depends on format of your data. But either way, you would have to use some JavaScript to make it happen.

[@user-yo7wt7yr7j]

Very usefull bro,,,but overtime also we can add,,,

[@gnaff9093]

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.

[@ExcelGoogleSheets]

Add 9 AM for 2 rows, then select them together and drag.

[@bill9912]

If someone earned $100 per shift, how do you calculate the dollars per hour rate?