Calculating Sum of Time in Excel
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- In this video, you will learn how to calculate the sum of time in Excel. We have demonstrated the different approaches to how you can make sure that the calculation you do to perform the sum of time in Excel provides you accurate numbers. With this video, you will be able to calculate and provide accurate results of time without errors.
While doing calculations of time in excel it happens that if you do not select the right format it will simply display the remainder of the time and not the actual value unless you change the format to the specific type.
By the end of this video, you will learn what is that type that will help you to show the accurate values and do the right calculations while performing the sum of time.
Also, focus on the locale information which is demonstrated in this video. If the proper locale is not selected you may not see the desired format with which you are trying to achieve the results shown in this video. As some of the locales do not carry the type of format that is required to achieve this type of output, you will need to change it to get the desired results.
Do not forget to watch the next video that we are going to produce which will help you to calculate the maturity of a given product based on a certain date or the maturity of a contract in a similar manner.
We hope that you are enjoying learning Excel through this Excel learning series videos. We request you kindly share these videos with the people who desire to learn Excel from scratch subscribe to the channel and click on the bell icon to continue receiving notifications about the new videos as and when they are posted to keep up with the learning.
Download the Practice File using: drive.google.c...
Free Excel Data for Practice: onlineknowledg...
This applies to:
Excel 2007 and above
#excelLearningSeries #excel #excel2016 #excelDateFunction #excelShortcuts #exceltime
Brilliant! You have a great teaching style and get to the point really quickly. Very much appreciated.
So happy to hear that. Subscribe to our channel th-cam.com/users/hussainisyed if you haven't already and click on the bell icon to enable notifications for updates.
Best Regards,
Syed H | myknowledgeportal.com/
Thank you, great explanation! Helped a lot! Regards..
Thank you for the feedback.
Do subscribe to my TH-cam channel for regular updates.
Best Regards,
Syed H | TechnicalSupport.blog
I was scratching my head so hard I was giving myself a bald spot trying to figure out what I was doing wrong, what I was missing, and a quick search and I saw your video and you opened my eyes and I learned something today. Thank you so much for sharing your knowledge and making it simple enough to follow along.
I'm glad it helped and thank you for your feedback. If you haven't already, do subscribe to my channel and enable notifications by clicking on the bell icon to receive updates from my TH-cam channel.
Best Regards,
Syed H | MyKnowledgePortal.com
Sir, please give solution if sum of time exceeds 60 hours e.g. add below time
0:14:06
0:13:18
0:12:28
0:12:48
0:12:40
0:14:11
0:12:59
0:13:01
0:13:28
0:11:45
0:13:02
Sir pls give solution if time excess 60 hrs in sum
add below time
0:14:06
0:13:18
0:12:28
0:12:48
0:12:40
0:14:11
0:12:59
0:13:01
0:13:28
0:11:45
0:13:02
Hello sir, in my company I want to create over time sheet column like from, to,1.25 and 1.5.
If the employee put their over time start in column from and end time in column to. there is a condition if employee work between 6:30 p.m. to 11 p.m. the rate will be added in column 1.25. and if employee work between 11:00 p.m. to 6:00 a.m. overtime work add in 1.5 column. But problem is there is if employee work between 11:00 p.m. to 3:00 a.m. Excel is not dividing the column 1.25 and 1.5 there is only addition in 1.25 column..
Please help me..
Hello sir, in my company I want to create over time sheet column like from, to 1.25 and 1.5.
If the employee put their over time start in column from and end time in column there is a condition if employee work between 6:30 p.m. to 11 p.m. the rate will be added in column 1.25. and if employee work between 11:00 p.m. to 6:00 a.m. overtime work add in 1.5 column. But problem is there is if employee work between 11:00 p.m. to 3:00 a.m. Excel is not dividing the column 1.25 and 1.5 there is only addition in 1.25 column..
Please help me..
Thank you so much. I was searching this on internet for last 2 hours
buddy i have time stamps as 120:52:33 and 108:00:00. I changed their format but I am unable to subtract them and it gives error please help
Very good video on calculating time, but where to you get the sample you wanted us to work out?
It is there in description: bit.ly/2AM3y12
Thanks bro
Amazing sir
Problem is I am using web version as it has limitations I cant customise [h]:mm i can only select [h]:mm:ss if anyone knows how to get rid of the extended ss in this version do let me know would be of great help. Thank you.
hi sir. how are you. i need some help from you. let say the hours given is 10561:11 and another figure is 23.44. how to get total hours. hope to hear from you soon
Beautiful explanation Stay blessed and keep working
How to calculate hours above 10000? ..ex subtract 13250:52 (hh:mm) from 15000 hrs in excel
Sir, I have this format of time 1h 26m and i have to calculate average time(consider all 16 sessions). Please suggest the formula
thank you so much for this video!!! I tried for hours to figure out why the sum kept saying I only did 7 hours when I did 79.
Happy to hear that it helped you. Do subscribe to my channel and enable notifications for updates.
Best Regards,
Syed H | hmyknowledgeportal.com/
Very helpful! Took me a few tries to find this video; others were trying to sell some product, but I knew Excel must be able to do this :) Thank you!
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Thanks
Thank you Mr Hussaini - this was so helpful to me
I am glad I could be of service and thank you for such helpful words. Kindly subscribe to my channel if you haven't already for regular updates.
Best Regards,
Syed H | MyKnowledgePortal.com
Really great. Good explanation
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Very good explanation. Many thanks
Excellent instruction on what I was trying to do in excel.
How to substract 520:45 hours from 522:00 answer should be 1:15
Question what if on any day they have no time entered because they were off? How is that calculated?
How to compute work hours shortfall adjustment against leave?
Really it's work for me, thank you very much
I tried to convert 1636558 seconds to duration in HH:MM:SS format but this way didn't work. Can you please help me by telling me the right way
Supposing the number you have is in cell A2, use the following:
=A2/(60*60*24)
Now, change the format of the cell to hh:mm:ss
Let me know if you need anything else.
Agar equipment running time 24 hours hoga toh usko kese jode ??
I use excel on my iPad Pro, can you explain how to do this on iOS
Exactly what I needed. Thank you!!😊
thanks a million!
Thank you for the feedback Rukhayya, it helps in keeping me motivated. Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
This mystery was really bugging me, as to why the status bar was summed correctly but the spreadsheet was not. After several searches through video's and figuring out a part of the error for myself, finding this video cracked the case wide open for me. I would not have known to check this part of the format cells without it! Many thanks.
Thank you so much for this feedback. It makes me happy to see that my work is helpful in some way.
Do not forget to subscribe to my channel and enable notifications by clicking on the little Bell icon to get notified about new videos on my channel.
Have a blessed day ahead.
Best Regards,
Syed H | technicalsupport.blog
How to calculate Sum of Time Minus (-) Sum of Time
Thanks indeed Hussaini Sahib. Very Informative & helpful video.
Happy to hear such good words.
Do subscribe to the channel if you haven't already and enable notifications for new video updates.
Best Regards,
Syed H | technicalsupport.blog/
Thank you so much. This was making me crazy.
Thanks So much
Thank you for the helpful words.
Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed if you haven't already for regular updates.
Best Regards,
Syed H
But for me total comes 0:00:00?
Hello Ahmd,
Happy New Year, could you please share it as a Google Sheet so that we can look at the issue and help you accordingly.
Best Regards,
Syed H
@@Syedhussainipage - docs.google.com/spreadsheets/d/1mb3emEnTkwvUbPH9WgGKhqPiAKA-Qjo4qWpb2eawbb0/edit?usp=sharing
same as my file, were you able to get the correct one?
@@ahmdnaube3745 You need to change the format of the numbers to "duration" (Google Sheets Format > Number > Duration). For excel, change it to the format as demonstrated in the video. See the same I have done here (See highlighted columns) docs.google.com/spreadsheets/d/14NDHmLbOyS7nxkubfEaRiR3zVritPdABE7OICMobcAk/edit?usp=sharing
@@GadeValdez You need to change the format of the numbers to "duration" (Google Sheets Format > Number > Duration). For excel, change it to the format as demonstrated in the video. See the same I have done here (See highlighted columns) docs.google.com/spreadsheets/d/14NDHmLbOyS7nxkubfEaRiR3zVritPdABE7OICMobcAk/edit?usp=sharing
Excellent video...wish I had found it a couple of hours ago...I was going crazy with this same issue in Excel. Thanks for this information!!!
Happy to hear that it was helpful. Do subscribe to my channel if you haven't already for updates.
Best Regards,
Syed H | PuzzleCheats.com
I am not able to sum the time I'll show you as
10055:00:00 in one cell 4:10 in another cell while adding these two cells in one row it shows #value in the next cell in same row. The format of cells is also same. Help me plz
Hello there,
Thank you for your message.
Can you please share sample data so I can check it for you.
Use this form to submit your sample data file forms.gle/CxaWGeqdugMktWPx6
Best Regards,
Syed H
Thanks for this video. It is the best one on how to do this that I have seen. You explained everything very well. Now I just have to apply what you taught to Google Sheets. Hopefully I am able to do the same things in Google Sheets that you were able to do in Excel.
How do I add pure time together? For example
I ran for 1:03:26:06 today and 1:15:37:13 how do I get the sheet to add the time together and know that the seconds, min and hours are added correctly?
How I can calculate days with this hours?
I do have the same doubt
sorry for very delayed response. Please let me know if you are still looking for any help.
@@Syedhussainipage I got it sir.. If you have any simple method pls let us know..
Very good, it's a life saver for me, but... what if you DO want those seconds to be counted as well?
Thank you for the feedback Demens, it helps in keeping me motivated. Kindly subscribe to my TH-cam channel TH-cam.com/user/hussainisyed?sub_confirmation=1 for regular updates on new videos from me.
You may change the format to take the seonds into consideration.
Please let me know if you need any help.
Best Regards,
Syed H
Wow super 🔥
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
thank you
You're welcome
Well explained especially the amount of time over 24 hours. Many thanks for posting this
Thank you for the feedback Stephen, it helps in keeping me motivated. Kindly subscribe to my TH-cam channel TH-cam.com/user/hussainisyed?sub_confirmation=1 for regular updates on new videos from me.
Best Regards,
Syed H
Thank you very much
Thank you! You earned a subscribe. This is a pesky Excel issue, solved 🙂
Thank you for the feedback, it helps in keeping me motivated.
Best Regards,
Syed H
Really it helped me.
Thank you
God bless you
Thank you for the feedback and blessings Umesh, it helps in keeping me motivated.
Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Simple and easy instructions with a very functional work book. Thank you .
Thank you for the feedback Glenn, it helps in keeping me motivated. Kindly subscribe to my TH-cam channel TH-cam.com/user/hussainisyed?sub_confirmation=1 for regular updates on new videos from me.
Have a wonderful day ahead!
Best Regards,
Syed H
Thank you for this video, it is the 'easiest' to understand.
Thank you for the feedback Danilyn, it helps in keeping me motivated. Kindly subscribe to my TH-cam channel TH-cam.com/user/hussainisyed?sub_confirmation=1 for regular updates on new videos from me.
Best Regards,
Syed H
If in excel the time mentioned as 10.15 as 10 hours 15 minutes , how can I addition these ?
When a time is expressed as a decimal that includes hours, the hours remain the same upon conversion. Multiply the remaining decimal by 60 to determine the minutes. If that equation produces a decimal number, multiply the decimal by 60 to produce the seconds. For example, with a decimal number such as 9.47, do the following:
Subtract 9 for use as the hours. Multiply .47 by 60 to equal 28.2 minutes. Multiply .2 by 60 to equal 12 seconds. Thus, 9.47 equals 8 hours, 28 minutes and 12 seconds.
Excellent video. thank you so much
Bev P thank you for your feedback.
Do subscribe for updates and enable notifications for new video notifications.
Best regards,
Syed
Hi ,want to create a monthly report with specific task ,total output and total hours spent for each task .is there any easy way to do that..
I'll need some sample data with more information on what you need. I can then, hopefully, guide you accordingly.
Great explanation, straight to the topic, clearly explained and shown. Thank you very much.
Thank you for the helpful feedback and motivation. Make sure to subscribe to the channel and enable the notifications receive updates and continue learning.
Best Regards,
Syed H | TechnicalSupport.blog
5 hr 40 min ( me se hr&min ko remove kaise kare)
Very clear lesson. Good lecturer. Thanks.
Thank you Moshe. Do subscribe for regular updates.
Have a lovely day ahead.
Best Regards,
Syed H
Of course, I have subscribed and follow your videos. Thanks.
Your teaching earned my Sub :) and really liked the content though i didn't get what i was looking for but i'll not give up :D
Excellent teaching
thank youououououououoouououoououououoouououoououououoouououououououoou
So glad to see such a wonderful and motivational comment. Please do subscribe (th-cam.com/users/hussainisyed) if you haven't already to receive updates from my TH-cam Channel.
Best Regards,
Syed H | myknowledgeportal.com
Why am I getting 0:00:00? Great video to follow, just can’t understand why it will not SUM.
Can you please send me sample of data to look at?
@@Syedhussainipage I can tomorrow when I go back to work. It will add if I add each cell individually (=b2+b3+b4) but it won’t add with (=sum(b2:b4)) for some reason.
@@BMVLifestyle this is strange. I would love to look at some sample data, especially the set you are trying to sum.
Thank you hun. You save my day🙏
Thanks for what you have done, well do
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Excellent - best explanation I found
Thank you for the feedback. I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Thank you so much, I was looking for this for a long time and nothing worked for me except this,
thanks helpfull video
If total time is 8.40 hrs hw to convert it into numerical example : 40*1.66=66.4 (100/60=1.66) so 8:66 is Ans in excel ...?
how to subtract 456:40 - 345:20 in excel sheet
put them in separate cells and try subtracting one from the other. I got 102:20 as a result.
good videos sir
بارك الله فيك
Fantastic video. Helped me a great deal. Owe you one.
Glad to hear such motivational words. Do subscribe to the channel and enable notifications for regular updates.
Best Regards,
Syed H
Hi, thanks for explaining the custom formatting of the time. In my worksheet the sum of calculated hours is coming more than the actual value , i did check the formatting as well and still not able to figure out why actual sum is more than that total hours, eg: i was calculating total annual leave hours and for 5 annual leaves the sum should come to 50 but its coming up as 170, i am very confused , what wrong i am doing. pls help.
HELP please, Syed!
Thanks for you excellent video. I still have a problem with the time format if I want to calculate working hours for a month or a year. Can you help we finding the right format?
Best regards Peter
Hello Peter,
Can you share some sample data on my email hussaini.syed@gmail.com so that I can look at it?
Thank you but I am trying to add a group of cells that contain different lengths of time of videos that I have to review. So for example 4 videos I have in spreadsheet at Video1- is actually 1hr 30 mins & 10 secs long so I entered it into cell as 1:30:10, for argument sake lets say the other 3 videos are just 30 mins 10 sec's long, entered into cells as 30:10 each. So total length should read 3:00:40 but I cannot get the sum to work. It just gives me 0:00:00
It worked without any problem for me. [See drive.google.com/file/d/1DTnl9b2ORunqNHpZ5q7cNsL1vNmX3D-b/view?usp=sharing ] Can you please make sure all the cells are in same format HH:MM:SS and let me know if you are still facing the issue.
I am looking forward to hearing from you soon.
Regards,
Syed H
@@Syedhussainipage You are correct sir, thank you. I just tried it with a new clean spreadsheet. There must be something in the previous spread sheet that I was doing wrong. Thanks again!
@@joeypucks7250 I am so glad that it worked. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
It keeps showing 0
Still didn't work for me...please advise
Can you please add details of your problem and share some sample data to get assistance?
Great
thak you very much
Thank you so much!!!
What if I need to add larger amounts, such as 30 - 40 hours per entry? The ()*24 won’t help me.
Hello John,
Happy New Year!. If you could elaborate your question a bit more, I can get you a working solution.
I am looking forward to hearing from you soon.
Regards,
Syed H
So I have several clients that send payroll hours in hh:mm format, and my software only tracks it by decimal. Many times I need to enter this ‘on the fly’ over the phone, so I don’t have the time to refer to a conversion chart, or calculate mm/60 on every entry. Is there a formula or function that will convert hh:mm when hh is already greater than 24? Is the answer to divide the total of hh:mm by the number of total hours in my pay period, say 40/week or 80/ biweekly?
Sorry, I just read my reply, I meant to say would I have to multiply the total time worked by 40 to get total hours worked in a week as a decimal.
wow, very helpfull thankyou :D
Thank you for the helpful feedback and motivation. Make sure to subscribe to the channel and enable the notifications receive updates and continue learning.
Best Regards,
Syed H | myknowledgeportal.com/
Omg Thank you!!
Greetings Syed,
My Cells in question are = +K93 + L66
My Cells values in question are = 10:18:00 + 15:28:00
When I add the cell values on paper, they = 25:46:00
I have formatted the cell to Custom, [HH]:mm:ss = 49:46:00
I have formatted the cell to Custom, [H]:mm:ss = 49:46:00
I have formatted the cell to Custom, [H]:mm = 49:46:00
I have formatted the cell to Custom, HH:mm:ss = 01:46:00
I am trying to track accumulated hours (weeks) of Study.
Hello Dear,
Not sure what is going wrong at your end. I did the calculations using the values you've shared and got the result 25:46:00. Download this sheet to see the sum: drive.google.com/file/d/1nEdwmoyYvJfp_REXW2r4xzfLM-a9qgxG/view?usp=sharing Make sure you are following the format I have shared. Let me know if you still face the issue.
Best Regards,
Syed H | puzzlecheats.com/
Greetings! Is there a way to make a SUMMING CELL empty, UNTIL something is typed in cells that are going to be summed? It is so confusing on big time tables. Thank you very much!
Yes, just try
If(cell-to-be-summed="", "", Sum of time)
Replace placeholders accordingly.
Let me know how it goes. You may share the data sample in case you are facing difficulty for any assistance.
Regards,
Syed H
Thank You so Much !!!!!!!!!
Glad to hear that. If you haven't already, please subscribe to my channel and click the bell icon to receive notifications about new content from our channel.
Best Regards,
Syed H I MyKnowledgePortal.com
Will this work when calculating the total monthly hours with times which was calculated by using the IF formula?
If you could share some sample data, I can check it for you.
@@Syedhussainipage thank you very much. i managed
@@tangosamba7192 I am so glad to hear that. Do subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Very helpful, thank you.
Happy to hear that. Kindly subscribe to my channel if you haven't already to receive updates.
Best Regards,
Syed I PuzzleCheats.com
How to calculate by days across horizontal
Please share a sample data for reference to assist you.
Please tel mi how to hours covert in percentage
I'll do a file and share in the morning. It's too late at my location now. Thank you for your patience.
Thank you 🙏
This tutorial has saved me countless hours. So easy to follow and the explanations are very good. Many thanks for your tutorials.
Thank you for the feedback, it helps in keeping me motivated. I am so glad to hear that. Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
Thanks!
thanks
Hello actually i am tracking the cashiers transaction i want to know when he start first transaction and when he did the last transaction. In same date i can do it by MINIFS & MAXIF but some of our cashier closing their shift in next day midnight. ( examples they started today 10 PM and they will close tomorrow early morning 1 AM) in this case their in and out time repeats two time in same date. Is their any way to calculating their actual time to avoiding there the repeat time.
If you set the format to show as HH:MM, then there shall be no issues in capturing. I would recommend you to capture date in a separate column and time in a separate.
Hope this helps.
Best Regards,
Syed H
Thank you so much for this amazing video. Loved it 👍👍👍
Thank you for the feedback.
Do subscribe to my TH-cam channel for regular updates from my channel.
Best Regards,
Syed H | myknowledgeportal.com/
This should have more likes
Thank you James. Do subscribe for regular updates.
Have a lovely day ahead.
Best Regards,
Syed H
FINALLY!!! Took me years to find this simple solution to add up my work hours without giving me just the remainder. THANKS!!!!
Thank you for the feedback, it truly helps to keep motivated. I am so glad to hear that it worked for you. Please subscribe to my TH-cam channel th-cam.com/users/hussainisyed for regular updates.
Best Regards,
Syed H
i realize I am quite randomly asking but does anyone know of a good website to watch new tv shows online ?
@Anthony Edison lately I have been using Flixzone. You can find it by googling =)
@Thiago Reginald Definitely, been using Flixzone for years myself :)
@Thiago Reginald thank you, I went there and it seems to work =) I appreciate it!!
Not able to calculate total hours by days
Please let me know the steps you are performing, and your data sample so I can take a look and help you with that.
You can use this form to submit your data: forms.gle/M3s9fiSJjrANk6fv9