Hello again and welcome back to our course on Excel 2016 Advanced.
In this section we’re going to start to look in some detail at dates and times and
at Date and Time Functions in Excel 2016.
Now of course the use of dates and times is absolutely fundamental in Excel and yet for
many people there are aspects of the use of dates and times that are completely baffling
and cause them all sorts of problems.
So what I’m going to do starting in this section is to explain in some detail how dates
and times work and then to look at some of the main functions that you’re going to
need to be able to be pretty familiar with in order to handle any kind of Excel application
that requires the use of dates and times.
Now before we begin let’s start with one of the things that I think is at the root
of many people’s problems with dates and times and that is that ideally you will have
the Date and Time settings on your device set correctly before you even start to use
dates and times in Excel.
Now I don’t mean by that that you’ve got the right date and time set but I meant that
you’ve got the format set.
And although there are some relatively minor differences in how these default settings
are handled in the versions of Windows on which Excel 2016 will run, fundamentally the
approach is the same.
You need to start by going to Control Panel and you need to look at Region.
Click on Region and you get this little dialog.
As I pointed out to you earlier in the course my default language is set to English United
States.
And in this dropdown here my selection Match Windows Display Language means that my regional
settings generally will match English United States.
Or the dropdown here where I could choose a different language to determine these settings.
So for example, if I went for English United Kingdom my short date format would be day,
month, year.
Whereas if I say match Windows Display Language it is of course month, day, year.
Now you’re probably familiar with what the little D’s and big M’s and so on mean
but I’ll come back to those in just a moment.
But the important thing to realize is that these are my default settings.
And whilst I’m working on Excel if I don’t change anything else, if I just insert dates
and so on these determine how they will appear, same is true of time as well.
Now it may be that in a particular workbook I need some different settings.
It may be that even in individual cells I need different settings and that’s fine.
I’m sure you know how to change the date or time format in a cell.
But if you find that you always have to change the short date format and you think, “Why
can’t it be the way I want it to be?”
You almost certainly need to change it in your default settings here.
Changing the short date format, for example, is really easy.
You can change to one of the standard ones just by clicking there and choosing one of
those standard short date formats.
Similarly, there are alternatives for long date formats, etcetera.
So there are the defaults and very importantly down here we have additional settings.
And this gives us settings for time and date.
Now starting with the date ones, there’s an example, short date and long date.
There are my date formats.
Not only can I choose from that list of standard defaults but I can actually click in here
and edit this if I want to to any combination of the appropriate symbols.
And immediately below that there is an explanation of what the notation means.
So D or DD is the day number, DDD or DDDD is the day of the week.
Note you have the short form and the long form.
M is the month, Y is the year.
Single M will give you a numeric month, long string of M’s will give you the words for
the month.
And also importantly under there there is a standard cutoff year which determines which
century is assumed if you enter a two digit year.
So if I set a year number of 67 it will be assumed that it’s 1967.
Now again you can change that cutoff year if you want to.
And also there you can change the first day of the week.
Mine is currently set to Sunday.
Note the Reset button at the bottom.
Now you have a similar set of options for time and an explanation of what those abbreviations
symbols, what the notation means.
The first thing you need to do when you’re dealing with dates and times is to make sure
that you know how these are set and to make sure that you’re happy with how they’re
set.
And the second thing is if when you’re working on Excel you’re constantly annoyed by the
fact that you never seem to get the right defaults.
This is the starting point for setting these defaults.
Now following on from that explanation you might like to take a look at this page on
Office.com, Format a date the way you want.
And this covers Excel 2016, 2013 and 2010.
And apart from referring you back to Control Panel at regular points it basically explains
how to set date formats, how to set custom date formats and so on.
I’m not really covering these formats on this course because I’m assuming that you’re
already familiar with these.
But if you do have any uncertainty about the formatting and the symbols and so on then
this is a very good page to look at.
So let’s start doing some things with dates and times.
Let me just choose a cell here and put a date in.
So that is June the 11th 1978.
Note that although I only entered the two digit year it becomes 19 because of that cutoff
date that I mentioned a little bit earlier on.
Let’s go back into that cell now and what I’m going to do is to change the cell format.
And I’m going to change it to Number format with no decimal places.
And it becomes 28,652.
Now this is the next thing that tends to confuse people.
A date in Excel 2016 on a PC is the number of days since the 1st of January 1900.
Now there have been a couple of minor errors in the way this has been setup right from
the very beginning and I’m not really going to go into those now and there are also some
well-known inconsistencies such as the fact that dates on a Mac are stored with a starting
point in 1904 instead of 1900, although Macs can deal with Excel spreadsheets fine and
vice versa.
But let’s just take it at the moment as a clean case that this is the number of days
since the 1st of January 1900 to the date I just entered.
Now when you look at a date on an Excel worksheet what you’re looking at is a representation
on a whole number.
And in fact times are stored as fractions of a day.
So midday on that date would be 28,652.5. 6 a.m. would be 28,652.25.
One of the things that Excel is pretty good at is recognizing dates and times.
If I start with this particular cell here, H6, let me just check the format of that cell.
It’s currently at the default General setting, no specific number format.
Let me just make this column a little bit wider.
Let’s suppose that I put in here now 10 August.
So just type that in as text.
You’ll see that Excel has looked at that and decided it’s probably a date.
So it will have changed the format of the cells.
It’s changed it to Custom Format and chosen that short date format, D-MMM.
But if I change the format of that cell to Number I’ll see that it’s correctly stored
it as a date.
That’s the number of days in question since the 1st of January 1900.
What about time?
Supposing I put in a time like 10:30 a.m.
Let’s see what it does with that.
Now you see it’s treated that as 10:30 a.m.
And if I look at the cell format for that, again Custom, but it’s chosen his time format,
H:MM, with an a.m./p.m. marker.
What about putting those two things together?
Again it’s realized it’s a date and time.
If I change it to Number format, let’s give it quite a few decimal places.
It’s actually 42,592.458333 as a fraction of a day.
However, if I tried to put that in a sentence then Excel will not recognize a date or time
or combination as part of a sentence.
So it’s important to understand how dates and times are stored.
And it’s important to understand that Excel will do quite a lot of work in recognizing
dates and times but that the formatting apart from the default formatting that Excel will
use is largely down to you and it’s important to understand how to use those formatting
codes, etcetera, in order to display dates and times in the way that you want to.
So it’s important to understand how dates and times are stored and to understand both
default formatting and how to change that default formatting for specific cases.
But it’s also important to understand the various functions that you can use with dates
and times.
For instance, a function you might use to tell you a date 60 days from today or perhaps
you might find what time it was two-and-a-half hours ago.
And some of those date and time functions are what I’m going to look at in the next
section.
So please join me for that.