Welcome to Excel
Basics number 10. In this video, we got to talk
about date and time number formatting, formulas
for dates and times, functions and calculations too. Now this is a great follow-up
video on our last video where we talked all
about number formatting. Now in the last
video when I ended, I said this video was going
to be about finding functions, but no. This video is a
follow up, and it is going to be one amazing video. Now, this video really
should be called, what is under date and
time number formatting? And the reason why is when we
wipe away the date and time number formatting,
we're going to be amazed what's really only in the cell. And then, of course,
the second part is and how to make a
date and time formulas. Now we're going to see
all of these amazing date calculations, and then we'll
create this payroll time sheet where we have to calculate
hours worked from time in and time out, and then gross
pay deduction, and day's pay. Here's all of our
amazing topics. Let's go over to
the first sheet-- Date Number Formatting
and Calculation. I'm going to click
on that sheet. Now actually, I want to come up. My ribbon tabs are hidden, and
I'd like to unclasped them. One way to go to any ribbon
tab, and right click, and uncheck that. Or there is keyboard. Control F1, Control
F1 is a toggle. You don't need to
know that keyboard. All right, because I want
to have the number group open, and we need to talk
about date number formatting. Now, date number formatting. All dates that we enter
into an Excel cell, there are serial numbers,
which are the number of days since December 31, 1899. Well, let's just see
an example and then we'll be clear what that means. Now in this cell, I'm
going to type the date-- 10/28. And notice with dates, I'm
in the US-based system, so I enter the month
forward slash then the day. You can also do other
characters, like a dash, will work, but I'm going
to use forward slash. Then, forward slash,
and then the year. Now I'm going to
Control Enter, because I want to put that
thing in the cell and keep the cell selected. Well, we can see on the
surface of the spreadsheet, there's the date. I can also go up
to the formula bar and it looks like
exactly the same thing. Now, this is one of a
number formatting examples, like percentage we saw last
video, where you're not going to be able to go
up to the formula bar and actually see
what's in the cell. So I want to do this again, and
I'm going to click in cell B5. Before we type the date,
I haven't added any number formatting to that cell. So now when we look up
and the Number group, ah, General, of course, is what
is applied in our normal style by default in all cells,
but watch what happens when we actually type a date in. 10/28/2017. Last Now I haven't hit Enter
yet, I can see in gray up there it says General. But as soon as I
enter a date, Excel is programmed to automatically
add a date number formatting. Now, I want to Control
Enter and immediately we look up to the number
group and there it is. It put date number formatting. Now, I'm looking at the
surface of the spreadsheet, I'm looking up to the formula
bar, they look the same, but now let's do our trick. Let's apply the general
number formatting, which is our eraser. Any time we apply
General, it wipes away whatever number formatting
we have applied, and shows us exactly what's in the cell. So when I click
General, there it is. 43,036. That means that
10/28/2017 is exactly 43,036 days since
December 31, 1899. Now if you're counting
on your fingers, that means the first
date that Excel actually knows that exists is 1/1/1900. So if we're counting
since December 31, 1899. That's one, that's two. This day right there is 43,036. Now, why in the world
do they do that? It's just pure genius
that they thought this up. It's so we can do date math. Now to illustrate
date math, we're going to calculate the days past
due for a particular invoice. So we need to know
the invoice due date, and I'm just going to
type that date here. 9/12/2017, and Enter. Now, they were supposed to
pay the invoice on this day, but today, and you type
whatever date is today for you. I'm going to type today's
date for me, but guess what? I am not going to
type 10/28/2017. I'm going to use an amazing
keyboard Control semi-colon. Control semi-colon puts
today's date in hardcoded. That's the perfect
keyboard when you're entering a historical
transactional dates. Now, over in cell B16,
Control semi-colon, and there's 10/28/2017. Now I hit Enter. And now we're
trying to figure out what date math is--
two different dates. This date right here is
further through history, this is earlier in history. Now let's just
remind ourselves-- I'm going to highlight both
cells, go up to number, click the dropdown, and
there's our eraser general. Those are numbers underneath. Number of days since
December 31, 1899, well that's a bigger number,
that's a smaller number. So I can take a smaller
number and subtract it from the bigger number,
and I'll get the difference between the two numbers. That will tell me exactly how
many days late this invoice is. Now I'm going to Control
Z, because I would never wipe away the formatting. I actually want
to see the dates. But in my formula, I want
to look at those underlying numbers. Now remember, last
video we talked about it a number of different
times formulas don't see number formatting. So, we can simply equal sign,
and I'm going to up arrow to get the later date, then
minus, and then up arrow up arrow to get the earlier date. And when I Control Enter,
that's absolutely amazing. 46 days this invoices late. So last video we saw
for the first time, formulas never look at
number formatting when they make their calculation. This was beautifully
designed by the people who designed this spreadsheet. We enter the dates
just as regular dates. We see them on the surface
of the spreadsheet as dates, but underneath those
serial numbers, that's what the formula uses. That is pretty amazing. Now, here we entered
a hardcoded date. We actually might not want that. We actually might want to
build a spreadsheet solution with a formula, so when we open
this tomorrow and the next day, it will have whatever day it
is currently automatically. Then we build our formula
and the number of days will always change every
time we open our spreadsheet. All right, well,
we're going to get to see an actual date,
function, equals, and there's this great function
called TODAY-- returns the current date
formatted as a date. Now I'm going to hit Tab to
accept today from my dropdown. And one the screen tip. There's open parentheses,
close parentheses. It doesn't tell us to put
anything in as an argument, so I simply close parentheses. This is called an
argument list function. I simply enter today, open
parentheses, close parentheses, Control Enter, and
that's today's date. Now tomorrow when I open
this, this will say 10/29. The next day it will say 10/30. So now we can create
in our formula. When we're trying to figure out
the number of days between two dates, we take the equal
sign, the later date minus the earlier
date, and that's a formula to calculate
the number of days between two date. Control Enter. Now of course, we
got 46 in both cases. But tomorrow, this one will say
47, this one will still say 46. Now one of our
important formulas for dates is calculate
days between dates-- always the later date
minus the earlier date. And we saw that TODAY function. Now want to try something
just to hammer home that dates have date
number formatting and there's an
number underneath. I actually want to type
43,036 Control Enter. Now I want to add some
number formatting. Of course, I didn't add
any number formatting, so it's general, but I am
allowed to do the reverse. I can come up and
click the dropdown, and there is a Short Date. If I click on Short
Date, no problem. And look what happens? I'm still not allowed to see
that number of the formula bar, but we know that that
number is underneath, and it is very helpful when
making date calculations in Excel. All right, let's go
look at our next example of a date calculation. Sometimes in banking we
have short-term loans, and on our contractors,
a loan issue day. So this loan was
issued on 9/2/2017, but we only borrowed
the amount for 120 days. So, we need to put in
the days until loan due-- 120 and Enter. Now notice, we have
a date and a number, but under that date is a
number, so we're totally allowed to calculate the maturity date--
that means that the loan is due-- equal sign, up arrow up
arrow, I have a date. Underneath, there's a serial
number, plus up arrow, and I'm totally allowed
to add a number. So when I Enter, there
is the maturity date. So I borrowed it on 9/2/2017. I have to pay it back
on New Year's Eve. Another important
formula is that we use when we're dealing with
dates if we have a project start date, and a
project end date, and we need to include
the first date, well, simply subtracting
these two will not work. Up here for our invoice, almost
all contracts for invoice do not include the first day. So when we do our
subtraction, it's perfect. 46 does not include
the first day, but for a project
or any date formula where you have to
include the start date, then here's the formula. It's still equals,
and I'm an up arrow, that's the later date,
minus up arrow twice, that's the earlier date. What do we have to do? Add one. Any time the start date is
included, that's your formula. Enter, and I know
that this project is scheduled to take 76 days. Now, that's another
important formula, if you scroll over
here a little bit. Days between dates where
start date is included, later date minus the
earlier date plus one. Now, we want to see two
other formulas which involve date functions. Here we have an
invoice date and we need to calculate the due date. Now in invoicing, there
are hundreds of ways to calculate the due date. You have to read your contract. Now, this contract
says that the amount is due on the same
day two months ahead. Now, I can't simply add
60 days because each month has a different number of days. But in this case, I can
simply use a function. Equals sign-- I'm
going to type ed, and there's the e date
function-- returns the serial number
of the date that is the indicated
number of months before or after the start
date, Tab, start date. I simply click on the
start date, comma, and the month's argument. Now if I type 1, it
gives me 11/27/2017. If I type 2, it
gives me 12/27/2017. That's exactly two months ahead. But if I type a minus
number, like minus 2, this would give me a 8/27/2017. So our goal is to
jump two months ahead and land on the 27, so I EDATE,
start date 2, Control Enter. Oh, look at that. EDATE did not
automatically format this. No problem. For people that don't know
that dates are serial numbers, that doesn't make any sense. But for us, we know we simply go
up, and apply some date format. I'm going to click Short Date. And there it is-- 12/27/2017. Now I'm going to F2 and
just put a minus here, because sometimes you
need to go backwards. So if I put a
minus, there it is. 8/27/2017. One other thing, if
I type 4, it totally knows to jump into
the next year. All right, I'm going to
leave this F2 as a 2 here. Enter. All right our next
due date example-- this contract says
that the amount is due at the end of the month. Now, here's the invoice date. 2/3/2016. I need to jump to the
end of the current month. Well, there's a great
Excel date function. And since we're trying to
calculate end of the month, I'm going to take equals
eo end of, and there it is, highlighted in
blue, end of month. I used Tab. The same two arguments
as EDATE, start date, and number of months forwards,
backwards, or, in our case, we want the end of the
current month, February 2016. So for months, we
say 0, meaning I don't want to jump
backwards or forward, give me the current month. So I up arrow to get start date,
comma, 0, close parentheses, Control Enter. Whoa, look at that. It knows leap year. 2/29. if I change this
to 2015 and Enter, totally knows the end
of every single month. F2, of course if
we did 1, it would jump to the end of next
month, and sometimes, that's exactly what the
invoice contract says. That would be 3/31/2015. F2, we can put a minus there
if we wanted to jump backwards. I'm going to change
it back to 0 though, because for this invoice, we
need the end of the month. I better change that
date back to 16. Now our last example
for dates, here we have a whole list of
customers, their amount owed. Here's the invoice
due date and we need to calculate number of
days that that invoice is late. Well, I'm going to use the
TODAY function, because I want this to automatically
update when I open this tomorrow or the next day. So equals TODAY, I see it
highlighted in blue, Tab, close parentheses, Control Enter on
that argument list function, double click, and
send that down. Those are all 28, tomorrow
they'll all be at 29. Now I make my formula. Equal sign, and I'm
going to arrow over get the later date,
minus arrow arrow the earlier date, Control Enter
to put that formula in the cell and keep the cell selected. I point to that little
green box, the fill handle. And when I see my
crosshair or angry rabbit, double click and send it down. I go to the last cell, F2. That is looking beautiful. All right, those are a
lot of awesome formulas for date calculations
all dependant on the fact that we know what
number formatting is and that underneath there
is a serial number that represents the number of
days since December 31, 1899. Now, let's go over to the
sheet Time Number Formatting and calcs. Now just like date,
time number formatting has a number underneath
when we enter a time. Under the time number formatting
is this serial number, but the serial number will be
totally different than our date serial number. It will be a serial number
that represents the proportion of one 24-hour day. So when you think of
time serial number, think of proportion
of a 24-hour day. Now, let's look at an example. And just like dates-- and I didn't show you this. I showed you this
back in video 8. If we have a date-- 12/2/2017, no problem. I hit Enter, I see it's
properly aligned to the right. That's the default
behavior for numbers. But if we enter 12,
instead of a forward slash, we accidentally type a question
mar, 2/2017, Enter, of course it's going to be
aligned to the left, because we did not enter
it as a proper date. Excel thinks it is
text, not a number. Now the way to fix it is to
replace the question mark with a forward slash. Now, let's learn about
time, because time has an even more specific
way that you enter it into the cell for Excel to
interpret it as a number. If I want 8:00 AM, you got to
type the hour colon minutes. Now, you could
type colon seconds, but we don't have any
seconds right now, so I'm going to type AM. You're going to have
to type AM or PM. When I Control Enter,
immediately see a problem. As soon as it's
aligned to the left, I know that Excel
thinks it's text. And the reason why is you have
to enter your hour, minutes, and/or seconds, then a
space, then either AM or PM. Now when I Control
Enter, no problem. Excel thinks it's a number,
and that default alignment to the right tells
us that it is. So when you're entering
time, enter the hours, then the a colon, minutes. And then if you want,
enter colon and then seconds, then a
space, and AM or PM. Now, if you don't put the
AM or PM, that's fine, but then it will
be military time. So 1:00 o'clock 13:00. Now, we entered
this into the cell. Look up to the formula bar. Oh, look at that. Even though we didn't
enter the seconds, Excel entered seconds colon 0 0. Now, I immediately want to come
down here and type this again. 8:00 space AM, Control Enter. But this time, I'm looking
up to the formula bar and now I want to go up and,
use our well-known trick for determining what's
really in the cell. Up in the number
group, drop down, and we apply our eraser general. Now, I'm going to escape
here, because at this point in the class, we
have used general so often I have to teach
you the keyboard for this. To apply general number
formatting with the keyboard, Control Shift tilde. Now, you got to look
on your number pad to the left of the number 1
key on your standard keyboard is tilde or grave accent. Now depending on what keyboard
or what country you're in, that key may look different. So you may see the grave
accent or the tilde. But whatever's there, it's
Control Shift that key. So now I'm going to click
in the cell right here, and we're going
to apply general. Now right now, the drop
down for number formatting shows Custom, because I
didn't enter the seconds, but that really is a
custom time number format. As soon as we use our
keyboard Control Shift tilde, there is the proportion
of a 24-hour day, and there we can see up
in the number dropdown, general has been applied
using a keyboard. What? 0.333 is under 8:00 AM? Here's what Excel did. Any time you enter a time,
it's as if you entered 8:00 AM, but Excel said, uh-huh. I'm always going
to divide it by 24. Control Enter. Now actually, I
meant to hit Enter, because I want to do the
exact same formula again, equals I enter 8:00 AM. Excel divides it by
24, Control Enter. Now, that's a number right? I immediately want
to do our trick to see if Excel can apply
a time number format, and it actually
shows up as a time. And sure enough, if I come
up to the dropdown, Time, there it is. 0.333 is the proportion
of one 24-hour day. Proportion means whatever
hour, minute, second I enter, Excel takes
it and divides it by the total number of
hours per day, 24 hours. Now I'm going to Control 1 to
open up Format Cells, Time. You actually have some
different options. You can do military
time, you can do what we did,
which was to show the hours and minutes, and
there some other options also. Always, if we Control 1 to get
to Format Cells and number tab, this will give us many more
options than our dropdown. All right, I'm going
to click OK or Enter. Now, if we scroll down
here, we have a table that shows us some times,
how we might derive that proportion of the 24-hour
days, fractions, and then the actual number or equivalent. Yes, 12:00 AM is actually 0. 3:15, we could do
that calculation, and we would get that decimal. That's the proportion
of a 24-hour day. Now, I want to scroll down and
we get to see some time math. Hey, and just
before I completely scroll down from here,
notice what Excel did. It took the time we entered
and divided it by 24. That means if later we
want to make a calculation, like total hours worked, we
are going to enter our times, but Excel will divide
those times by 24. So when we make calculations and
we actually want total hours, since Excel divided
by 24, we have to remember to multiply
that decimal time by 24. Now, let's see our example. Wage per hour, $21.25. Time in, we'll enter
it as a time value. 8:00 space AM Enter. The person left at noon. 12:00 space PM and Enter. Now, when we go to calculate
total hours worked, that is the calculation--
later time minus earlier time. So that means it will
be similar to dates where we try to figure out
number of days between later and earlier time, but of course,
there a different serial number under these two. So I want to figure out how much
time has elapsed from the start time and the end time. So, you ready? Equals, up arrow--
that's the later time-- minus up arrow, up arrow-- that's the earlier time. Now, I'm going to hit Enter. And you've got to be
careful with time values. For a second, it looks
like we got 4 hours, but really there's a time number
format applied to that cell. If we went ahead and try to
calculate gross pay based on this time answer, let's
just see what happens. Equals up arrow-- that's the
amount of time worked, times, and I'm going to up arrow
to get the wage per hour. Now, I think I can
do that in my head. 4 times 21.25 is $85. When I hit Enter, the employee
is going to be so mad. $3.54-- that's not
correct at all. Here's why. When you make F2 a time
calculation-- remember, the formula is actually acting
on the underlying numbers-- Escape-- but there was time
number format on this cell. So whenever that
happens, we have to apply the general
format to get back to what's actually in the cell. I'm going to use the
keyboard Control Shift tilde. And sure enough, there is our
proportion of a 24-hour day. Now any time we're
doing payroll, and we're subtracting
time out and time in, to get that decimal
back up to actual hours, F2, since Excel divided
our times by 24, we have to multiply this by 24. Now if I do times
24, that will not work, because the math
order of operation will always calculate
multiplication before subtraction. So, we want to make
sure and put parentheses around the subtraction. This will tell Excel to
calculate the subtraction before the multiplication. Now when I hit Enter, the
employee is much happier. $85, and there are four hours. So that means a very
important formula in Excel for hours worked-- later time minus earlier
time in parentheses times 24. Now, let's look at
another example. Here's your homework tracker. You started it at 8:00 AM and
did the homework all the way to 11:00 AM. Well, we can calculate
the number of hours there. Equals, and I have to
remember to put the time calculation in parentheses. So, open parentheses, up arrow-- that's the later time--
minus up arrow, up arrow-- that's the earlier time-- close parentheses. And since Excel
divided these by 24, I need to bump it
back up times 24. When I hit Enter, you
studied 2.5 hours. Now, we can do the
next calculation. Equal sign, open parentheses,
end time minus start time, close parentheses times 24. And when I hit Enter, well,
I did something wrong there. Up arrow F2-- look at that. I accidentally
typed a minus sign, so I'm going to put my cursor
there, and backspace, Enter. Now I can add the two
actual hours studied. Equals up arrow, plus, and
them I'm going to up arrow to get the 2.5. When I hit Enter, I
added those two times. Now, we did a
homework tracker here, but this is very
common in payroll, you have time in/time out,
and then there some lunch, so they clock out, and then
they have time in/time out. Now if you have this situation,
notice that's the formula, and we had to multiply by
24, but really that was just a decimal inside of there. Then we did a
second calculation-- time minus time, that
was some decimal, and we multiplied it by 24. So we could actually add these
two decimals together first, and then multiply by 24. Not only that, but
because we probably don't want to do the
calculation especially for payroll in two
steps like that, let's see how to do
it all in one formula. Now remember, our goal
is to add these two and we could have
added this calculation plus this calculation, and
then multiplied it by 24. If that's the case, guess what? I'm going to use SUM function. And I type this one out,
Tab, because I see SUM. Now notice, SUM allows you
to enter some number comma, some other number comma. You could enter as
many different numbers as you want, but guess what? I want to add the difference
between these as a decimal plus the difference
between these as a decimal. So in number 1, I'm going to say
later time minus earlier time. Now that number 1
argument will calculate that as a single decimal. I'm going to type a comma. Number 2, I'm going
to do the second time calculation-- later
time minus earlier time, and that will work. Now if I close parentheses
and Enter this, and Control Shift tilde,
that's the total amount of time that you studied, but I need
to bump it up to actual hours. So F2, I can simply times 24. Control Enter, and that
will work-- oh, no it won't. Control Shift tilde. I love that keyboard
for apply general. All right, so you studied
a total of 3.75 hours. It seems like you
need to study more. The more you study, the more
fun you can have with Excel, of course. Now, if I look back
in the cell and hit F2, that's the biggest formula
that we've done in terms of we have a subtraction, and
then another subtraction inside a sum function, and
then we multiply it by 24. So I've got to show
you a great trick that will allow us to get Excel
to show us exactly how it calculated this step by step. I'm going to click
escape, I'm going to make sure and select
the cell with the formula. And we want to go up, not to
home, but over to the Formulas tab. I'm going to click, and over
here in Formula Auditing, we have this great
button Evaluate Formula. Now, your screen might
not be as wide as this, so you want to look
for that little f of x. And when you hover your cursor,
it will say Evaluate Formula. So I'm going to click. And what this does is it
shows us the entire formula in the Evaluate
Formula dialog box, and it's already highlighting
the very first thing that it wants to evaluate. Now, you can click the
Evaluate button here with your mouse or hit Enter. Now, watch what happens. I'm going to hit Enter. So it's looking inside the
cell for the start time, then it underlines
the next cell. If I hit Enter, now
it's underlining the first subtraction. Now, notice how cool this is. We get to see exactly how Excel
calculates this step by step, so now when I hit Enter, it
does the first subtraction. That's how much time you
studied before the break. Now look at this--
it's underlined. So I'm going to hit Enter,
that's the start time. Enter, that's the end time. Enter, and that will
do the subtraction. Now the SUM function
has our two decimals. It's totally allowed to add
to get the total decimal time. So when I hit Enter,
the sum got 0.15625. Now it does the multiplication,
so when I hit Enter, that's the final result. So using Evaluate Formula
is totally amazing to see that large
formula and how Excel calculated it step by step. Now I'm going to
click Close or Escape. That's pretty amazing. All right, let's look
at our last example and this is a common
one, a payroll example. Employee, their wage,
time in, time out. So hours worked-- I'm going to create one
calculation and copy it down. Equals open parentheses, arrow
key to get the later time-- that's the time out-- minus
arrow key, arrow key-- that's time in-- close
parentheses, times 24-- don't forget the times 24-- Control Enter. I'm going to double
click and send it down. Go to the last cell,
F2, that's looking good. Now, gross pay. I need to multiply wage times
hours worked, but notice we need to eventually
get exactly pennies, and we've seen this a
number of times already. When we have extraneous
decimals like this, we are required to use round. So equals ROUND Tab number. That's just some formula. I'm going to take hours
were times the wage comma, we're trying to get money, and
for payroll, it's to the penny. So I put a 2 to
round to the penny, close parentheses,
Control Enter, double click, and send it down. Now, I go to the last cell,
F2, that's looking good. It got wage and hours work. Escape. Now, tax deduction. Look t this. We need gross pay
times tax rate, and there it is, a decimal
past the penny position, and we're multiplying
these two things. So I already know I
have the potential for extraneous decimals
I don't want with money. So I want to use equals ROUND. Now, I'm going to left
arrow to get gross pay as a relative cell reference,
times up arrow, up arrow, up arrow, I need to lock
that all the way down, so hit the F4 key, number,
that's the formula comma. A number of digits-- we're talking
pennies here, so 2, close parentheses,
Control Enter, double click, and send it down. Go to the last cell, F2. I got a relative and an
absolute, it's looking good. Enter. Now the total day's pay. I'm going to increase the
column width just a little bit. Click in cell H44 equals the
gross pay minus deduction. Both is relative cell
references, Control Enter, double click and send it down. Go to the last cell, F2. And there we go. We have completed a
lot of calculations quickly by knowing how
to do our hours worked. Gross pay, we had
to round, because we had extraneous decimals. Deductions, same thing there,
and then a simple subtraction. All right. That was a lot of fun with date
and time number formatting, and a bunch of different
time and date calculations. We saw how to calculate a
complete payroll template. We talked about the
basics of time calculation when we're trying to get
number of hours, later time minus earlier time times 24. Don't forget-- time is
really a proportion of one 24-hour a day. Back over on Date Number
Format and calcs-- we did a whole
template for number of days late for each
one of the invoices. We saw the end of the month
and EDATE date functions to calculate due dates. We also saw the basics for
date calculations or date math where days between dates is
later date minus earlier date. And if we need to include the
start date, be sure to add one. And we started it all
off by, yes, indeed, date number formatting. There are serial numbers
underneath 1/1/1900 is 1 and 10/28/2017 is 43,036. Now, if you liked that video,
be sure to click that thumbs up, leave a comment, and some,
because there's always lots more videos to
come from ExcelIsFun. Now our next Excel
Basics video, we'll talk about how to search
for cell functions. All right, we'll
see you next video.
