Excel Magic Trick 718: Calculate Hours Worked (Day or Night Shift) & Subtract Lunch
Вставка
- Опубліковано 6 лип 2024
- Download Excel Start File people.highline.edu/mgirvin/Y...
We have In Time & Out Time for either Day or Night Shift and we must subtract 1 hour for weekday lunch and 1/2 hour for weekend lunch. See the formula to make this calculation that uses the IF, MOD and WEEKDAY functions. In case the In Time and Out Time are blank or have the word "OFF", we will use the ISNUMBER and SUMPRODUCT to show a zero.
Download file: people.highline.edu/mgirvin/ - Наука та технологія
Totally awesome formulas! Thanks!
Found it out myself, by pulling some kind of box. Gotta love the internet for having tutorials and guides like these! Thank you!
OMG! FINALLY! I watched 3 vids already only yours helped me!!! I just wanna cry!
Point to lower corner of highlight cell and hover cursor over little black box (fill handle) and when you see your cross hair cursor, click and drag to copy formula.
You are welcome!
That is so much easier than manually copying the formula over and over! Btw thanks for making this video, helped me alot!
Outstanding Video!! Thanks so much for the help and especially for the keyboard shortcuts.
Thanks for posting these videos - they're helpful :)
hey Mike. i love your videos because of your enthusiastic you are. i've learned a lot & still watching 1 by 1 to see if i can discover new things.
just want to point out 2 parts.
1) you already did the MOD * 24. so the part for lunch hours don't have to be in a fraction of 24 hours. it could be:
=MOD(B13-B12,1)*24-IF(WEEKDAY(B11,2)>5,0.5,1)
2) instead of using AND, you could use COUNT:
=IF(COUNT(B12:B13)=2,MOD(B13-B12,1)*24-IF(WEEKDAY(B11,2)>5,0.5,1),0)
The workbook might have the custom Number Format:
.000
Highlight cell and Ctrl + 1, then go to Number tab in Format Cells Dialog box, then click on Custom, then in the Type textbox, change it to:
0.000
Was a great video, thank you. So helpful!
Thanks very much! I couldn't get the night shift time difference to calculate until I found this.
Thank you. This really helped
EXCELLENT work
hello. thanks for the video. I was wondering if you could help me with a slight variation from this. Im a pilot and I have a digital logbook. I just need to be able to calculate how many hours have been flown in a 7, 30, 90, and 360 day period. So i already know how many hours we do on a day to day basis. But need to be able to incorporate the hours within the aforementioned day periods above. Would you be able to help with this?
IT WAS ALSO SO MUCH HELPFUL
great thanks a lot
you saved me heaps of time
Thousand likes🎉 Thank you!!!
You are welcome!!!!
The video was really helpful..
I am facing a problem in Converting date-time intervals into 3 shifts...
for ex-
"17.10.2018 07:00:00--17.10.2018 --15:00:00--", needs to be convert into 3 shifts (1st shift- 6:00 to 14:00, 2nd shift- 14:00 to 22:00, 3rd shift- 22:00 to 06:00) and the table should display 7 hrs in first shift and 1 hr in Second shift. Also sometime the diffrence is between 2 days
So Basically I want to divide days and hours into shifts.
thanks for sharing!!!!
hi, i have a problem with the if function on overtime calculation and I cannot find the tutorial. Hope you can help me.
how do I write this formula?
Example, if he works after 1900hr to 2300hr there will a $3.00 per hour, if after 2301hr to 0800hr there will be a $5.00 per hour.
What if I want to Round up to a 1/2 hour. Ex. arrive on-site at 12:52 pm and depart site (off-site) at 2:24 pm. I need the total time on-site calculation to round up to the nearest 1/2 hour as our charges are by each half hour.
I'd like to know as we have different breaks depending on hours worked for example 4 hours 0.25, 5 hours 0.25, 6 hours 0.5, 7 hours 0.75, 8 hours 1 and 9 hours 1.5 what would the formula be?
plus an addition to calculate each the total hours worked on a sunday by 1.5
Hello, thank you for the vide ! It is amazing on minute 9:35 you said that you could put off as a value. Could you please help me as I will also like to add holidays to be equal to 8H Working, and I will like to know which formula to use.
Thanks you soo much in advance!
oops
*how enthusiastic you are*
you're very welcome. keep up the great work. doubt i can finish watching!
do you have a formula/method for calculating multiple emloyees?
What if you have lunch on some weekdays and don't have lunch on other weekdays? What formulas can you use then?
Its Awesome, Is that possible to Add a video which calculate the Time difference between 2 data excluding weekends and Non Business Hours
This is awesome. I got What I want... thank you!
You're welcome!
Many thanks.
Excelisfun, need help with a formula calculating time n pay. Created a spred sheet to keep track of phones calls. I pay by minutes on phone calls. I keep track of times by getting a stamp of the time the call began n ended. Where am having an issue is adding the times n calculating the pay. For example, 1st call lasted 0:03:51; 2nd call lasted 0:39:43, 3rd call 1:54:09. The rate is .24 cents. The problem am having is the converting of the 1.54 to read in minutes. If I calculate it as is, 1.54.09 it does not give me the correct pay. If I calculate it at totals minutes spend, that is 153 minutes, I get a pay of $36.72...hope u have a video dealing with this issue.
First off thank you for sharing this with us. now, I was able to modify this to use it in my schedules where we take 30 min lunches no matter what. The big difference is that I would like to include that if the total time worked is under 4 hours, I would like it to leave it alone and not deduct 0.5/24 off of every shift, only to the shifts that work over 5 hours 7 days a week. can you do a video on that or send me a formula with those tricks?
Thank you for your time.
Is there any video on that? please can you give me the link?Thanks for help.
Can you share the formula for deducting 30 minutes
@Exelisfun...hi I`m just doing a basic hours worked spreadsheet, i wanna know if i can display the `extra hrs work in a day` WITHOUT having a `standard workday CELL`. but to add the standard hrs of a day into the formula itself and have it spite out remaing hrs in same CELL ??
How did you do what you did on 3:50 ? That simple way of copying the formula to other cells?
can you show me a formula which subtracts 1 hour's break after having worked for more than a set period of time. An example is working from 9 am - 10 pm which i will get an hour's break. Whereas when i work from 6 pm - 10 pm i will not get a break. Thank You
hi there ive been looking for a way to count hours worked in day and night shift. our night shifts start at 10:00pm and ends at 7:00am the next day. but sometimes we enter 8:00pm so that is still included in the day rate. how can i do this- please help
Great Explanation......In excel when i enter the value like 0.104 in the cell and when i press Enter and move to the one Cell down the" Zero" Disappears...can u guide me in this.
Thank you for the helpful videos, I have run into a dilemma . So far I have the formula to give me the total hours worked for each day of the week. What I need to include is if the total hours worked per shift is over 5 hours 7 days a week deduct 1/2 for lunch and over 8 hours deduct 1 hour lunch. Can you please help with the formula to add.
i liked ur vid but i was wondering if the total time for the day or the total time for a work pay period can be added as actual hours and mins not in decimals like 8.5 or 7.97, i dont want to do the caculation myself to fin out what 7.97 equals. i mean i know that 8.5 would be 8 hrs and 30 mins. can u help me out?
Hi MOD formula works great if there is no minutes to be calculated in the time cells but this formula doesn't work if I need to do e.g ( 3:30 AM - 9:45 PM = 6h :15M ) so is there any solution if I need to calculate the number of hours and minutes as well ??
and Thank you ur videos are really great
I have a problem with my spread sheet for my hours this is the formula but it doesn't give me the hours i worked for that night, can you please help me out.
6-:20 PM 7:15 AM #VALUE!
=IF(H16
Hi
Can I calculate my worked hours if I put hours in one cell? For exsample, if I write in one cell that I worked from 17-23:30. Is there any formula that can automatically calculate that it is 6.5 hours?
THANK YOU!!
Glad it helped! : ) Thanks for the Sub and Thumbs Up : )
thank you very much its helpful
You are welcome !!
How could I use this same formula but have it only calculate in the lunch if 8 or more hours are worked, if less than 8 hours then don't minus the lunch? I'm sure this is just another if statement but I'm a novice with excel.
Terrific.
I have a time problem needing to be answered. Let's say you list a bunch of start-end times (plane departing, plane landing). What I need to figure out is 10 hours of flying is exceeded for any 24 hour period. There will be several flight legs. Using your video, I can easily figure out the total flight time for each flight leg. The next step is to analyze any 24 hour period to see if 10 hours was exceeded. Any ideas how to do that?
i downloaded a excell sheet that is simple and i put the time in and out and i wanted to chage the total of the sunday shift because i get paid 150% on sunday so from 11.75$ i get paid like $17.56 ? so how can i change the payment for sunday i got it from www(.) calculatehours(.)com/excel-how-to/calculate-timesheets(.)html
I need help this has been driving me crazy im new to this too in all... but every time i go to add the hours in the total cell when i hit enter after putting formula in it always comes up #value! Please help me out as soon as possible
Multiply 24 at the end but i wont because of order of operations. What am i doing wrong how would Multiply in beginning so confused.
Never figured it out 😅 just need to put parenthesis around it.
Hello sir good day,
Hello Sir good day.
In out in out late time
9am 1pm 5pm 9pm ( )
How to calculate late timings because my employees 4 times in out a day.
Thanks...
I am sorry. i do not know how to use a Mac (zero).
Try this site:
mrexcel..com/forum
Wow, I did it right, but I don't understand it. If I were in payroll how can anyone expect me to remember that long formula? I think you lost me when you are doing the subtracting half hour for sat and sun and 1 hour for weekday. Could you make me understand why we did that part? Otherwise I get it. Thanks, Joe.
Hi, I've seen your videos and learned from then and I have a request or a question. I'm doing a weekly driving hours log and in it I have to also put in daily and weekly rest periods. I have the formula for daily rest but the problem occurs when there is a day missing in it. For example I finish work on Saturday morning 10:15 then I start at 00:30 next day (Sunday). Because I had started work the day before on Friday at 22:15, the start time 2215 and finish time 1015 would be in the Friday's column. The Saturday's column would be blank. So I wasn't sure how to calculate the rest period between 1015 Saturday and 0030 Sunday when there is a whole column missing. Obviously the formula is trying to take the blank space and gives me the rest period of 0:30 which is not true. There must be some kind of if formula that goes here. I hope you understand my question appreciate the time you take out to answer this.
Did you get your answer
@@saphead-invader3100 nope. It was way too complicated so I just ended up downloading an app where I can input two dates and times and it gives me the difference. So that works
What app did you use
Please gide me if worker 12 hours working in a company 10: 12 Am to 10:00 pm how to calculate plz guide me.
what formula are you gonna put if there is no break..if its less than 6 hours and there is no break..how are you gonna put it in?
so i dont have to use the mod formula because i do not have any shifts going into the next 24 hour period but i still need to subtract lunch. if its under 6 hours then i need to subtract 1/2 hour and if its over 6 hours then i need to subtract 1 hr. this is what i have so far can you please help me with the proper algorithm in order to subtract the proper lunch time in a shift. this is what i have so far, "=IF(AND(ISNUMBER(AG15),ISNUMBER(AH15)),AH15-AG15,0" thank you in advance for you help on this
Maybe a formula like this would work:
=IF(COUNT(TimeIN:TimeOUT)=2,(TimeOUT-TimeIN)*24-IF((TimeOUT-TimeIN)*24
So far everything worked until I got to my night shift value. I used the the MOD formula, where it takes my check out time (1:27 AM) subtracts it from my check in time (11:57 PM).
Now it does calculate it but it adds a value of (1.60) total hours worked, now my time tracking app states a value of (1.36). Not sure why it is adding an extra (0.24) to its value.
Why is this happening ?
If you are still having problems here is the answer: you need to have 3 set of formulas. your default would be. =NETWORKDAYS(E10,E11)-1+(MOD(E11,1)-MOD(E10,1)). where in E10 = Start Date and E11 = End Date. You may still add holidays by the way on the Networkdays. The first formula would work for almost all cases except when you start at a night shift then end at a morning shift the following day in which the start date hour is greater in number than the end shift, example, Start date 10/31/14 11:54 AM and End Date 11/1/14 2:30 PM, this is because you will have a 0 network days plus a negative mod. So from this point you will need to use the second formula, which is, =NETWORKDAYS(E10,E11)-1+(MOD(E11-E10,1)), this is applicable since mod will always be positive. Your last formula would be, an IF function. which is =IF(A1>0,A1,B1) where A1 = the 1st formula, B1 = the 2nd formula, which means you'll be using your 1st formula as long as it does not equals a negative number. Hope this answers your question.
Sir please make 24 hours shift roster for different employees. Just like a morning, afternoon, 1st night and 2nd night.
Amazing
Glad you like it, Shrinish!!! Thanks for the support with your comment, Thumbs Up and Sub : )
What if you don't want to subtract lunch
good
how to download it?
hi dear sir the video quality is not good i can't see it clearly
Need help fast!!! I need to design a spreadsheet that gives me the date, time clocked in and out, night differential, hours worked, extra hours and total.
Our night differential is from 6pm to 6am
I need to time to reflect to increments of 15 minutes i.e. .25 = .15, .50 = .30 and .75 = .45 minutes.
Can anyone help me. I have a deadline of Monday morning.Show more Show less
sound???
can you solve my ? its in my video and excel link is in video description . reply
Sir plz explain in mode function how to describe 1
Here is a video fro you; ua-cam.com/video/mfPBAxt0rpM/v-deo.html
Thats pretty good answer and its working but what if i write a 02135899088 in a cell and move one cell down the zero dissapears.
hi
I wanted to find out the number of hours a person worked and multiply the number of hours by $15.00.
A formula like this will work: (End Time - Start Time)*24*WagePerHour
@@excelisfun Thank-you! it worked!
@@rushbimbaugh Thanks for your support on each video that you watch with your comment and thumbs up and of course your Sub, Rush !!!!
Hellah confusing lost me after the 7 minute mark =/
Make video in Hindi
why cant you just do A1(Start) = 5:00 am A2(end time) = 6:00 pm A3(Break) = 0 A4( Hours worked) = (A2-A1)-A3
XD
Hindi is Indian language
You are welcome!