Learn Excel from MrExcel Episode 910 - Overtime Calculation

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Dec 22, 2008.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top