How to Format Dates in a Microsoft Excel 2016 Spreadsheet
During this Microsoft Excel 2016 training tutorial video, we will show you how to format dates entered in the workbook. You will learn how to change date and time settings according to your region, how to format dates in their short or long forms, how to work with dates that appear invalid in a cell, as well as how Excel interprets numbers entered as dates in a cell.
Video Transcript
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.
Want More? Get Started With a Free Excel 2016 Course! Click Here
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.
Want More? Get Started With a Free Excel 2016 Course! Click Here
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.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
