Patrick has a tricky overtime calculation. Episode 910 will show how to calculate overtime for people who worked over 80 hours and to show a blank for people who worked less than 80 hours.
This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Question sent in by Patrick, today.
Patrick has to calculate overtime.
He says any time that the hours go above 80, then he has to figure out how many minutes above 80 they worked, and figure out the overtime.
So the one thing I don't know, because I can't see Patrick's data, is if his hours are stored as a decimal number of hours or as a time.
Let's first assume that it is a decimal number of hours.
So, basically, what we want to do is take that time in C, minus 80 (=(C4-80)).
That's going to give us the number of hours.
And to get minutes then, we're going to multiply by 60.
So we'll copy that formula through, and you see that sometimes people worked less than 80 hours; sometimes they worked more than 80 hours.
Here, someone worked about three hours over-- so that's one 178.2 minutes.
Now, Patrick says, in the Overtime column, he wants to see-- if it's greater than 80-- he wants to see the overtime calculation; otherwise he wants to see a blank.
So we're going to use the IF function.
=IF (this number)>80 then here we put whatever the calculation happens to be.
So that's going to be the number of minutes, times the rate, times 1.5, divided by 60, or whatever you happen to pay; and then a comma-- the comma then says, "Well, this is what we're going to do if it's not greater than 80." So I'm just going to put in "" basically to force a blank to show up there.
So you see there's a formula, but no value.
But when we copy that formula down for the people that worked greater than 80 hours, we get that particular answer.
Now, that's assuming that the time is stored as a decimal.
If it's actually stored as time, then the calculation is going to be a little bit more difficult.
To figure out the number of hours over 80, we're going to have to subtract 3 days-- because 3 days is 72 hours-- and 8 hours.
So what we're going to do here is, say that time minus 3-- 3 being 3 24-hour periods or 72 hours-- the time of 8 comma 0 comma zero (=C6-3-TIME(8,0,0)).
Now, why don't we just put 80 in the time function?
Because it doesn't work.
That always ignores the pieces over the day and it just subtracts 8 hours.
And so what we're going to get there is, basically the number of hours expressed as time.
We went on to convert that to minutes.
I'm going to take that formula and multiply the whole thing times 24, and then times 60, and format it as a number.
=C6-3-TIME(8,0,0)*24*60.
Let's do a little test here-- there's our 178.2.
So we now successfully had converted that back to minutes.
And again, we still have to do a calculation over here in Column E, to figure out the overtime.
This time, since I already did the hard part, I'm going to =IF(this number)>0 then I'll put in my calculation-- so the number of minutes times this rate, times 1.5, divided by 60, and then a comma.
Again, if it's false, just "" to put a blank in there.
So we end up with various overtime calculations.
So a couple of things there.
It could be tough, Patrick, depending on whether you have real time or decimal hours, and then figure out what the minutes over are, and then finally an IF statement to figure out whether we get a blank or not.
Alright, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Question sent in by Patrick, today.
Patrick has to calculate overtime.
He says any time that the hours go above 80, then he has to figure out how many minutes above 80 they worked, and figure out the overtime.
So the one thing I don't know, because I can't see Patrick's data, is if his hours are stored as a decimal number of hours or as a time.
Let's first assume that it is a decimal number of hours.
So, basically, what we want to do is take that time in C, minus 80 (=(C4-80)).
That's going to give us the number of hours.
And to get minutes then, we're going to multiply by 60.
So we'll copy that formula through, and you see that sometimes people worked less than 80 hours; sometimes they worked more than 80 hours.
Here, someone worked about three hours over-- so that's one 178.2 minutes.
Now, Patrick says, in the Overtime column, he wants to see-- if it's greater than 80-- he wants to see the overtime calculation; otherwise he wants to see a blank.
So we're going to use the IF function.
=IF (this number)>80 then here we put whatever the calculation happens to be.
So that's going to be the number of minutes, times the rate, times 1.5, divided by 60, or whatever you happen to pay; and then a comma-- the comma then says, "Well, this is what we're going to do if it's not greater than 80." So I'm just going to put in "" basically to force a blank to show up there.
So you see there's a formula, but no value.
But when we copy that formula down for the people that worked greater than 80 hours, we get that particular answer.
Now, that's assuming that the time is stored as a decimal.
If it's actually stored as time, then the calculation is going to be a little bit more difficult.
To figure out the number of hours over 80, we're going to have to subtract 3 days-- because 3 days is 72 hours-- and 8 hours.
So what we're going to do here is, say that time minus 3-- 3 being 3 24-hour periods or 72 hours-- the time of 8 comma 0 comma zero (=C6-3-TIME(8,0,0)).
Now, why don't we just put 80 in the time function?
Because it doesn't work.
That always ignores the pieces over the day and it just subtracts 8 hours.
And so what we're going to get there is, basically the number of hours expressed as time.
We went on to convert that to minutes.
I'm going to take that formula and multiply the whole thing times 24, and then times 60, and format it as a number.
=C6-3-TIME(8,0,0)*24*60.
Let's do a little test here-- there's our 178.2.
So we now successfully had converted that back to minutes.
And again, we still have to do a calculation over here in Column E, to figure out the overtime.
This time, since I already did the hard part, I'm going to =IF(this number)>0 then I'll put in my calculation-- so the number of minutes times this rate, times 1.5, divided by 60, and then a comma.
Again, if it's false, just "" to put a blank in there.
So we end up with various overtime calculations.
So a couple of things there.
It could be tough, Patrick, depending on whether you have real time or decimal hours, and then figure out what the minutes over are, and then finally an IF statement to figure out whether we get a blank or not.
Alright, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
