Welcome back to our course on Excel 2016. By now I hope you’ve completed Exercise
01 okay. In this section we’re going to carry on looking at entering and editing data.
And in particular in this section I’m going to spend quite a bit of time looking at Date
Formatting. Now dates are very often important in your worksheets and looking at the formatting
of dates is actually a good way of finding out more at formatting data in Excel as well.
Now in order to demonstrate this I’m going to start by creating another new workbook.
I’m going to put in this workbook details of some business expenses. Now for the moment
I’m going to work near the middle of this worksheet. It doesn’t really matter where
you work because you can always remove and add rows and columns later on a worksheet
if you need to. But I’m going to type in one of the cells, let’s say M9 here, 3/9.
Now what does 3/9 mean? Well if you were Microsoft Excel you might take a good guess at it. Perhaps
it means 3/9, it’s a fraction. Perhaps it’s a part number, a part code of some sort. Or
perhaps it’s a date. Depending on where you are in the world if it’s a date it could
be March the 9th or it could be the 3rd of September. And when you’re dealing with
dates local is very important. But it’s also important to understand that there are
many different formats, many different ways in which people show dates.
Now in order to see what Excel makes of 3/9 let’s just click somewhere else. Let’s
click in N9. And it’s taken it as the 9th of March. But notice I have 3/9. It thinks
that’s the month number and then the day number. But it’s actually taking it as the
9th of March. So why has it done it that way round?
Now in order to see why dates come out on your device in the way that they do we need
to look at something in Control Panel. So make sure that you can locate Control Panel
on your Windows device. And then we need to look at the Region Setting. Now I’m telling
you this now because whatever I’m doing in this section on dates may look quite different
to you on your machine because you may have different settings to make. It doesn’t really
make any difference to how the whole thing works in terms of Excel but it will make a
difference to what you see in the workbooks, the worksheets that I’m showing you if you’re
trying to work along with me. Now the first thing is in this Region Dialogue
my location is United Kingdom. But my formats are set to English (United States) formats.
So for dates and times the formats I’m using are these and how various dates and times
look in these formats, there are some examples of those in the lower part of this screen.
So for instance my short date format is Month/Day/Year. So when I typed in 3/9 as an even shorter
date Excel takes that as Month/Day and then when it displayed it back to me it displayed
it back to me as 9-March. So it took the three as the month and the nine as the day. Now
whatever happens on your device it’s going to be largely governed by your setting that
you’ve got for the region on your device. Now if you’re in the U.S. you’re probably
going to use the same setting as me. You’re putting up your location set to U.S. as well.
Somewhere else in the world, including the UK and much of Europe, you’re probably going
to want your short dates the other way around. But be aware of what the settings are on the
machine because generally speaking they’re not set totally dependently in Excel. You
can change the settings but by default Excel picks up its regional settings from your Control
Panel settings. So let me now see what happens if I put in
a longer version of a date in cell M10. This time I’m going to put in 3/10/15. How do
you think that’s going to come out? Now what it’s done, first of all, is, is recognize
it as a date. And because I didn’t specify a century it assumes that it’s the current
century. So it’s put the year as 2015. But it’s left it in that 3/10/2015 format because
my Date Format is U.S. date format. That’s Month/Day/Year.
But notice that with those two the way the date appears is very different and it’s
important to understand what’s happened there.
Let’s look at the first one again and this time I’m going to go into the Format Cells
Dialogue and just have a look at what Excel 2016 made of that. And what it made of it
is actually quite surprising. There is in the categories here a category of Date but
it hasn’t used the category of Date. It said this is a Custom category. It’s sort
of a date because it’s using D for Day and MMM for Month but it’s not really strictly
treating it as a date. It’s treating it as if you like a string of characters that
happens to be made up of two parts of a date, the day and the month.
But notice it doesn’t say Month first then Day. It says Day and then Month. And I could
in fact here change that format manually to switch it around. But if I look at the second
date, that has been interpreted as a date. And an example of the date is on the right
there, the sample. That’s the one I entered. There is the Date Category selected on the
left and the type is the top one on the right. And apart from the format there which is basically
Month/Day/Year there is also an asterisk. And what that asterisk means is that this
is a date which will change according to the regional date and time settings on the device
where you’re looking at this workbook. So if you were opening this workbook in say the
UK or somewhere else in Europe or perhaps in Australia or New Zealand then it will interpret
the date in the way that I intended. So it will interpret it as March the 10th even though
if you were looking at 3/10/2015 as a UK format you’d say that was the 3rd of October.
So if you want to use dates which will actually be interpreted according to the locale where
the Excel workbook is opened you need to make sure that you use one of these asterisk formats.
And that again is one of the reasons why I’m telling you this pretty early on in the course.
Dates can cause people all sorts of problems. It’s important to understand that you can
set up dates which are if you like internationally safe and will be interpreted according to
the format of the machine that they’re opening. Now if you wanted to change this date format,
supposing I wanted to change that M10 content to be this nice long date format here. Let’s
choose that one which is still a stared format. Click. Now you see you’ve got Tuesday, March
the 10, 2015 and that’s absolutely fine because I intend it to be March the 10th and
not the 3rd of October. In fact if I go back to the contents of M9
I could apply the same format there. Let’s call Format Cells from the Ribbon this time.
Let’s make it a Date Format. Let’s choose that, click on OK and that’s fine. Note
that it is assumes that as the year wasn’t specified it’s meant to be this year, 2015.
Now earlier on I showed you how to format a whole range of cells. So let’s format
column M to be Date Format using that same long version with the asterisk there. Now
let’s try typing some different things into the cells in column M. What about 11/4? Let’s
see if you can work out what that’s going to be. Well it’s going to be Wednesday,
November the 4, 2015. Now what I want to do is to just go back and
reduce the width of column M and make a point about this. In some situations if you have
data to display, and this is particularly true for dates, and they just won’t fit
you can get a string of hash symbols. Now that doesn’t mean to say that the value
in the cell is invalid in some way because in fact if I clicked say in M9, if you look
in the Formula Bar you can see the contents of M9. Not in its full format of course, in
the Short Date Format, but you know that there’s valid content in the cell. But in order to
see that valid content you may have to in this case increase the width of that column.
Now as you saw before one way of doing that is to do an AutoFit Column Width and then
everything will be fine. But you will often see that, particularly with formats like Long
Dates. Now another useful thing to observe there
is that if you wanted to edit the date, Wednesday, November 4, 2015, and you wanted to change
it from November the 4th to say September the 11th you may be looking at that thinking,
“Oh my what day of the week was September the 11th?” and you’re trying to work out
what the day you should type first. Well, if you use the Formula Bar version here then
you can edit the date in its short format even though it will be displayed in the long
format. So if I wanted something like say September the 11th I could put the day is
11, the month is 9, tick, and of course it’s a Friday. So again a useful thing to know
about working with dates. And one other important point to make here
is that although you can apply all sorts of formats to all sorts of data that you are
entering and editing there are of course many situations in which you can’t apply a format
to something. So for instance, if you take this text, Train Fare York to London, and
I wanted to format that as a date I really wouldn’t get very far. So I click on Date,
click on OK and of course it won’t do it for me. If I had a number like 19,763 and
I tried to format that as a date it actually comes out as 2/8/1954. Now you may think now
in what way did that nice long number there come out as a date in 1954? Well the reason
is because dates are stored as the number of days since a date in the past. So don’t
necessarily assume that because something doesn’t look as though as in this case it’s
a date that it can’t be interpreted as so by Excel. So that’s really just a word of
warning. But clearly again if I take Taxi to International Chemicals HQ and tried to
format that as say currency that wouldn’t work either. So you can’t do everything
but sometimes you’ll get taken by surprise with some of the formats that do appear to
work in Excel. That’s it for this section. I’ll see you
in the next one.