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)
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
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.
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?
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.
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.
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
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,
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?
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?
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
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.🤷♀️
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
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
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???...
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
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
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 .
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.
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?
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")
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.
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.
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?
