Calculating total working hours using Excel - example & discussion
ฝัง
- เผยแพร่เมื่อ 31 ก.ค. 2024
- Learn how to calculate total working hours using Excel formulas.
Here an alternative video that shows simpler formula for timestamp data.
• How-to calculate time ...
👉👉👉 Check out my Excel course and become awesome:
=================================================
chandoo.org/wp/excel-school-p...
👉 ⏱ Here is another, much simpler formula for this problem - th-cam.com/video/2k1Jh1Nyw9k/w-d-xo.html
Thanks Sir 🙏😊
How do I add Total hours for a month
Thanks, very helpful after I watched many that didn't answer clearly enough.
Thank you now i just got to figure out how to apply this logic to my service level tracker
Thanks Mr.Chandoo, this was the solution i was looking for. I have also taken your vba classes and its content is put together very well..
how can i put
if someone works more than 4hrs , put 1:00
cuz i want it to minus from the total hrs they made
i just want to do like
if(C2-C1
it works for numbers
but not for time
thanks, its really very helpful.
Really Good tutorial 100% working ..this is what i am looking for ..1000000000000000000000000000000 *100000000000000000000 Likes
it was very useful in my career
thanks this was simple and easy to follow if you have a grasp of the basics of excel, however if start is like 5.45AM and finish is 6.45PM, none of the formulas seem to calculate the correct time? unless i'm doing something incorrectly, should the formulas work with the 45 minutes?
This was made more complicated than it needed to be
I have an error when writing the formula with if statment written like formula in the video . Please how could i fix # value error ?
Thank you sir, this is the answer to my problem. the if condition if(D5>C5,D5-C5, 1-C5+D5)
I just change the operation in the "value if false" if(D5>C5,D5-C5, 1+C5+D5)
Thanks for this video, now i have an idea for our ot calculation. Just one question. On the LUNCH TWIST. What if you have 2 criteria Threshold 6:00 and Minimum 5:00. How can i apply the formula with if? Hope you can do a video for it please thanks and more videos please.
Thanks for this video and i want to know formula for below concept
I want to calculate working hours between two date and times - if case is allign to me on 2-Mar'19 02:00:05 and I closed the case on 5-Mar'19 17:25:05 and i worked only between 9 am to 9 pm..... In this case i want to know time duration in hours but non working hours(9PM to 9AM) should be exclude
I could not understand how a person should start doing the whole thing from the new excel file (when you open it and it is empty).
How do we insert calendar and how do we insert the function, which will automatically show me that if I worked less than 8hrs the sum will be minus or vice versa - if I worked more than 8 hrs, the sum will be surplus. E.g. I worked 7 hrs, the sum will show me not 7 hrs, but -1. Or if I worked 8,5 hrs it will show me +0,5hr, so that I will observe if I worked overtime or I need to work more to fulfill 40hrs a week schedule.
Thanks
Superb work 🙏🙏🙏 Sir
Excellent Sir...
How do you work the rest periods between each day? And also if there is a day off, how do you tell excel to take the previous days end time and subtract that?
thank you for your tuto but iferror does'nt work i have excel 2010
Can you upload video with paper calculation for school exam sir. Thank you
when I subtract the time out from time in it shows the error..!
hi use full formula, is it possible can u guide me how to filter the over time hours, subtracting the normal working hours per day eg. clock in time 6am clock out is 9pm , (normal duty hrs is 8am to 5pm) hence morning 2 hrs and evening 4hrs is taken for over time
Hello. Can anyone teach me how to do the formula for this scenario.
For counting minutes/hours of late
If above 9am IN, counting the late minutes ex (9:26am IN) = late 26 mins
If below 9am IN, result will be zero
Another for overtime
If 6pm and start of counting of overtime is 7pm
Example: (6:45PM) = 0 minutes overtime
Example: (7:01PM) = 61 minutes overtime [6-7pm will be counted as 1 hr]
Thank you
Ur perfect
This is very helpful for me; however, the total hours for the week aren't adding up correctly for me. I used =SUM(G5:G10) and got 21:00 instead of 44:00. How did you get it to add the totals? Thnx.
Duh, found my problem. It was the format of the cells. I had h:mm instead of [h]:mm. I really need to watch my brackets. Thnx.
I have an error when writing the formula with if statment written like formula in the video . Please how could i fix # value error ?
Exactly what I needed but how do you factor in day off? Where the employee did NOT work?
Hey buddy, did you manage to find out how to factor days off?
if someone look in to this again this is how i do it.
=IF([2ndcell]>[1stcell],[2ndcell]-[1stcell],"OFF")
=IF(C6>B6,C6-B6,"OFF")
it dictates that if 2nd cell is > 1st cell Do 2nd cell - 1st cell then it will appear how many work hrs.
then the "OFF" shows after the formula is not followed,
How can I fill out my timesheet with 25and a half hours a week.
FOR THE LUNCH HOURS WORKED HOW CAN I REPLACE WITH IF IN PLACE OF MOD AS FOR MY SYSTEM MOD IS NOT WORKING (=MOD(D5-C5,1)-MOD(D5-C5,1)>$O$3)*($O$4) Kindly advise
Thank Sir, but can you please tell us what is manmonth
Hi , since you seem to be an Excel Guru, i've a question that you may be able to answer.
is there a formula that add to something while keeping a previous calculation?
Let me explain what i'm looking for:
Let's say i have in cell A1 a value "100,00 hr" & in cell A2 "1,50 hr" (using decimal values with suffix, not hours / time / duration)
(meaning 1.50 hr instead of 1:30:00)
I want A1 to sum the added value every time i add something in A2.
Let's say monday i add 1,50 hr on A2, i want A1 100,00 hr to be be "101,50 hr"
Tuesday i write 2,00 hr in cell A2, i want it to sum up in A1 ( "103,50 hr")
Wednesday i write 5,00 hr in cell A2, i want it to keep summing in A1 ( = 108,50 hr)
etc.
First of all, is it possible to do this? and if it is, what would be the formula?
To be even more spécific i'm gonna use it on Google Sheets, hopefully , if there's a formula on Excel, Google Sheets will deal with it.
Thanks a million.
I have been on a hunt to figure out how many hours an employee can work and what time to clock out if he clocked in at (clock in) what time would he need to clock out by without going over 60 hours. Any ideas?
nice info just try to make it to the point
Hello Sir good day.
In out in out
9am 1pm 5pm 9pm
Agar is timings main koi 2 minutes 5 minutes ya or bhi late ata ha tu us sorat main late timings kis tarhan nikale plz help me .
Actually you can just type =(D5-C5)*24
बहुत बढ़िया धन्यवाद साहेब
shukriya 🙏
iferror calculate working hour but when you sum give you 1056 instead 44
i am working on 2007 excel
Thanks
Thanks a lot for the information you granted us. but please give us chance to download the excel sheet you used
Sure, please see this: chandoo.org/wp/calculating-billys-total-working-hours/
Thanks alot for providing a practice workbook
Thanks!
The IFERROR does not calculate the minutes, just the hours, then how does one add the balance minutes, suppose you have start time 19:00, endtime 24:15 total time is 5 hours and 15 minutes, the IFERROR only shows 5 hours also for a ST of 19:00 ET of 01:25 the ISERROR formula results in 7.00 where all the other forumla (IF, MOD, < Formula) result in 6:25 - Can you show how to get the minutes here
It boggles my mind that excel doesn't have a built in formula yet for such mundane task.
when I using the IF formula then the result is showing this [Tuesday 9:00 PM 5:00 AM 16:00
] why?//
It is working... What I would want to happen is do not display the result if both the time in and time out haven't been entered yet... Is that possible???...
You can add IF formula to check the input values. For example = IF(and(in_time>0, out_time>0), show result,"") could work.
i want to calculate the hours between shift start time and shift end time
i want the formula for each shift calculated, i want it to deduct 30 min for every 6 hours worked AND if someone works over a 12 hour shift, then 1 hour gets deducted
when I apply this formulas I face value error, help me to solve this
Suppose normal working hours 8 and some employee not completed total 8 hours then how to highlight less hours of employee.
👍
I want to learn logical function
Here calculate automatically - th-cam.com/video/MZpH4Owo9yc/w-d-xo.html
The formulas are great and nice learning.. but i just wonder why billy created this problem in the first place. He could have simply wrote his hours himself.🤷♀️
Billy's favourite song... "Oops I did it again..."
@@chandoo_ 😄😄, good one sir, and frankly i didn't expect a direct response from you. Thank you for the attention.
I bet this guy works in a call centre !
how do you sum the hours now?
Just add up the hours. If you set the result to number format, It will show the value in days. So for example, 1.5 means 36 hours. If you want to see the result in hours, use the custom number format code [hh]:mm
Let me know if you have any questions.
If there is 2 in or 2 out time then what will be formula
See this video, which shows how to incorporate break time in to the calculation - th-cam.com/video/2k1Jh1Nyw9k/w-d-xo.html
Hlo, I need ur help in the same, I have calculated all the time difference correctly for one day and to calculate it for a month I multiplied it by 30 but it's not showing the correct no. Of hours. The sum was 11 hrs:40 min then it has to be calculated monthly it should be 350 hours but it's showing 14:00 .
Please help if u can
Hi Nitya... Just adjust the format of the cell to see total hours. Select the cell, go to format cells, set format as Custom and type the code [h]:mm to see total hours.
@@chandoo_ thank u soo much
@@chandoo_ sr the amt multiply by time calculations is not giving correct amt how to rectify it ?plz help me
Please answer as soon as possible sr
How to Display the "DD:Hrs:Mnts" of the total Hours by considering a Day is 8Hrs- Please do help..
In your situation, you need a formula to convert as Excel treats a day as 24 hours.
Assuming you have total time in a cell (should be 9.25 for 9 hours 15 minutes), you can use below formula
=QUOTIENT(B2,8)&":"&TEXT(MOD(B2,8)/24,"hh:mm")
@@chandoo_ OMG...Thats soo Kind Of You . 🤩🤩
Could you please explain how to get or do sum up of total hours of the week? Like your total hours sum up result is 44:00 hours. So did you do this please?
Just use SUM to add up the hours.
Then apply the number format code [hh]:mm to see total hours.
Hi in thise video there is no Excel file
how can i calculate 8 hours as 1 day if i work for 25 hours then how i calculate it as 4 days by formula
Assuming A1 has the hours, just use =ROUNDUP(a1/8,0)
@@chandoo_
Thanks
Am sorry, but I really don't get how you got the formulas in the first place? Since the formula was there already, and I don't know what to click. Thought I would be learning excel like a beginner . Your first video started out small and baby steps, then it seemed like you jumped with thiss video.
Sorry to hear that. I am making another video to explain solutions to this problem in a simple setting. Check it out in 2-3 weeks time.
DAY START END IF Formula MOD formula < formula IFERROR formula
Sunday 8:00 AM 3:00 PM 17:00 5:00 PM 5:00 PM 17
Monday 10:00 AM 2:00 PM 20:00 8:00 PM 8:00 PM 20
Tuesday 9:00 PM 5:00 AM 16:00 4:00 PM 4:00 PM 16
explain this?
Please see the download file or refer to this alternative video - th-cam.com/video/2k1Jh1Nyw9k/w-d-xo.html
Why can't you just put the time in a 24 hour format?
Hello , I have an error when writing the formula with if statment written like formula in the video . Please how could i fix # value error ?
Hi i need one more help can we calculate the time along with dates and excluding weekends for calculating SLA
Speak too long, just go to the point nest time
plz give me Exal faile
Video needs to be to the point.
Bolne ka tarika thik kro ki kuchh samajh m v aaye
Can you please get to the point immediately don’t try to speak with that sick sound and make the introduction so freaking annoying.