Welcome to Excel & Business Math video number 32.
In this video we're going to talk
about rounding time calculation to the nearest five minutes,
or 15 minutes, using this amazing rounding function
called MROUND.
Now, we'll see that MROUND can take a time
and round it to the nearest five minutes.
So 10:12:29, if we're using the rounding rule up or down,
that one goes down to the nearest five minutes--
because it's not half way or more.
10:12 and 30 seconds--
that's exactly 2 and 1/2 minutes between either 10:10 or 10:15.
So because this is exactly halfway between,
it gets rounded up to 10:15.
And of course 8:31 would be rounded to the nearest 5
minutes--
8:30.
And 7:43 would be rounded to 7:45.
Now, being able to use the MROUND function to round
for payroll is really important, because lots of businesses
don't let you write any particular time down.
It's always rounded to some number of minutes.
Now, we want to start out by going to the sheet MROUND,
and talking about how MROUND works.
Now I'm going to look at a pricing example first.
Here are some prices.
And in some businesses, they want
to round the prices to a certain amount.
Now, if we use the round function,
like we've done so many times--
I put in some number.
But when I get to number of digits,
that just looks to the number, and says
what digit to round to.
But that's not what we want to do here.
We want around each one of these prices to the nearest $0.50.
And when we say, round to the nearest $0.50,
the only possibilities become $0, $0.50, $1, $1.50, $2,
and so on.
So if I have $0.50, and I'm talking about $0 to $0.50,
everything from $0.24 down to $0 would be rounded down to $0.
And $0.25 all the way up to $0.50 would be $0.50.
So if I look at $104.24, well that's $0.24 on its way towards
the next $0.50.
So this would be rounded down to $104.
This is exactly $0.25, so this will be rounded up to $104.50.
And because this is $0.43, this will be $104.50.
And this will be $105.
We don't have to do it manually.
We can use MROUND, tab, number--
that's this right here--
comma.
And they call it the multiple.
Remember multiples-- $0, $0.50, $1, $1.50, $2.
So we're going to put in that multiple of $0.50.
And I'm going to lock it with the F4 key.
Close parentheses, Control Enter,
double click, and send it down.
And just as we did manually, this one is $104.
$0.25 goes up to the nearest $0.50.
$0.43 goes up to the nearest $0.50.
And $0.99 goes up to the next dollar.
Now, we're not limited to pennies.
We could round to the nearest $10--
equals MROUND.
Here's the number, comma.
There's the multiple.
And I'm going to lock it with the F4 key.
So that one will go down to $100.
Double click and send it down.
This one does, because it's exactly $0.01 below the middle.
That means $5 and above would go up.
$105 goes up to $110 and $106 goes up to $110.
Now let's see how to do this with time.
And actually, I'm going to delete this, and remove
the number formatting.
Because we need to learn how to enter a time here.
Because if I'm going to round these amounts to,
say, five minutes--
or, maybe later I want to change it to 15 minutes.
So we're going to enter it in as a time.
00 for hours, colon, 05 for minutes.
And guess what, I'm not even going to put the AM or PM in.
When I do that, it gets actually entered as military time.
But if you look up into the formula bar,
it will always list it, even as military time,
with the AM or PM.
And of course, up here it says 12:05 But that's five minutes.
Now I can use that as a formula input.
And I can use equals MROUND.
There's the number for our time value, to round to comma.
There's the multiple five minutes.
I'm going to hit the F4 key, close parentheses.
This one is right before 2 and 1/2 minutes.
So this will round down to 10:10.
This one is exactly halfway.
That means 2 and 1/2 minutes.
So it will round up to 10:15.
Control Enter, double click, and send it down.
That is so cool.
Now, I can change this up here.
00:15-- that's rounding to the nearest 15 minutes.
Control Enter, and that one changes.
The rest do not.
Control Z.
Now, this one down here, we want to round each one of these
to the nearest 10 minutes.
Well, this one is already rounded to the nearest 10
minutes.
So is this one.
But this one will go down to 10:40.
This one will go down to 6:30.
Equals MROUND.
I'm going to put in my time value comma--
there's the multiple.
F4 to lock it.
Control Enter, double click, and send it down.
And sure enough, these did not change.
They were already to the nearest 10 minutes.
And these went down.
All right, now let's go see how to do this in payroll.
I'm going to click on the sheet, Nearest Five Minutes.
Here is our employees.
Our wage per hour, time in, time out.
Now, I want to do a few things before I get
to the actual number of hours.
This way, we can kind of visually
see and prove to ourself that it's working.
Let's just do a straight formula.
Equals, later time or time out, minus the earlier time.
Notice we don't have any night shifts here.
It's all AM and PM.
So we're good to go with that formula.
This is not going to give us actual hours.
It will give us the decimal value,
and then the number formatting will be pulled.
Control Enter, double click, and send it down.
Now I've got to show you a good trick about time number
formatting.
With the column selected, I can use the keyboard Control 1
to open up Format Cells.
I want to go over to the Number tab.
In Time, we can choose.
And I want to choose to see in military time,
because I don't want to see the AM-PM.
I just want to see the hour and the minute.
Click OK.
So if I was rounding my hand to the nearest five minutes,
this would be 6:30.
If I was rounding this to the nearest five minutes,
this would be 7:10.
This one would be 7:30.
This one would be 7:50, and so on.
Now let's try MROUND.
I'm going to left arrow to get that time value, comma,
and there's the amount to round to.
Now, remember underneath there is a decimal.
That's only five minutes through a 24-hour day.
So if you look at the decimal, it's really small.
All right, I'm going to hit the F4 key to lock that.
Close parentheses, Control Enter,
double click, and send it down.
Now I want to use my trick here--
highlight, right click.
And on the mini toolbar I want a copy format only.
I'm going to click.
And now I should see exactly what I want--
each one of the values rounded to the nearest five minutes.
That is a handy trick to have when you're doing
Payroll Calculations in Excel.
Now, actually, if we wanted hours,
we would just take that and multiply it by 24.
But sometimes we don't want to have a 2-step process.
So I'm going to say MROUND, and write in the number argument.
I'm going to take time out minus time in, comma.
And the multiple, I'm going to click on that cell up there.
F4 to lock it.
Close parentheses, Control Enter,
double click, and send it down.
That's the decimal, and we don't want that.
So I'm going to click on the top cell.
F2-- I want the actual number of our sum,
[? and going to ?] multiply by 24.
Control Enter-- oh, that number formatting.
Control Shift, tilde grave accent,
double click, and send it down.
Now I want to come to the last cell, and hit F2.
I want to verify that the relative cell
references and the absolute cell reference
are pointing to the correct location.
And they are.
I'm going to click Escape.
Over here, F2.
That one's looking good.
Over here, F2, looking good.
Now we need to calculate gross pay based on number of hours.
So we have all three conditions for requiring round.
We're going to deal with money, so we
have to round to the penny.
We have extraneous decimals, and we're
going to have a total down here.
So, you ready?
Equals round hours worked times wage per hour,
as relative cell references, comma 2--
because we're rounding to the penny--
close parentheses.
Control Enter, double click, and send it down.
Go to the last cell, hit F2.
That is looking good.
Now I'm going to come down here--
Alt equals [? to ?] [? add. ?] I'm going to redirect,
and highlight just the cells that have the numbers I want.
And, Enter.
Look at that time number formatting.
How did that get there?
Up to the dropdown for general, or use your keyboard to apply
general Control Shift tilde.
So, we saw how to round to five minutes.
Well, what about 15 minutes?
It's a formula input.
If the business decided to change,
then we would simply say 00:15.
And when I hit Enter, instantly everything will update.
Now I'm going to Control Z on that--
Control Z.
Now let's look at our last example.
Let's go over to the sheet, Night Shift.
Now here is our employees.
Here's the wage.
Here's time in, time out.
And on this sheet some employees start in the AM
and get off in the PM.
But some employees, like Lydia Watts, got on at 5:40 PM
and left at 1:00 AM.
That means our standard formula of time out minus time in
will not work.
Now, last video, we saw how to do it with the IF function.
We saw how to do it with a logical function.
We also saw how to do it with the MOD function.
Now, the MOD function is the fastest and easiest
amongst the three methods.
All we have to do is take time out minus time in.
And it doesn't care if it started in the AM or PM.
And we simply comma.
And in the divisor, we divide that by 1.
MOD, because of the internal algorithm,
will always give us the correct decimal of hours worked.
Control Enter.
And we're going to keep it as a time number format,
and copy it down.
Now we have our formula for hours worked as a decimal,
for day shift or night shift.
But now we need to, for each one of these,
round to the nearest 5 minutes.
So this one will be 6:25.
This one, 7:20.
But this one's got to be rounded up to 6:25.
This one up to 5:20.
This one down to 5:35.
So how do we round to a specific amount?
We use MROUND.
Number-- that's simply the time calculation comma.
And the multiple-- there it is up
there, F4, close parentheses, Control Enter.
I actually want to double click and send this down.
And then I want to come over and highlight, right click.
And on the mini toolbar, click the Format Painter.
Click to apply the time number format.
And sure enough, look at that.
It did every single one correctly.
Now I'm going to come to the last cell
and hit F2, verifying the cell references, and Enter.
This one also, F2, looking good.
Now, time in hours--
we could simply take that rounded time value
as a decimal underneath and multiply it by 24 equal sign,
left arrow, times 24.
That 24, of course, is never going to change.
So we hard code it into our formula.
Control Enter.
I want to get rid of that time number formatting.
So I go up to Home, drop down for General, or Control
Shift, grave accent tilde.
Double click, and send it down.
And now we have time in hours.
Now we can do our gross pay.
And we have money, so we have to round.
We have extraneous decimals.
And we're going to add up all of these
down here, so we have to round.
Not using MROUND, using ROUND.
This rounds to a certain position.
So I'm going to take time in hours times wage per hour--
both as relative cell references-- comma.
And I round to the 2, close parentheses.
That will round it to the penny.
Control Enter, double click, and send it down.
Go to the last cell, hit F2.
Cell references looking good.
Now I come down here--
Alt equals redirect.
It's got the right gross pay numbers.
And, Enter.
$2,402.
All right, so there are two homework problems for you
to do.
And in this video we very importantly learn
about how to use MROUND to round to a certain amount.
For payroll, it could be some time amount-- five minutes,
15 minutes.
We also, at the beginning of the video
saw how to use MROUND for pricing and for time.
All right, if you like that video
be sure to click that thumbs-up.
Leave a comment in [INAUDIBLE],, because there's always
lots more videos to come from Excel Is Fun.
Including next video, number 33, we'll
talk about FICA and Medicare deductions from gross pay.
All right, we'll see you next video.