Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel gives you a variety of ways to display Date and Times. In this worksheet called Date and Time, look at the data in columns B and columns C, both showing essentially the same kind of data, simply displayed differently. The data in column B, shows four- digit-year and column C, two-digit-year. Obviously, one takes up more space. Sometimes that's the issue, usually not. Is it clear to the audience, whoever is using this? Recognize also the data in column I is different too. The formatting in column I, does clarify which month it is.
Sometimes and particularly, if you're getting data from other countries, outside the United States, they tend to work with the layout of day, month, year, whereas in the U.S., we tend to use month, day, year. But if there's any doubt, column I tends to eliminate that by that display. Let's take a look at some of the built-in formats here. The standard display in most Excel versions is, as you type an entry, you see a four-digit-year, the way we're seeing these in column B. Let's imagine we might want to change the ones in column C here. If it's the entire column, we'll just right-click on the entire column, and one of the many ways we can get into formatting--Format Cells--after right-clicking.
In the Format Cells Dialog Box>Number tab>Date, and you've got to kind of put the pieces together a little bit, if you simply wanted to show month and day, that's the way we see it right here. We would use that display. Here's the one with the two-digit-year. Here is the one that uses leading zeros for months that are under 10 or days under 10 and other ways to display the data as well here. Quite a few variations as we look through the list, even spelling it out this way. You might even try this one for example. That certainly takes up more space, but it certainly eliminates any doubt as to what you mean by this.
And you could even go further with these. You can apply your own. Now, I often steer people away from the idea that you can actually create a custom format, but what I just did here was to right-click column C and choose Format Cells, and on the Date tab-- after having selected this, I'd just made the adjustment to-- I'm going to jump over to Custom. What I then might do in this display and I don't want to explain every single icon here, but I'm going to change this so that instead of simply four M's, a D, and three Y's, in front of this, I'm going to put four D's.
That actually will spell out day of the week. If I put in three D's and we put in the abbreviation, a comma and a space. Now, would I really care about a higher date as to what day of the week it is? Probably not. But in certain other kinds of data, I might, and this would allow us to see it clearly as we see here. So there are lots of variations on that. These do take up a lot of space, but for clarity, sometimes that make sense. There's only one keystroke shortcut associated with date entries, that's currently being used in column I. If we want to use that in column B, simply click column B and keystroke shortcut is Ctrl +Shift+#.
So, that displays the date information that way. Now, when it comes to times--we've got some columns over here with times-- the way these times are entered--and they're also set up to handle formulas too-- we use colons for time entries. One way to display times is the so- called 24-hour style, which is widely used throughout the world. Maybe a little bit less so in the United States, but even here we see these a lot; 24-hour time like this. The variation you might want to use here is, once again, by right-clicking, going in the Format Cells, on the Number tab>Time.
Maybe not obvious at first, but the choice for PM is right here. Now, this will show AM or PM as necessary. Click OK. So, we see that display and readjusting the column, it's possibly this way. We can display times also more coherently, could have done those both. There we are. We either use the AM or PM or the 24-hour style. If the numbers are coming to you this way and you're saying, "I want to change them all", we'll do the whole columns, here too. Remember, another way to get to Formatting Cells is Ctrl+1.
You can do that here, and the variation here, where we want to want not see AM, PM, is the choice 13:30. Click OK. If you entered times with colons, and previously in our examples, entered dates with slashes or hyphens, you do set the stage for using these in formulas and taking advantage of Excel functions that relate to dates and times. So, it's a strong feature and the formatting capabilities certainly clarify the appearance of the data in the way that you want this data to appear.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91122 Viewers
80 Video lessons · 138161 Viewers
59 Video lessons · 56978 Viewers
52 Video lessons · 70612 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.