How to Calculate Hours Worked In Google Sheets
Вставка
- Опубліковано 9 лип 2024
- Refine your reporting skills: Click to support and gain access to one-on-one coaching and tools: ko-fi.com/hashalinz
Your time, brains, and muscles are valuable, and you deserve to be paid for every minute you work. In this video, we'll explore how to accurately calculate hours worked, ensuring that you're equipped to advocate for your rights. No more guesswork; no more cutting corners. Watch this video and learn to assert your worth with precision and confidence, all through the power of Google Sheets and Formula24.
Follow along with this spreadsheet, then design your own with your new-found formula knowledge: docs.google.com/spreadsheets/...
----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Intro
00:29 How time works
10:06 Overtime
----------------------------------------------------------------------------------------------------
💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓ - Навчання та стиль
Thank you, this was so helpful! I never considered using an inequality to get around the negative numbers.
Thanks! Very clear explanation.
👏Thanks for creating shot and cut to point video
Hey brother this is great! I have a question tho, during every shift my employees take 30min brakes and sometimes when they do double shifts their brake is for 60min, how do i add it into the formula so it understand when it goes over 8h of work it needs to substract 60min pauses and when it's below 8h
it substracts 30min from the total of hours worked. thanks !!
Follow along with the spreadsheet used in this tutorial ➡docs.google.com/spreadsheets/d/1b8vW2_zeVjV-JQyHWrK7_mFkZLr4H3fVlNIpUFpyjL8/copy
Super helpful bro. Subbed
SO HELPFUL!!
Thanks for the video, very useful.
Thank you so much for this!
very helpful, thanks
Ive been trying to sort adding time for days. Youve just nailed it in five minutes
Perfect!
This video is very easily understood!! Thank you!
You're very welcome!
I use software that exports time in the following format, but cant figure out how to tally it.
18h 56m 25s
1d 15h 30m 17s
11h 32m 35s
22h 44m 19s
1d 8h 1m 44s
1d 9h 30m 9s
1d 18h 34m 10s
This was excellent, but when you got to the last page with all the workers on it and the delimiter....you broke my brain! LOL
Yeah that part is advanced and takes a lot of work to get you're head around it all. I like to think of the textjoins and splits as 'wrappers' around what we already have. You might've noticed we create our formula, then 'wrapped' the formula we created in the textjoins and the split. It takes some getting used to, but try to break apart the formula like an onion - read the innermost part first (inside the innermost brackets) and work your way outwards.
@@HashAliNZ Thank you. Even just the first part is saving me hours of work each payday!
Can you make a personal budget sheet that deals with multiple revenue streams and compares it to a set of budget targets etc? I'm new to sheets in any meaningful way and the way you lay forth your content is masterful!
Thanks for the easy Guide!
Glad to help!
thanks
Is there a way to format the minutes to show up as decimals? Like rather than 10:30(duration) it would be 10.5
In the case of the cell E3 you could use in an other cell =HOUR(E3)+(MINUTE(E3)/60) + (SECOND(E3)/3600)
Is there a way to calculate into 2 separate columns - One shows regular hours worked so anything 8 hours or less, and the other showing overtime hours (the amount of hours over 8) - using the start and end times?
Yeah, one way is shown at 9:36 of this video, or another method (well say method but different layout) is shown here: ua-cam.com/video/2firYvQyovQ/v-deo.htmlsi=EKfmCfn0d-p12xWO
Amazing, you should be one of Google Sheets programmers :D
Thanks so much for the kind words!
Hello, quick question. I am trying to calculate my wage per hour x my total number of hours worked in Google Sheets. I am using this formula: =C2*B2. It keeps giving me the wrong number. I'm not sure why. Thank you.
Hi Erin, I'm guessing it's because your hours are written as hours and minutes. You first need to change it to decimal hours. Check this video for how: ua-cam.com/video/hQ_6bDJ_cPU/v-deo.html
The pertinent info is at about 3:10
@@HashAliNZ Hello, thank you for your response. My apologies, but I still do not understand. Do I use the HOUR, MINUTE, and SECOND Functions to do this? Is there a way to quickly convert my time to decimal hours? I'm not the best at math. Thank you for your help.
thank you
You're very welcome
❤❤❤great❤️
Hi! If 8:11pm - 5:00 am is 8.49 hours how can I make it to exact 9 hours?
And how to calculate night diff from 10:00PM - 6:00Am if the employee enter to 1:00am to 10:00am
Please
For the first question, you can use the ROUND function or the ROUNDUP or the MROUND. Totally dependent on the way your workplace works, just make sure you're not breaking laws by short-changing your employees (a few people I've helped out didn't realise that in many jurisdictions rounding timesheets comes under wage theft and the company can be penalised very heavily).
For the second question, I'm not sure what you mean. 10PM - 6AM should give you 8 hours as shown in the video, and 9 hours for 1AM - 10AM. If they are two different shifts in the same day then they should be entered on different rows.
@@HashAliNZ how can i get the night shift differential of 1:00am to 6:am using google sheet formula sir?
Let's say 6am is the end time and is entered into D5, and 1am is the start time and is entered into C5.
Then it would be simply =D5-C5
The result will give you 5AM, so go up to the Format and change the format to Duration instead of Time.
@@HashAliNZ hi sir thank you for your response
Can i ask one more pls. I need it so much.
How to combine the formula stated on the video and the formula that you’ve been said to me now?
Thank you so much for your upcoming reply
Hello! How can you compute for the average hours worked?
In the last section I show how to do the analysis showing the SUM of the values. In the pivot table sidebar, simply change SUM to AVERAGE and it'll do it for you!
Helped a lot brother... Keep it up
Thanks so much Shubham!
Hey there, i need some help with redoing my sheet, having difficulty when it comes to adding up the total hours. think you could help me out ? do you have discord?