Sum the Hours over 24 Hours - Excel Trick
HTML-код
- Опубликовано: 22 июл 2024
- In this video, we sum the hours of a weekly timesheet. These hours total over 24 hours. This creates a problem.
In the video, we see how to write the formula and then use custom formatting to total the hours over 24 hours.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
Brilliant! So simple when you know how, and no unnecessary filler content. Thanks!
You're welcome. Thank you.
Genius! Done this ages on a work laptop and needed it again, new it was something to do with H. Nice neat, quick to the point!
Thank you.
Exactly what I needed. Thanks for making it so easy to follow!
My pleasure Ryan. Great to hear.
Nice little trick Alan, many thanks!
Cheers
Mohideen
1. Love the accent! 2. This is exactly the answer I was looking for. 3. This also works in formatting for the python module XlsxWriter
Thank you Mathijs.
Thanks so much I have to do spreadsheets to capture the hours my engineers spend on tasks and this helps no end.
Excellent! Happy to help, Louise.
Thank you so much. I was racking my brain. I love you!
You're very welcome Diana. Thank you.
after about two hours of looking for this formula ... I foud your video it was great thank you and easy
Excellent! I'm very happy to have been able to help.
Brilliant. Just saved me a lot of time today. Many thanks.
You're very welcome Mohamed.
If I have 5 days 8.15, 8.35, 7.35, 8.00 and 8.35 and I want to crear a formula that adds only anything 8 and under and another formula that adds anything above 8 hours for the overtime. What suggestion can you give me?
Big help thank you this just had me stumped. Glad to find the video! 👌🏽
No problem 👍
I've been struggling with this. You a genius. Thank you 🙏
No problem 👍
Hi, I need to do it on ipad version of excell. Do you have any idea?
Thanks
Been struggling to find the solution to this trick, thanks for the video man.
No problem Kwanda 👍
Life saver right here !
Thank you
No problem!
This helped me figure out what I was doing wrong. Worked on this for a long time till I seen your video. Thank you!
Glad it helped Cory!
5555
You explained it so well! It was very simple and helpful! Thank you :)
You're very welcome! Thank you for your comment, Sara.
Thanks buddy could the same trick to calculate each day also as I dont have a break in my hours
Question to ask. I work two 24 hour shifts per week. The shifts are broken into three 8 hours blocks. 07:00-15:00, 15:00-23:00 and 23:00-07:00 the next morning. How can I write a formula that will calculate my total hours worked. For example I clock in at 07:00 and out at 16:00. Then back in at 22:00 and work until the next morning until 07:00. Thanks in advance for any help.
Just what I was looking for. Thanks!!
Great to hear Megan.
how do you calculate overtime if one day i worked 24 hours straight with 1 hour break, is it 8 hours regular time and 15 hours overtime?
Thank you so much! Great and simple explanation!
Thank you very much Dana.
how to calculate two shift start morning 8 to 4 and start second shift 6 to 12 am how to calculate this with mod formula or is there any other methods available?
So simple. So helpful. Thank you.
You're welcome Big John.
Wonderfully explained, thank you!
You're welcome, Jack. Thank you.
how would you work out the average END time?
Really helpful!! Thank you so much!
My pleasure. Thank you Christian.
Genious! works perfetly!
Awesome! That is great to hear.
Thanks a lot, smart working is always required in buzy life, thanks for the video.
You're welcome. Thank you.
i cant get the custom thing to work i click it but it doesnt let me delete the ss
Thanks for the help!
You're welcome 👍
Thank you, can't express my gratitude....
You're welcome, Melbin.
WOW Thanks just solved my issue!!
Awesome! Good work Chiara.
Thank you for making this so simple.
You're very welcome Sophia.
thanks for this. I am working contact hours and it would not add-up correctly. your explanation was really easy to follow
You're welcome! Happy to help.
thank you very much, this video helped me very much
Great! My pleasure, Gabie.
Thank you was simple and to the point.
You're welcome. Thank you.
finally someone that knows how to fix my problem thank you
You're welcome 👍
Life saver Thanks man
No problem 👍
Thanks for the video. Any suggestions or other videos on totaling the hours in a 24 hour format when the hours overlap days. Example: 8 hour night shift 1800 hours to 0200 hours.
You're welcome. Sure, I have this video - ruclips.net/video/_Fg4gl4qvss/видео.html and this one - ruclips.net/video/_NMXXuNMy7A/видео.html
referred many videos yours is simple and effective.. good day
Excellent! Thank you, Harish.
THANK YOU VERY MUCH! That was so easy,
You're welcome
Thanks for the information, very helpfull.
You're welcome. Great to hear 😊
Thank you so much been searching this fornula
You're welcome 😊
Sir,how can we calculate that hours to day...means' work in day 7:30hours. So, we need convert in 7.5hours. Pls reply
Thank you so much!
You're welcome, Ivy.
Thank you!!!!!🙏🏿
You're welcome, Aminu.
Always Great ❤️ Mr.Alen
Thank you, Hazem.
how do i do this on my tablet?
Thank you very very much again! :) :)
You're very welcome!
How calculate follows in exel sheet.??
250hrs40min + 345hrs 23min + etc =???
Thank you!
You're welcome. Thank you.
Thank you so much it's so useful 😊
You're welcome 😊
You sir, are now on the Christmas card list!
Oh yeah!
It worked, thank you
Great to hear. You're welcome.
Excel android doesn't have custom formating option what to do?
Thanks very helpful
You're welcome 👍
Thank you 👍👍👍
No problem 👍
Thank you very much sir
You're welcome.
Thank you very much
You're welcome Kyaw Min Oo.
thanks for sharing
You're welcome, Jaimin.
Perfect 👍
Thanks 👍
Thanks help me alot🙏
Glad to hear that Sienna.
Many thanks
My pleasure, Zainab.
Danke für die Klärung 👍
You're welcome 😊
much appreciated
You're welcome, Abdelhaq.
i did the same thing on google sheets but when it exceeds in 24hrs, why does my result is -22:17 whereas the result should be 25:43?? hour:min
Will it work with the time format AM and PM. it does not seem so. Is there any other way?
Sure, Excel does understand those. I would check the formatting is correct and understood on your spreadsheet, but generally speaking, yes that works.
Very good!
Thank you, Fabricio.
Thank you
You're welcome 👍
Thanks I couldn't figure this out. I knew it was due to going over 24.
Glad I could help 👍
i watched youtube to get throu University, now at work I do the same. thnx bro
😊 Thanks, Alexandr.
How to calculate between two shift which end time exceed midnight 12
What I needed. A 3:39 video for one simple thing
👍
There's no such option in excel android os
Could you just sum total then time by 24 ex. =()*24
Is this formatting supposed to work in Google Sheets as well? Cause I tried and it doesn't :/ Anyone who have the same issue but in Google Sheets?
I'm not too familiar with Google Sheets so don't know.
Thanks. I've found it helpful for Google Sheets. but for some reason, after 36 hours I have the same issue that it resets... Pls advise. Thanks again
Great! I am not aware of this issue, but then I don't use Google Sheets.
@@Computergaga Any idea how I can make it?
I have been trying this on Google Sheets myself but can't seem to find a way how to do it. Any chance you could help?
Thanks...
You're welcome!
What is the formula for adding innings?
innings? As in Cricket?
What to do if I've followed this step and it still returns a faulty value? 😭 I have been searching everywhere but I only find this solution wherever I check and it's not helping me..
I get the result "08:15" to a simple Sum function that should return about 500 hours
thankss
You're very welcome, Jack.
u saved my life ...
Great to hear.
can be this done in Google spreadsheets?
i tried but not working
Ok, shame. I don't use Google Sheets. I'm sure they have another method.
What about formatting total hours when they run into the hundreds? Is it even possible
The same approach can handle the hundreds Sonny.
Thaaaaaaaaaaank u
You're welcome.
How to add time for example s/on and s/off.
12/02/2019 s/on 07:30 s/off 18:00
13/02/2019 s/on 01:30 s/off 08:00
14/02/2019 s/on 21:30 s/off 06:30 next day
Now I want to know from total hours continious from s/on to s/off from 12/02/2019 to 14/02/2019
It should be 50 hours now how to calculate in excel
Now the interesting part is when you count for example total aircraft flight hours, which is 50000. Then Excel gets mad and this type of format does not help :(
how to subtract (22:00 - 03:00) without showing am/pm
The am/pm sounds like formatting of the cell. Pop into the Format Cells dialog and change the format of the resulting cell there.
it seems that this does not work when you need to add hours which are in hundreds ... result is 0
same problem. Though mine are only 23 something.
Why does my total say #NAME?
#NAME? indicates a word Excel cannot recognise in the formula. Could me a mistype of a cell reference, function or a named range that doesn't exist.
AM USED IN 2024 TQ
You're welcome
3:30 minutes to say something that could be said in 20 seconds. Ah, I guess I understand the youtube (advertising revenue share) paradigm now... :-)
Twenty five words to say something that could have been said in two. You're welcome 😉
Total working hours is wrong or what 11.30 ???
You should have watched the video a little longer. It was then formatted to show the correct answer of 35:30. The 11:30 is the total hours after 24 which was initially returned, and then fixed.
@@Computergaga 👍
I’ve summed the hours but it gives me 0
Did you get a value before applying this technique? If you select the cells, do you get a sum in the bottom right corner of the screen.
Got same problem, man. Only thing I get is 0:00:00 even after following all that you showed. And I don't get anything in the bottom right corner except count. Please help....
I hate Zoomato that comes as
annoying food delivery advertising on your video.
I hate excel.
Nooooooo!
Sooo useful, thank you so much!
You're welcome, Mihael. Thank you.
Thank you
You're welcome, Mathish.
Thank you
You're welcome Tarek.