Join Dennis Taylor for an in-depth discussion in this video Formatting numbers and dates, part of Excel 2016 Essential Training.
- In Excel, the standard display of dates is as we see them in Columns B and C, month, day, year in the United States, four-digit year, no leading zeros for the month or the day. If you type in an entry for example in cell G5 and simply typing 5/6, that would be May 6th, /16, 2016. But as I press enter, you see what happens. And for a lot of people in the United States, this is just fine, this is what they would want. If you want to save some space and you prefer a two-digit year, you can click the area that you're about to change, for example, Column B here, and immediately go to Format Cells, either by right clicking and choosing Format Cells, or pressing Control 1.
And you'll see on Format Cell's dialog box, the Number tab, Date. And quite a few options in here. And you can even create your own customized versions if you wish. Here's the same kind of date but displayed with a two-digit year. And again, no leading zeros. If that's clear to you and if there's no confusion whatsoever about which century we're talking about in those years, fine, it will take up less space. You can go behind the scenes to do that. At a later time, if you're seeing this and prefer a four-digit year, you don't necessarily have to go into that dialog box.
On the Home tab, you can click the drop arrow right here that's above the other choices, and choose Short Date. If you choose Long Date, and I think everybody tries this maybe at least once, but maybe not so much later, Long Date looks like this. And that's probably a lot more detail than you would need most of the time. I'll undo that with Control Z. Notice something else too. If the dates are selected and you go to Format Cells, Control 1, if you then switch to Custom, now there's some fine tuning capabilities here that are well beyond the scope of this course, if we were talking more about numbers.
Quite a few choices in there and you can customize this to no end. But when it comes to dates and times, there are some simple rules for controlling this, if you wish. ms and ds and ys. If you put in mmm, that abbreviates the month. If you put in mmmm, it spells out the month. So if you want to put in the month and then if you wanted to put in day of the week, dddd would spell it out; ddd would abbreviate it; dd would put in a day as 09 or 07, a single d will handle all kinds of dates, just the numerical part of it.
So if we put an mmmm dddd, space and yyyy, this probably is not what you would want. See what this looks like. mmmm dddd yyyy. Probably not what you would want because we don't have the actual day there. You may have the day of the week, but not the number. So jumping back to Format Cells again with Control 1. All kinds of variations. I think you can see this could be a time waster if you're not careful too. Make this a little bit better. Right before the yyyy, you could put in a d, comma, space.
Then it would look like this. Again, that's probably a little unorthodox too, but you've got tons of choices out there. Again, a quick fix to go back to the standard, if you like the standard, I'll click the drop arrow here. Again, this is on the Home tab in the number group, the drop arrow at the top here, and choose short date. And here, we have to readjust the column if we want to keep that. Double-click the boundary between B and C. Sometimes you want to change the format for readability or possibly you're dealing with data that travels back and forth across different countries. I think a lot of you know that month, day, year, although used in the United States, is not commonly used elsewhere.
It tends to be day, month, year. Now we're not going to change that here, but what we can do is use a keystroke shortcut. It's Control Shift 3. It's the only keystroke shortcut for dates, and you like it or you don't. I understand it's widely used in the federal government. It does eliminate that possible doubt that you have sometimes as to which month it is. Not doubt for very long but you have to sometimes pause and think, "Does seven mean July or not?" And this just eliminates that particular doubt. What some people don't like about this is the digits before the month and after sometimes gets confused.
Is that 13th, November of 2014? Or is it the 14th of November 2013? Well, it's the former, not the latter. That's 2014. So if you like this but you'd like to change it, once again, you could go to Format Cells by way of Control 1. On Custom here, and you do see a preview here too, if you had one cell change. But if we got them all, we won't. But we could just put in a four-digit year. So instead of yy here, we'll add two more. and then we'll have this kind of display. Have to adjust the column width too.
So there we see it that way. That could be a preference too, but no keystroke shortcut to get to that. If you're dealing with times, certainly one way to display the times of day would be this way. Now this is 24-hour style, widely used throughout the world, not so much in the United States although you do see them on airline timetables, for example and reservations and that sort of thing. A quick fix here, if you prefer to see AM/PM, and we could do it for both columns, is Control Shift 2. And we see these choices here. Now when you're measuring time, not AM/PM but elapsed time, here are some actual differences between the two.
We would want to keep them this way. In other words, you don't want these to be AM/PM. Now when you write a formula like this, sometimes, in other words, if this were not here, if I type =M2-L2, because this was formatted previously, it's not going to change. But if I write the formula out here, =M2-L2, I get an answer like that, which would not be acceptable. So if you had answers like this, we do the same kind of thing we did with dates. Immediately go to Format Cells, by way of Control 1.
And here, select the Time category. And the other option you would use frequently is 13:30. And we see the preview above and it says 1:19. So I think for most people who use time displays, it's either going to be the AM/PM style, or they want to have it displayed without AM/PM, looking like this for example. Now if these times were farther apart, we would cross the 12-hour boundary. Suppose, for example, the start time here on one of these here, what if it's 2:23 AM and maybe the end time over here was 11:52 PM? I'll just put the PM there, like that, that's 21 hours.
That's the way we would want to see it be displayed, no AM/PM because we're talking about a difference here. Keep in mind too that although you might not necessarily be using dates and times in an arithmetic or calculation kind of way, the stage is set for using the numbers that way, provided you enter the data with colons properly. And a little tip here, which we mentioned in a previous movie, if you are putting in times of day and you want to see the AM/PM displayed, you can type for example 4:45 space P for PM.
Something like that, a space A for AM if you wish. So at different times, you'll need the AM/PM, and other times you won't. And sometimes you'll need to get rid of them, sometimes you'll need to add those. But it's all available by way of Format Cells on the Number tab. And getting back to dates, the same idea there. So Control 1, our shortcut for getting there, is really helpful when we're adjusting date and time formats in Excel.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros