google sheets min This is a topic that many people are looking for. faithandmedia.org is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, faithandmedia.org would like to introduce to you Google Sheets – Timesheet, Overtime, Holiday Hours, Logical MAX & MIN Functions – YouTube. Following along are instructions in the video below:
“So in this video we re gonna review our timesheet and improve on what we we ve built so far if you haven t watched. The first video to create then i recommend you watch that first before you come back to this video. Because we ll be using a lot of existing code. And i m not gonna go over our existing formulas again to explain how it worked so if you don t understand what s happening here you should definitely watch the first video to understand what s happening in that video.
And then come back to this video. And it will make a lot more sense. So what we have here we have this basically weekly timesheet and that s in this particular tab for one person so far. We ve built our formula to calculate the number of hours worked so if we have a time in so let s say.
That. 9 00 am is timing and we have a time out. Which is gonna be whatever time that s gonna be let s. Say 4 00 pm.
It s gonna calculate the number of hours and then based on the rate here it s gonna multiply the number of hours by the rate to get us the total and this was our formula to calculate the number of hours. And this was rounded up with this m round function to basically half hour increments now. If you don t want to round that up which i probably don t i m gonna remove that so what i want to do i want to let me drag this down really quickly here. So that should take care of that i want to add some extra things here first of all i want to add an option for us to do overtime.
So we ll do something like ot multiplier and that will be our overtime multiplier and let s say for overtime. We pay about and a half of our regular rate. That s gonna be our overtime multiplier now in some cases. There could be some also holiday rates so we ll do a holiday multiplier whatever that multiplier is going to be so let s say for holidays.
We do double so it s gonna be two. I m gonna take that and do this formatting for all of this so i want to be able to add some of this stuff to my existing formulas to calculate the total now the number of hours is not really gonna be that different. It s still gonna be this many hours per day right so what we want to do we want to figure out if that s a holiday multiplier. How much they re getting paid and if it s overtime multiplier.
How much they re getting paid. Now we have to figure out how exactly this is gonna work because the way you apply overtime or a holiday multiplier. It s not always gonna be the same so in most cases. I suppose the way it works is that it goes by weekly hours.
So if you work over 40 hours per week. Then you get overtime and for the holidays you just get your a holiday multiplier now if you do overtime on holidays. I guess you might get the overtime multiplier on that too okay so that s gonna vary and in some cases. I guess if it s a holiday multiplier.
You don t get the overtime multiplier. So this is the problem with this like overtime and holiday multiplier calculations. Because it s not always gonna be the same. I guess in every case.
So you could think about overtime in some simple cases as if if you just work over eight hours on that particular day the rest of the hours is overtime..
And if it s not the rest of the hours is not overtime. So that s also another way to think about it so that would be actually the easy case if we just think if it s over eight hours per day the rest of the hours is gonna be overtime so first of all what i m gonna do i m gonna remove this total because that s really useless at this point. I m gonna add a few other columns. One is gonna be an option for us to check if that s an holiday rate or not and what i m gonna do i m simply gonna just make this a checkbox so i m just gonna highlight all of these i m gonna go on their data.
I ll do data validation and i ll use checkbox. I m just gonna do save and now we ll have a checkbox for us to just check the box. If it s supposed to be holiday rate or not for our overtime. We need to figure out how many hours are over time from those hours.
So let s start with a simple case. Let s say we re just gonna go with this idea that if we work over eight hours per day then we re gonna get overtime without multiplier for anything that s over eight hours so to do this we ll do our wage calculation. I guess let me add a couple of other timing timeouts here now what i need to do i need to so at this point. If this is nine hours this person should be paid eight hours regular time and one hour overtime.
So first of all how do we get that number of the overtime rate. So what i m going to do if i just do this hours minus. 8. Which is our regular 8 hour time.
We re just gonna get the difference. Which is 1 hours right so if i drag this down. This is gonna give me overtime 1. For this one it s gonna give me overtime 5 for this one it s gonna give me overtime negative.
2. Because there was no overtime here now we shouldn t get negative 2 over time there should be really zero hours overtime in this particular case. So one way to handle this you can say if this is greater than zero. Let s do that number otherwise.
Let s just of zero to do an if formula a better way to handle this is to use what i call a logical max and what that is is that if we use a max function. We ll provide this formula as one of our arguments and remember that formula is basically gonna give us like 1 for negative 5 0. Sometimes whatever that number is gonna be and i m gonna do comma and do a second argument. 0.
So basically i m letting this function choose between whatever is higher is the total from this function higher or the 0. And what s gonna happen as a result is that if that calculation was above 0. Is she s gonna keep that calculation but if it s negative it s gonna have 0 because. 0 is gonna be more than that negative number so this will just give us how many hours overtime.
That person worked and then we ll take that and multiply it by the rate. It should be multiplied by so if i multiply it by basically. It s gonna be their pay rate multiplied by the overtime multiplier and because these are the cells on top and i m gonna want to drag this down. I m gonna want to lock those two would have four and that s gonna give me this so now we want to add to this our regular hours worked.
Now we have to figure out what our regular hours are so i m gonna first of all let s leave this alone..
Let s figure that out on the side and we ll just add it to this now how do we know what s the regular hours worked well what i m going to do i m going to choose the minimum and this will be our logical minimum between this and eight. So if this number is higher than eight then we ll choose eight because that s the number of hours. They should get paid as a regular pay otherwise we ll just pick whatever is lower so if it s six. It s gonna just pick six.
Okay so let s just take a look so i m gonna do this and i m gonna drag this down see we get eight eight and this one is six because well it should have been six. So now we can take that and again multiplied by our hourly rate because this is regular rate so we re gonna do f4 to lock that and that should be our pay regular type so now if we take that copy it without the equal sign go back here and add to this that should be our total which over time and again. If you wanted to have like separate. Columns.
You could just put this formula in one column and say regular time or overtime. Actually the first one and this one will be a regular time in two separate columns. I m just gonna keep it in the same formula. So now i have my pay together with my overtime so if it goes over it s gonna calculate the overtime hours which is whatever extra with this one and a half rate otherwise it s gonna go with regular rate.
So finally i m gonna add this holiday rate to this whole picture. I m gonna use this check box and the way. We ll do it say if this you could say if that equals to true. But i m just gonna skip that so i m just gonna say if this because if it s checked it s gonna be true if it s unchecked.
It s false so we can just skip that and just say if that then that means it s gonna be checked if it s true so if it s true i want my rate to be the rate multiplied by the holiday multiplier comma otherwise just the rate closed parentheses hit enter and that s gonna be now 22 dollars an hour and if i check the box doubles it i uncheck it it goes back to this one so that s one way i guess to handle it another way to handle it if we don t want to go and change this entire formula because if i do this with this basically now i have to go back and change this entire b3 calculation in two different spots. So here and here so i would have to use that if function if two places. Which i really don t want to do so. Instead.
What i m gonna do i m just gonna take this whole thing put it in parenthesis to just group. It together. What i want to do is just to figure out do i use the multiplier or do i not use the holiday multiplier. So i ll say if this comma then we want to use the multiplier otherwise.
The multiplier is going to be one and if i enter. That s just gonna be one now and if i check this it s gonna be two and what i ll do i ll just take that formula now copy that and i suppose we should just lock this b5 this cell because we re gonna drag this down would have four before we copy this i m gonna copy that hit escape go back to this take this whole thing and multiply by that i don t really need this anymore i m gonna drag this down so basically if it s a holiday we get paid double so see this is 209 check the box for 18 uncheck 209 and our overtime is still gonna be the same way so the way this works now for holidays you still get paid double. And if you have overtime over that double you get paid another multiplier of 1 and a half. So that s good enough for me.
I m gonna just go here. And do a sum of all of this hit enter there s our total and if our multiplier for a holiday is one and a half. We can just go and change it here and we should just get that that s our one half multiplier and that should take care of this i m gonna undo a couple of steps here get back to this so this is if we go with this logic. That if you work over eight hours per day.
Then whatever goes over eight hours is overtime. Now let s try to change this in a way that if you work over 40 hours per week. That s where you get overtime. So i m gonna make a copy of this i ll do another one person so we don t need this anymore.
Because that doesn t help us and we re gonna start over this so this hours calculation is again that s accurate let s just keep adding something so we get over that 40 hours..
We went to 48 hours. So the way. We should be calculating this is that the extra 8 hours should be with our overtime rate. We need to be able to calculate how many hours out of this is the regular hours.
And how many hours is over time as we go through this so for example over here in the first line. This should not be calculating overtime. Because we haven t reached 40 hours then for these two combined together it should not be calculating overtime. Because it s still not 40 hours.
And it should keep going until at some point. It s gonna do our overtime hours. So let s just do something like regular hours and overtime hours for now. So we have two columns to work with what i m gonna try to do i m gonna create a sum function and i ll do a range so my range is gonna be let s take this the same thing and you ll see why i m doing this.
So. My range is basically just one cell and what i want to do i want to just lock the beginning of this range this first d8 so i ll do dollar dollar for the first d8. I m gonna leave the second one unlocked so if i d enter that will just give me nine because well that s nine. And if i keep dragging this down.
Basically. This will just give me our rolling hours total. So this is nine. Then this two combined is 22.
Then we keep going we have 28 right. And what happens. This is our formula in the first one when we drag it down. Because we didn t lock the second one it s gonna expand and include both of them.
And then it s gonna keep doing this to add them together hopefully that makes sense so this should help us to know that once we hit that forty hour mark like this that extra one hour at this point should be overtime. So what i can do now. I can take this and apply that same idea i applied before i m gonna take now this number and do 40 hours so if i do this like that basically what we re gonna get is negative negative negative 1 8 8 8 so if it s negative here we know that you should not be paid overtime. If it s a positive number.
We know that you should get paid overtime at this point. Now this one would be the overtime. You should be paid because well that s where like if i get back to that formula again see this 41 hours. So this point you get one hour overtime on this one day.
Because you went over that forty hour mark. So i m gonna bring that formula back. But if we go to the next day on this one you re not gonna get paid eight hours overtime. You re only gonna get paid.
However many hours you worked..
Which is the seven so the first thing. I m gonna do i m again gonna use that logical max thing first of all to get rid of all this negatives. So i m gonna say let s just grab either the max of this or the zero i m gonna close parentheses hit enter. I m going to drag this down so that should get rid of all the negatives now it s just gonna say zero for the months that were negative.
Because there should be no overtime in that area and the next one this is one hour overtime. That s correct and the other one going like here this should not be 8 hour overtime. It should be seven hour because we worked seven hours the next day that s the seven hours. We need and then the other day after that again it should not be eight hour overtime.
It should be zero. Because well there are no hours at all what we re gonna do we re gonna choose the minimum between this and this so if we look between this and this the minimum is 1. And if we look between this and this we should be choosing not 8. But seven because minimum is 7.
And between 8 and 0. Which is the minimum that s going to be the zero. So if i just take this whole thing and put it inside of a min function. And i m gonna say choose either that comma or this i m gonna close parenthesis hit enter and i m gonna drag this down and this should give us how many hours overtime they re gonna get paid now this was not our regular hours this was really our overtime.
So i m gonna cut that formula move it over here this will be calculating our overtime hours and now we need to figure out how we re gonna do our regular hours. So now to calculate our regular hours. It s gonna be really easy. We really don t have much to do we ll just take our hours or overtime.
And just drag this down and that should take care of it we have our overtime hours this should be our regular hours whatever the rest is and we have our hours. We might want to do the sum of these two so that and this that s good so now we have to calculate our totals. So our regular hours are here our overtime hours are here that part is kind of gonna be easy right so we re going to take this multiply it by our hourly rate and we ll lock the rate with at 4 plus. We ll take our overtime hours and multiply it by the rate which we should lock.
We have 4 and then we ll take that and also multiply it by our overtime multiplier and we ll lock that with f 4. As well hit enter so that s our totals. So for example here it s gonna take zero times. The regular rate.
Which is this zero and then it s going to calculate all of this with our overtime rate on this one it s gonna take the first 12 hours multiplied by the regular rate and then it s gonna take the one hour and multiply it by that multiplier and the rate to get us our overtime rate and then its overtime rate from here and on so finally to add that holiday to this we ll just take this whole thing in parentheses. And we ll say let s multiply that by that f function again and if this is checked then that means we want holiday rate. So we re gonna do our holiday multiplier and we ll lock that because we re going to want to drag that down comma otherwise our multiplier is going to be 1. We ll close this and drag this down and we ll sum it up and if we decide this was supposed to be a holiday rate check the box there it is and see two holidays in this case.
And that should be our final time sheet with all this extra things on it and then if we just clean this up it will look nice and will be bunch of zeros and we can start filling this up thanks for watching please subscribe and i ll see in the next video. ” ..
Thank you for watching all the articles on the topic Google Sheets – Timesheet, Overtime, Holiday Hours, Logical MAX & MIN Functions – YouTube. All shares of faithandmedia.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
First Part: https://www.youtube.com/watch?v=Xl646S9mQ20
In this tutorial we ll add overtime u0026 holiday hours to our timesheet template in Google Sheets. We ll also introduce the idea of logical MAX u0026 MIN.
#googlesheets #timesheet #overtime #formulas
Google Sheets, Timesheet, Overtime, Holiday, Hours