Welcome to Excel and Business Math video number 29.
Hey, in this video we got to talk
about time number formatting and time calculations in Excel.
Now here are our topics.
The number one topic is we got to figure out
when we put 8:00 AM into a cell, what
is really underneath the time number formatting.
Once we figure that out, then we can create awesome payroll
templates like this where we actually have employees
put their time in, time out.
Then we can calculate these four formulas down here,
hours worked, gross pay, deduction, and the day's pay,
because this is for a single day, or also known as net pay.
Now let's go over to our first example, time number formatting
and calculations.
Now time number formatting.
Before we read this, let's just try
to enter a time into the cell B7.
Now, we enter time with the hours
then a colon, the minutes, then a colon, then the seconds.
And then we put a space and then either AM or PM.
So if we want to enter 8:00 AM, we enter the hours in first.
I'm going to put eight.
Then a colon.
As soon as you put a colon next to a number,
you are instructing Excel that this is not a normal number.
This is a time number.
Then, we put the minutes, in our case 0, 0.
If we wanted to put seconds, we would then type a colon 0, 0.
If we enter it right now, it would be military time
without AM or PM.
But if we want AM or PM, we have to put a space.
Now, I'm not going to put a space.
I'm going to type AM.
Now I have a time number, but no space before the AM.
So when I hit enter, we have to remember back
to video number four.
We actually saw this exact example back
in video number four.
As soon as we see default alignment to the left,
we know that Excel thinks this is text, not a number.
And time is supposed to be a number.
But no problem.
We made a mistake entering it.
So if I double click to put it back in edit mode,
with my cursor right between the zero and the A,
when I type a space, now when I hit enter,
default alignment to the right.
We know that Excel thinks this is a number.
And that's correct.
When we enter time, it is a number.
Now I want to do the same example again,
but I want you to notice up in the number group,
it says general.
As soon as we type a number and a colon,
Excel is programmed to apply a time number formatting.
So you ready?
8, colon, 0, 0.
And this time I am going to put a space.
Not only that, but I don't have to type capital AM or PM.
If I put lowercase, it'll know and make it automatically
uppercase.
Now if I look up into the number group,
you can see the general number for many
as soon as I control enter to enter this.
Up in the number group, we have custom.
That means a custom time number format has been applied.
Now, what do we know about number formatting
so far in this class?
We know that anytime we choose any one of these number
formattings, it is a facade.
That means that what we see on the surface of the spreadsheet
is not always what is underneath in the cell.
Now, time number formatting and date number formatting,
that we'll learn about next week,
are both number formattings that will not
allow us to look up to the formula bar
to see what the actual number is that sits in the cell.
You could see we entered 8:00 AM,
but it will always enter hour minutes, seconds.
So the way we're going to look underneath and see what
actually got entered in this cell
is by applying the general number formatting.
So you're going to come up to the dropdown
and select general.
Now, before I do that, I should have taught
you this a long time ago.
You got to find the tilde up or grave accent
key on your keyboard because if you
want to apply the general number formatting with a keyboard,
the keyboard shortcut is control, shift, tilde.
So that's what I'm going to use.
It's exactly the same as coming up, clicking general.
So you ready?
Control, shift, tilde, grave accent key.
And just like that, we've wiped away our time number
formatting.
Now anytime we apply general, it's like our eraser.
It'll remove whatever number formatting is on the surface
and show us actually what's in the cell.
We can look up to a number group and see that keyboard really
did apply the general number formatting.
Now, what is 0.33333?
How does that represent 8:00 AM?
Well, any time we enter time using 8, colon, minutes,
seconds, Excel will take whatever time we enter in
and think of it as an integer and divide it by 24.
Now, we only entered an hour here.
But behind the scenes, this is when Excel did.
It said, oh, you want 8:00 AM?
I'm going to divide whatever time you enter by 24
and return underneath the number formatting a decimal that
represents the proportion or the fraction of a 24 hour day.
Now in this cell, we entered 8:00 AM
and then wiped away the custom time number
formatting using general.
But let's do the reverse.
I want to prove this to myself.
8 divided by 24.
Well, that's like a fraction.
8 divided by 24 reduces to one third.
And as a decimal, it's 0.3333.
But can I apply time number formatting?
You betcha.
Number group dropdown.
And let's apply time.
And just like that, now it is displayed as 8:00 AM.
The reason this is important is because we can't do time math,
like have an employee enter a time in and time out.
If we don't know what actually is underneath in the cell,
we can't make a formula.
Now, we'll look at a formula in just a second,
but think about what that means?
If I enter 12:00 AM, well that's actually 0.
8:00 am, well 8 divided by 24 will give me
a decimal of 0.3333.
12:00 PM.
Since that's exactly half way through the day,
12 divided by 24 is the same as reduce
fraction 1/2 or decimal 0.5.
3:00 PM.
Well, that would be 12 hours plus three is 15 divided by 24.
That would give us the decimal 0.625.
If we enter minutes and seconds, whatever
fraction of an hour that is, Excel will take it,
total up all the hours, divide by 24 to give us a decimal.
These are the decimals that we're
going to have to consider when making formulas about time.
Now, it's no big deal.
What did Excel do?
Excel divided by 24.
So when we come down here and look
at our first example, a payroll example where
we have time in and time out and we
need to calculate total hours worked
and then multiply total hours worked by the employee's
hourly wage to get gross pay, we have
to remember that when we enter time, Excel divided it by 24.
Now all that means for us is we're
going to have to remember to bump it back up to actual hours
by multiplying by 24.
Now let's see what that means.
Let's enter time.
So this employee came in at 8:00 AM.
So 8 colon 00 space AM.
Enter.
And they left four hours later.
12 colon 00 space PM.
Now we can do this one in our head if we were doing payroll.
12 hours through the day minus 8 hours gives us four.
So four times 21.25.
And that would be our gross pay.
Well, let's just try and make our formula.
Now any time we do time and we're
trying to figure out how many hours between the time
in and time out.
This is a payroll example, but how long
a truck is out on a job, the time
they got there, the time they left.
Any time you have two times and you
want to figure out how many hours between, you
take the later time minus the earlier time.
So let's try that.
Equals sign, up arrow.
That's the later time.
Minus up arrow, up arrow.
That's the earlier time.
Now when I hit enter, remember we've seen this many times
in the class.
Those cell references will pull the number formatting.
So I'm going to he hit enter and it looks like 4:00 AM.
Well, the AM part is kind of annoying,
but it sort of looks like four hours.
Well let's just trying to multiply that times 21.25.
Equal sign, four hours times.
And then up arrow to get my 21.25.
When I hit enter, the employee is going to be mad.
$3.54.
It's not the correct--
$85.
It's because what happens?
When we use cell references in formulas,
they do not see number formatting.
They look at the underlying number.
Enter.
Let's use our little trick we just learned.
Either go up to the dropdown for general or use control,
shift, tilde.
There you go.
4 divided by 24.
That's the decimal.
That's the proportion of a 24 hour day.
So how do we get that decimal back up to actually four hours?
Now, I'm going to control, Z to bring back
the number formatting.
F2.
We multiply this times 24.
Remember for each one of those times, Excel divided by 24.
So anytime we're calculating total hours,
we have to multiply by 24 to bump it back up.
Now that won't work because multiplying
comes before subtracting.
And we need to subtract before we multiply.
So we're going to in parentheses subtract the two times
and then bump it back up to 24.
When I hit enter, uh-oh.
Now we have a number formatting problem.
It already has time number formatting.
We need to remove it using our dropdown in general or control,
shift, tilde, grave accent key.
And there we have it.
Four hours.
So 4 times 21.25 is properly 85.
And, of course, the employee is much happier with $85.
All right.
Let's go look at our second example here.
A homework tracker.
You set this up.
Start time, end time.
Because you know you always study in the morning.
Then you go off to school and work.
And then it's not until later in the evening that you study.
And we want a total number of hours studied for the day.
Well, we can do two different formulas
to calculate number of hours.
All right.
You ready?
The base formula for number of hours worked
is always going to be equals in parentheses
whatever the later time is.
And you subtract whatever the earlier time is.
You put that in parentheses.
And then you have to bump it back up by multiplying by 24.
Remember when we enter the time into Excel,
Excel divides by 24.
So that formula will work.
And enter.
So you worked 2.5 hours.
Down here, same formula.
Equals in parentheses later time minus earlier time.
Close parentheses.
And bump it back up.
Multiply by 24.
And enter.
So we have two individual calculations.
Now we need to add them.
I'm simply going t00 equals up arrow plus.
And then up arrow to get the earlier time.
And enter.
So the total is 3.75 hours.
Now later, tomorrow, you can come in.
You got up early.
When I enter 6:30 and enter, the formula updates.
Now I'm going to control Z to undo that.
Now we actually had to do one, two, three different formulas.
We can actually combine all of this
into a single cell formula.
Now we could repeat that whole thing, put a plus symbol,
and then repeat that whole thing.
And that would work.
But guess what.
We can use the sum function and do
these two individual calculations inside the sum
function, and then multiply that by 24.
So you ready?
An alternative to getting 3.75 for example is all equals--
that's the sum function.
It didn't guess right at all.
I'm going to do the first subtraction.
Later time minus earlier time.
Now we saw this earlier in the class.
The sum function and other aggregate functions
are awesome because you can put as many arguments
as you want in.
So that's the first calculation.
But now I type a comma.
Number two, I'm putting the second later time
minus earlier time calculation right
into the number two argument.
So guess what.
That will be the decimal, the proportion of a 24 hour day
that you worked in the evening.
That will be the proportion of a 24 hour day
you worked in the morning.
Sum function will add them.
And that decimal will represent the proportion of a 24 hour day
that you studied.
So now I simply multiply by 24.
And enter.
Now we know that that is number formatting.
And we don't want that there because what we want
is total number of hours.
So I'm either going to come up to the home ribbon,
tap dropdown down for general or use control, shift,
tilde, grave accent.
And there it is.
3.75.
Now you can do this in the two step method, especially
when you're learning.
Right?
Doing it two steps like this emphasizes
each individual calculation and helps us learn it.
But at some point, especially if you're
doing a timesheet for employees, and they had time in, time out,
lunch time in, time out, that formula, a single cell formula
might be more efficient.
All right.
Let's scroll down.
We have a payroll example here.
Now we have our employees, the wage, time in, time out.
Now we're going to need to do our hours worked formula.
Then we calculate gross pay.
That will be hours worked times wage.
Then from the gross pay, we have some tax deduction.
And actually, later in the week we'll
have more specific videos about different types of deductions.
But here we just have a single deduction.
And then we'll calculate the pay for the day or the net pay.
All right.
So hours worked.
The formula is always going to be equals in parentheses,
whatever the later time is minus whatever the earlier time is.
We've got to bump it back up, times 24.
Control, enter to put the formula in the cell
and keep the cell selected.
Double click and send it down.
Go to the last cell and hit F2.
I'm verifying that the two cell references are correct.
And enter.
By the way, when we type 24 into our hours worked formula,
we're not violating Excel's golden rule,
because that number is not going to change.
All right.
Now we can do our gross pay.
Notice we're going to multiply some number
with lots of extraneous decimals times money.
And for money, we have a requirement
that we only have pennies.
So we have to round.
Not only that, but we're going to use the formula
in subsequent formulas.
So we have to use round equals round.
In the number argument, I'm going
to say, hey, give me hours worked times,
and then arrow over to get our wage, comma.
We're dealing with pennies, so for number of digits,
we put A2.
That says round to the penny.
Those are both relative cell references.
Close parentheses.
Control, enter.
Double click and send it down.
Go to the last cell and hit F2 to verify.
Sure enough, cell references are looking good.
That also, of course, is the number
that's not going to change, so we hard
code it into our formula.
Now the deduction.
Ooh.
Look at that.
Lots of extraneous decimals.
We've talked about this so many times earlier in the class.
By now, we know we're dealing with money.
We have extraneous decimals.
We're required to round with money.
So we are going to use the round function.
Right in the number argument, I'm going to do left arrow.
That's the gross pay.
I want that as a relative cell reference.
So as I copy the formula down, I'll
get a different gross pay for each employee.
Times up arrow, up arrow.
I'm going to hit the F4 for key because that
is a tax rate that needs to be locked as we copy down.
Comma two because we're rounding to the penny.
Close parentheses.
Control, enter.
Double click and send it down.
Be sure to go to the last cell and hit F2.
I'm verifying that that has remained locked.
And sure enough, it has.
Now I can calculate-- for this example,
this is just the pay for the day.
This is also known as net pay.
Whatever your gross pay is minus all of your deductions--
in our case, we just have one--
that gives us net pay.
So equals the gross pay minus deductions.
Control, enter.
Double click and send it down.
Go to the last cell and hit F2.
That is looking awesome.
So we definitely applied our new knowledge
of time number formatting.
Any time we enter a time, we know Excel divides by 24.
So when we calculated total hours,
we are smart enough to know what's under the time number
formatting.
We make the subtraction, later time minus earlier time,
multiply by 24.
Than we do gross pay deductions and not pay.
All right.
So in this video, we saw our first example
of a payroll table.
Next video, we'll learn a lot more.
And we'll learn an important time calculation
for hours worked, later time minus earlier time times 24.
And we learned for the first time
about time and number formatting.
Now if you like that video, be sure to click that thumbs up,
leave a comment, and sub, because there's always
lots of videos to come from Excel Is Fun, including
next video number 30, we'll do a more comprehensive time machine
example for the whole week and then
gross pay, including overtime pay using the If function.
All right.
We'll see you next video.