I really appreciate and like your methods of explaining the process. My favorite part was how you would put the keybinds to quickly do that action on screen.
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 !!
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!
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 My google sheets is just in this case driving hours for July 2024. So a line per date in July 2024 (31 lines, as we have 31 days in July 2024). I have created the same formula as you have shown for each of the days (some days the total drive time is 0, because I was not working). BUT the issue is when Iwas to SUM up all 31 days to see how many hours I have been working totally per month. When I make the sumup I get the issue, and cannot make it work. I want to make the sum of F3 to F33. Like you in your video (Time stamp 3:45 in your video). Each cell from F3 to F33 (F coloum is the total hours) and is formatted as "Duration" and each cell has this formula [(=E3-D3+(D3>E3)]. D is start time and E is stop time. My sum formula is looking like this (=SUM(F3:F33), i have tried many different versions, but none has worked. Also "arrayformula" ect., but cannot make it work. In this case it come with a result 5,69 total hours, but it should be 148 hours with is the correct number. If you can tell me what my errror is, or how to solve this you will become my Goolge Sheets NINJA 🙂
@@HashAliNZ In my sheet the column D is start time and E is ending time: Line 3 to 33 is all the 31 dates of the month (July 2024). Column F is the total daily hours where I have made the formula as per your video: =E3-D3+(D3>E3) My issue is that II am trying to calculate all the hours for the month, here my formula is per your video (timestamp 3:34) =SUM(F3:F33) I have tried many different versions (including arrayformula), but I will not be correct. The correct number is 148 hours, but in this case the result is 5.69. All the cells in between F3 and F33 is all formatted as “Duration” Do you need any other info? If you can solve this you will be my Google Sheet NINJA 🙂
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
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: ruclips.net/video/2firYvQyovQ/видео.htmlsi=EKfmCfn0d-p12xWO
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.
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, 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: ruclips.net/video/hQ_6bDJ_cPU/видео.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.
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!
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?
![Quando Rondo - Life Goes On [Official Music Video]](http://i.ytimg.com/vi/eIf3fhMXAow/mqdefault.jpg)
I like the way he shows his logical thinking progress and advances to newer and better thinking
Follow along with the spreadsheet used in this tutorial ➡docs.google.com/spreadsheets/d/1b8vW2_zeVjV-JQyHWrK7_mFkZLr4H3fVlNIpUFpyjL8/copy
This was incredibly helpful and the first time I didn't hardcore struggle with formulas, thank you!
I really appreciate and like your methods of explaining the process. My favorite part was how you would put the keybinds to quickly do that action on screen.
Ive been trying to sort adding time for days. Youve just nailed it in five minutes
Perfect!
Thank you, this was so helpful! I never considered using an inequality to get around the negative numbers.
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 !!
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!
Simplicity and amazingly helpful!
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!
This video is very easily understood!! Thank you!
You're very welcome!
Thanks! Very clear explanation.
👏Thanks for creating shot and cut to point video
Amazing video. TYSM
You are so welcome!
Amazing, you should be one of Google Sheets programmers :D
Thanks so much for the kind words!
Super helpful bro. Subbed
Thanks for the video, very useful.
Thank you so much for this!
SO HELPFUL!!
Thanks for the easy Guide!
Glad to help!
very helpful, thanks
I still is having problems with my simple calculation. But with you it looks so easy. 🙂
Copy and paste the formula and I can try figure out the issue
@@HashAliNZ My google sheets is just in this case driving hours for July 2024. So a line per date in July 2024 (31 lines, as we have 31 days in July 2024). I have created the same formula as you have shown for each of the days (some days the total drive time is 0, because I was not working). BUT the issue is when Iwas to SUM up all 31 days to see how many hours I have been working totally per month. When I make the sumup I get the issue, and cannot make it work. I want to make the sum of F3 to F33. Like you in your video (Time stamp 3:45 in your video).
Each cell from F3 to F33 (F coloum is the total hours) and is formatted as "Duration" and each cell has this formula [(=E3-D3+(D3>E3)]. D is start time and E is stop time.
My sum formula is looking like this (=SUM(F3:F33), i have tried many different versions, but none has worked. Also "arrayformula" ect., but cannot make it work. In this case it come with a result 5,69 total hours, but it should be 148 hours with is the correct number.
If you can tell me what my errror is, or how to solve this you will become my Goolge Sheets NINJA 🙂
@@HashAliNZ In my sheet the column D is start time and E is ending time:
Line 3 to 33 is all the 31 dates of the month (July 2024).
Column F is the total daily hours where I have made the formula as per your video: =E3-D3+(D3>E3)
My issue is that II am trying to calculate all the hours for the month, here my formula is per your video (timestamp 3:34)
=SUM(F3:F33)
I have tried many different versions (including arrayformula), but I will not be correct. The correct number is 148 hours, but in this case the result is 5.69.
All the cells in between F3 and F33 is all formatted as “Duration”
Do you need any other info?
If you can solve this you will be my Google Sheet NINJA 🙂
Is the sum formatted as a duration too?
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
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)
How can I calculate overtime in that type of data overtime of employees
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: ruclips.net/video/2firYvQyovQ/видео.htmlsi=EKfmCfn0d-p12xWO
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, 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: ruclips.net/video/hQ_6bDJ_cPU/видео.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.
thanks
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!
thank you
You're very welcome
❤❤❤great❤️
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?