Transcripts
1. Introduction: Welcome to this
class on dates and times in Microsoft Excel. My name is Julian and I've
been working with and teaching excel over the past ten
years as a digital marketer. Understanding how
Excel treats date and time values and how you
can format, analyze, and work with dates and times
in Excel is in my opinion, one of the most important
things to learn for anyone who wants
to work with Excel. This is because dates
and times are almost impossible to avoid
when working in Excel. Whether you're working with
sales data or a project plan, for example, you'll
frequently be working with dates or times
in one way or another. In this class, we'll cover the fundamentals of how Excel treats date
and time values, date and time formatting
approaches and best practices, popular date and time
functions and how to use them. An example, use cases and tips for working with dates
and times in Excel. At the end of this class, you'll be able to
work with dates and times in excel more efficiently, be less likely to make mistakes when working with dates
and times and excel. And be able to use date
and time functions in Excel for more
detailed analysis. Once you've finished the class, you'll be able to
test what you've learned with the class project. Thanks for enrolling in this
class and let's get started.
2. The Fundamentals of Dates & Times in Excel: In this lesson, we're going
to start off by looking at the fundamentals of working with dates and times in Excel. We've got a shortlist of example dates and
dates and times here. We've got first of January 1901st of January 1903 o'clock, and then further down we've got some more recent
dates and times say we've got 20th February
2022, for example. Now it's important to understand
that what we see here, obviously our dates and times. But beneath all of these values, excel has a serial number system that basically gives meaning
to all these dates and times and allows for calculations to be done when working with dates
and times in Excel. To make this all a
little bit clearer, what I'm gonna do is just
copy these dates and times, gonna copy them into
these cells here. Now, obviously the formatting of these dates and times is all in a typical date and time format. What I'm gonna do is
highlight the cells and then I'm going to change the
formatting too general. And you'll notice that
all of the dates and times have now
swapped to a number. Now the reason I included the
first of January and 1700s is because in Excel is serial number system
for dates and times. This actually represents
the first day from which all dates and times
are calculated from. So if we go to just say three
o'clock on the same day, we get 1.625 because it's not
move the whole day forward. We've just moved part
of a day forward. If we go to the 15th
of January, 1900, we've gone 14 days forward
from that first day. We've got 15 and then obviously, all of these days add up. So if we look at very
recent dates and times, we have much larger numbers. So here's a very
specific example. We've got the 20th
of February 2020 to ten minutes past
midday and 15 seconds, and that turns into a serial
number of 400461.5072, 12. Now, all of this can
seem a little bit confusing and abstract
to begin with, but it's worth being aware of this serial number system that Excel has underlying or
dates and times in Excel, because it's what
all calculations are based off of when you're
working with dates and times, and it's how functions work with dates and
times in Excel. So it's going to
become a lot more clearer as to why it's
important in future lessons. But just to get
started with, yeah, that's a very basic intro in how Excel works with
dates and times.
3. How to Format Dates & Times in Excel: In this lesson, we're gonna look at the different
ways in which you can format dates
and times in Excel. We've got date and time
of tenth of January, 202213 minutes and 42 seconds past ten o'clock in the evening, written out five times here. Now what we're gonna do is just copy this date and
time into these cells here. And we're going to format it five different ways just to
run for you a few examples. So I'm going to bring up the Format menu by using Control one, the
keyboard shortcut. And then I'm gonna go
to the date category. Just worth pointing out, you can change the location settings. Mine is currently
set to English, United Kingdom, but worth
being aware of that. I'm going to start off by just changing it to this
first one here. What you'll notice
is, is that the time has disappeared
and now we've just got tenth of January 2022. But it's important to note that the time actually still
remains in this cell. We do still have it here
in the formula bar. So this is actually still the same date and
time is this one. But because we've
changed the formatting to not show the time,
It's not there. Now I'm gonna do
another example. So we'll do this one here and we'll just go to the
date category again. We're gonna go with
this second example. Type here. Select, Okay? And this time we've got the
month written out as text, so it's January
rather than no one. Again, the actual underlying
value remains the same. So we've still got that
time data in there, but it's not showing due to the formatting
that we've chosen. I'll do one more in
the date category. We'll just select the one with dots rather than
forward slashes. Again, underlying value
remains the same, but the formatting
has changed so that we've now got the two dots. For these last two,
we're going to have a look at custom formatting. So I'll do control one
to bring up the menu. And then in custom formatting, what I'm gonna do
is I'm actually going to remove
the seconds here. We've got all the
same information showing but not seconds. You'll see that the
second is just disappear. But again, there's still, they still haven't
actually been deleted from the data itself. Then for this last one, I'm going to change the order of date and time and
for the formatting. So I'm going to cut the hours, minutes and seconds,
and paste them before. Select. Okay? And you'll notice
that the time is now at the beginning and the
date is at the end. So all of these look
quite different. But again, it's important
to note that they haven't actually changed in terms
of the underlying values. And we can show
that again by if, by changing the
format to general. So we get XL serial
number and you'll notice obviously that they're
all exactly the same. I'm just going to
go back to show those different
formatting examples. And yet That's just a
quick run through of how you can format dates
and times in Excel.
4. Date & Time Functions You Need to Know: In this lesson, we're
going to look at a range of date and
time functions. So we've got a date and
time written out here, the 20th of April
2021101342 seconds. The first few functions that
we're gonna run through are quite straightforward
and self-explanatory, so I'll go through
them fairly quickly. But what they're gonna do is extract different parts
of the date and time. We'll start off with second. And as the name suggests, this will extract the seconds, so it'll give us 42. Moving onto minutes. We can use this and
it'll extract 13. Then our this will give us
the 22 for ten o'clock. And then now moving
on to the date. The day function
returns the day, so that's the 20th. And then month for use
that will get the four. And then lastly, we have 2021. That's those functions.
Now the next few functions are slightly more complex. We've got weekday now what
this does is it basically assigns a number to the
different days of the week, which will then tell us what weekday a given day it was on. So let's just dive
into an example. So we select our date
just like before. And return type is an
optional field here, but it's worth moving onto it. So we know exactly what
numbering system is being used. So if we went with option one, week starts on a Sunday, so that would be assigned number one all the way
through to Saturday, which would be
assigned number seven. So Monday would be to
Tuesday would be three, etc. Let's go with option two. Week starts on a Monday
with this numbering system. Given the number one, all the way through to seven, which is Sunday, as you can see, there's various
different options. We could even start our weeks, week days with a 0 for Monday. But for the time being,
let's go with option two, hit enter and we
get to what that means is based on the numbering
system that we've chosen. This date is a Tuesday, the 20th of April 2021. That was not so Tuesday. Now weak num this
is quite similar. Basically what it
does, it will tell you which which number week a year, a week, a given date
falls in within the year. So it works in the same way
as the weekday function. So we select the date
and then we can specify the return type so we can
start our weeks on a Sunday. Let's go with two however. So basically the counting
of weeks starts on Mondays. Hit Enter once we've
selected two and we get 17. So what that means is is that based on the numbering
system that we've chosen, this date falls within the 17th, 17th week of the year. Now we're gonna look
at ISO week num now, ISO weeks, the certain
type of numbering system. They follow the same rule in that week starts
on a Monday with the first week of any year
has to include a Thursday. Now this type of
numbering system is commonly used in businesses
and organizations. So it's a useful function to
remember and be aware of. So we don't have to specify
a return type because the rules are already set
based on the ISO system. So we're just going to select the date and time
again and we get 16. So as you can see, although the rule is that week
starting on Monday, because we've got
that additional rule. The first week of the year
has to contain a Thursday. We actually get 16
rather than 17. So the last two functions
we're going to look at which are in this
separate section here, or what's called
volatile functions. And what that means is, is that the output of these functions can change every time the cell
is recalculated. Or if you were to
save your workbook, close it and reopen it, you get a new answer if
the value is changed. So we're gonna start off
with the now function. And this is quite simple. It basically just tells
us the exact date and time that we have right now. So once I hit Enter and you'll notice that I don't
reference any cells. I just have to close
the function off like this with an open
and close brackets. Once I hit Enter, I get
the 3rd of March, 202217, minutes and 50 seconds
past nine o'clock, which is exactly
what the time is now when I'm recording
this lesson. Today works in a
very similar way. All it does differently
is it doesn't give us the actual time, it just tells us the date. So once we hit enter
there and again, you don't reference any cells, you just close the close
the brackets off like that. Hit Enter and we'll
get 3rd of March 2022. And you can see how
the NOW function above recalculated again because it's a volatile function. I'm just going to
enter that again. You can see how the
seconds will change. So we went up three
seconds there on that last time I entered it. That's a range of date
and time functions. And in the next lessons we're
gonna be looking at some of the practical use cases and applications of
these functions.
5. Using the TEXT Function with Dates & Times: In this lesson, we're gonna take a quick look at
the text function. Now, whilst this isn't a
date or time function, you can use it when working
with dates and times. So it's another useful function to be aware of and to
have in your toolkit. We've got the date
and time here. 20th April 202113 minutes and 42 seconds past ten o'clock. Now, what we can do is use the text function to convert a value two texts in a
specific number format. So what I'm gonna do is I'm going to reference this
cell here, cell C3. And then I'm going to use
the formatting of ddd, which will actually
put this date into the day format in the
abbreviated date formats. So I'm gonna hit Enter
and we get Tuesday. And that's because
this day here, the 20th, April
2021 is a Tuesday. If I wanted this to show
the full word Tuesday, I can just add one more
d there and Excel will interpret that and provide us
with the full word Tuesday. Another thing I can do, I can put the month, so I can do MMM, which will give us the
abbreviated month, April. And again like before, I
can add one more in there and we get the full month of
April written out like that. And something else I
could do is year year, which will give us 21. I did three y's. I get the full year
of 2021 as well. We can use date
and time functions to do similar things and also play around
with the formatting. But it's worth being aware of
this text function as well. You may see it being used in some workbooks that you come
across for this purpose. Worth having it in your toolkit when working with dates
and times as well.
6. Example: Insights with Dates & Times: In this lesson, we're going
to look to apply a few of the functions and techniques that we've learned so far. We've got some sales data here. It's sales in dollars and it
runs for the whole of 2021. So first of January 2021, all the way down to the
31st of December 2021. And we're going to add in
some data to show the mumps, weeks, and days in
these columns here. So to start off with, we'll do the Month column, column C. And I'm going to use the reformatting techniques. I'm going to copy
all of these cells, then paste them here. Bring up the formatting
menu of control one, and go to Custom. And I want these these dates to show in the abbreviated
text month format. So Jan, FEB, morrow, etc. I'm going to highlight
there and just do MMM. You'll see we get the sample of Jan, which is what I want. Select, Okay, and then now at
a glance we can more easily see which month those
sales figures relate to. So we've got them written
out in text just like that. Next up we're going to
populate the week column. And to do that I'm
going to use the ISO week numb function. There we go. I said week num, and then I'm going to specify the date such as
come along here, hit Enter, and we get all of our ISO week nums now populated. Now you'll notice that the first three days
of the year of 2021, ISO week number 53, they actually correspond
to the a week that straddled two thousand and twenty and two thousand
and twenty one. And our first full
week in that year actually starts on
the 4th of January. Lastly, we're going
to enter the and the day what actual day of the week each of these
dates relates to. Because at the moment,
at first glance, we don't know if this is a Monday or
Tuesday or Wednesday, Thursday, Friday,
Saturday, or Sunday. That's going to
help us with that. So I'm going to use the
text function for this. So equals text. I'm going to specify
the date and I'm going to put it in the
abbreviated format. So DDD hit enter, and then we get all of our days written out in
that abbreviated format. So fry sat Sandman, etc. Now we've got this
extra information in. What we're gonna do is just
bringing a PivotTable. Pivot tables are a feature
in Excel which allow you to quickly analyze data
and they're really useful. And to bringing a
tip pivot table, we need to make sure
we're in the Insert tab. So this one here next
to the Home tab, make sure we're selected
anywhere in the data. Then I'm going to
select Pivot Table, existing worksheet, and then I'm going to specify the location. I'll just go with
cell H2, select. Okay? And then we get our pivot
table options coming up. You can see we've got the sales, month, weekday, and
date fields all there. What I'm gonna do is
find out which day of the week typically
sees the most sales. I'm going to drag this day field down
into the row section. And then the sales
I'm going to put into the value section. Then we have all
of our data here. I'm just going to right-click
Sort Largest to smallest. So now we can see that Monday by quite some way is the day that receives the most sales
across the whole of 2021. The lowest is actually Sunday. So this is something
that we're able to do because we've added in this information so we can
quickly and easily do that. Now we've got that
information in there. If we could remove day and we could add the week number in, for example, then we
get all of our data and data broken out by
the week numbers. Then likewise, we could
also do that with the month and have it quickly
showing just like this. So Jan, FEB, ma, et cetera, all the
way down there. In terms of just being able to quickly see more information
about the dates. As another really
useful thing of the applying these lessons
that we've learned so far. And then also in terms
of being able to quickly analyze it with pivot tables
in a different format. It's another benefit of having this knowledge of date and
time functions in Excel.
7. Example: Project Timings: In this lesson, we're gonna
look at a few functions and formulas for
calculating the number of days between two dates. We've got a project
start date or fictional project start date
of the fourth of April 2022. For our project, we've got a deadline of the
19th of August 2022. So there's a few months
between these two dates. But what we wanna do is
we want to calculate how many days there are
between these two dates. So what I'm gonna do in this
cell here, I'll see eight. I'm going to put equals
and then I'm going to select the deadline cell C5. Then from that I'm going to take away the
project start date, which is the fourth
of April 2022, hit enter and we get 137 days. So we can see that
between our two dates, we've got 137 days now, that's quite useful
for us to know, gives us an idea of how
much time we've got. But there's a function
called Network Days, which we're going to use next, that's even more useful. And what that does is it basically factors in weekends
and it deducts those from the number of working days that you've got to give you a more realistic picture of how much time you
would actually have. If we open up the
Network Days function. I'm going to select the
start date first this time. Then I'm going to
select the end date. Then we have both of
those two dates selected. Now, you've got this
optional field of holidays, which we will do in
the next example. But for now I'm gonna
hit Enter and we get a 100 working days now. So now we've factored in the weekends,
they've been taken out, so we get a more
realistic picture of how much working
time we've actually got between those two days. Now, obviously in
an organization, people are gonna take holidays. And for this next one that we're going to use
that holidays field. If I open up the
Network Days function, select the start date, 4th of April 2020 to date. Just like last time. This time, I'm going to do another comma to move on
to the holidays option. And what you have
to do is select the cells where your
holidays are listed, so you need them
listed out like this. I'm going to highlight
all of these cells. So cell B14 through
to B22 Hit Enter. We now get 91 days. So what we can see
is that we've got that same calculation
going on of the number of days
minus the weekends, but we've also factored
in these nine holidays. So we've gone from a 100 working
days to 91 working days. So that's three really
useful formulas and functions be aware
of in Excel when you're calculating how much time you might have between two dates wherever it's for
project management or just general organization. Another function
to be aware of is the network days
dot INT L function, which is effectively a
slightly more advanced version of the network days function. So I'm going to run
through an example now. And it works very similar. So we select the start date, we then select the end date. But we also have the option to specify the type of weekend
that we want to have. Hypothetically
speaking, let's say we have staff working
on Saturdays. And that means we can go with
the Sunday only weekend. As you can see, there's
various different options. You could have
weekends or on say, Monday and Tuesday or
Tuesday and Wednesday, which will all potentially impact the number of
working days you have depending on your project start date and
project finish date, the deadline, various
different options here, but I'm gonna go with the
Sunday only as an example. Like before, we can
enter the holidays. So I'm going to
enter the holidays, highlight all of those, and then hit Enter. And now we get 110, which is obviously different to the figure that we
had four of 91. It's also worth being aware of this network days
dot iNACOL function, which gives you slightly
more flexibility when calculating the number
of days between two dates.
8. Example: Tracking a Budget: In this lesson, we're gonna look at the Year frack function and we're going to apply
it to a specific example that looks at budget and budget pacing to give a better example of
how it can be used. What we have here is the
beginning of the year, which is the first
of January 2022. And what we want to do
is in this cell here, put yesterday, the
date for yesterday. Now, we could enter
this manually, but a quicker way to
have yesterday in there, assuming that you
want this to be a spreadsheet that
you're updating on a regular basis is to use the today function and then
to deduct one from it. So what this means is that every time we open
up this workbook, this cell will be
automatically updated with the correct date for
yesterday and we won't have to keep
updating it manually. Now we want to calculate
the percentage of the year complete. And for that we're going to
use the Year frack function. So it's quite simple. We need to select
the start date. So first of January 2022. Then we select the end date, which is the Yesterday
date that we just put in. Then next step we need
to select the basis. Otherwise, it will just
revert to the 0 option. And I recommend using one. And what this does is it calculates the year
fraction based on the actual number
of days divided by the actual number
of days in the year. There are other
options if you want to go 360 days in the year or 365. But by using this one factors in things like leap
years as well, go that close it off, hit Enter. And we can see that we're 16.2% of the way through the year. Now, next up we're
going to look at the advertising budget for 2022. We've got an advertising
budget of $80 thousand. Now, let's say we've
checked our expenditure for the up until the
end of yesterday and we've spent $17,542.34. Now to work out the
percentage of budget spent, this is just a simple division, so we're going to take that
spent figure and divide it by the advertising budget and
we get a figure of 21.9%. Instantly. You can
see that we're 16.2% of the way
through the year, but we've already spent 21.9%. So without doing any
further calculations, we can see that we are kind of pacing ahead of the budget. But what we can do is a
quick calculation here, just to give us an exact percentage of
how much a head we are. I'm going to select cell C8, then divide that by cell C4 for the percentage of year complete and
then do minus one. And then now this
shows us that were 35.7% ahead of budget based
on where we are in the year. Now, if for example, we'd only spent say, $70 thousand so far, you'll see that this figure
changes to minus 45.9%. So we're, we're quite
a lot below budget. So this simple tool now just allows you to keep
track of your budget. And yeah, it's a
great example of how the Year frack function in
Excel can be used and how you can find it useful when calculating different things to do with budgets and
pasting, for example.
9. Next Steps: Thanks for taking this class and I hope you found it useful. Let me know if you have any
questions and you can find the class project
instructions and falls below to test
what you've learned. You want to learn
more about Excel, check out my other
classes and follow me for updates on
my new classes. Once again, thanks for taking the class and I hope to
see you in the next one.
Excel Classes, Excel teacher
