Create your own customized date and time formats

- [Instructor] Excel has any number of different date formats, and on this worksheet, in columns A through D, we're seeing the same date displayed four different ways. Column A has the full expanded date display, with the day of the week spelled out, the month spelled out, day, and four digit year. Column B, see a variation here, column C, the main difference in these two is we see leading zeros in column C for those months before October and those days before the 10th. And in column D a more compact version that only uses two digit years.

We can change these anytime we want. You could also have a situation where maybe the dates that you're recording here are all within the same year, you might not want to display even the year. So let's imagine we want to change, for example, what's in column B. We can easily go into format cells by right clicking the selection, or simply pressing Ctrl + 1, either way. If we right click, choose format cells, takes us to the format cells dialog box, we want to be sure to be on the number tab, and then choose date, and lots of options here, over 15 of them.

And lots of variations as we go through these. Some of these are not obvious, is that March 12th, or is that March of 2012? Let's choose that option, click OK, and then we'll see something like that, looks like that's for the year. I'll undo that with Ctrl + Z, so you might want to experiment a little bit. And by the way, a little oddity here, if you click only one date, and then jump into format cells, you will get a preview as you explore some of the options, so if I look at 3/14, and that's associated with today's date right now, that will simply put in the date without the year.

This 14 MAR here, we see in the preview, same idea, but a different display and so on. So we can get some preview if we've selected only a cell that contains dates itself. So some variations there. There is a keystroke shortcut associated with dates, there's only one format that works with keystroke shortcut, Ctrl + Shift + 3. So if for example, if we wanted to change column D here, just click column D, Ctrl + Shift + 3, sometimes this is displayed as Ctrl + Shift + #, same key, there we are, it looks like that.

So it uses a two digit year. You won't see the Ms and Ds and Ys except when you happen to go into custom formatting, but as we see them here, I think you can quickly figure out what they mean, and also as you look in columns F and G, you can see how the various Ms mean different things, as do the various Ds and the two Ys that we see here as well. So you might want to create your own. So let's say we want to change column C. Another way to get in here quickly, select column C, press Ctrl + 1, that takes us to format cells, choose date, again, we could explore the options there, but let's jump over into custom.

So we can use Ms and Ds and Ys in a variety of ways. Maybe for example we'd like to show day of the week as an abbreviation, and you can see off to the right that we would use DDD. If we want a comma after that, or a space, we put that in right now, maybe a comma. I might want to spell out the month, or if I want an abbreviation here, I'd use 3 Ms. Spell it out, 4 Ms. Space, I want a single digit for the day. Now of course if it's the 10th and above we'll see two digits, but by putting in DD we would get leading zeros.

I'm putting in a single D here, comma space, 4 Ys for a four digit year. And OK. And we'll have to make that column wider by double clicking the boundary between C and D. So that's one of many variations that we could create. And again, looking at the entries in columns F and G, you can see what you could be using to come up with that date display that you need. Now off to the right here, we've got some times as well, and this is an unusual list, you probably wouldn't mix these this way. This is an unusual list as well.

They're both atypical. But, if you were copying data from different sources and saw the dates like this, you might want to standardize them. There's one keystroke shortcut associated with dates, it's Ctrl + Shift + 2, sometimes you'll see that displayed as Ctrl + Shift + @, it is the same key, and I'll use it here, Ctrl + Shift + 2, and that's the display that we see with a.m.s and p.m.s. If you would rather use 24 hour type times, like 1700, and standardize these, we'd jump into format cells again, Ctrl + 1 would be fast here, this time of course we go to the time category, and although not obvious at first, the choice you would make here is the one that shows 13:30, 24 hour type.

And so we could be using that display. The idea of course is in any given column or in any list, you would want to have a standardized display, either the a.m. p.m. style, or the 24 hour style. Now, one other oddity about formats, when we're working with times. I want to add these. These are the number of hours spent on a project over a three week period. We want a total at the bottom. I'll simply go to the bottom, and either click the Auto Sum button, or press Alt + =, and there's that total, press Enter, but we know that's not correct.

After highlighting these, and oddly enough, if we look in the status bar at the bottom of the screen we will see a total. If you're not seeing a total in the status bar, by the way, right click and choose the Sum option. You might want to choose some of the others too, occasionally they're handy for other features as well. Anyway, that total is correct down there, it's 92 hours. Here's a 20, how did that happen? Well let's change the format here. And I want to point out, this is not at all obvious, but you certainly would know that the 20 is incorrect. So, let's again jump into format cells, Ctrl + 1 is certainly one way to get there fast, and perhaps by process of elimination and going back to time here, the time category, you would come to realize that the option here that has the 37 in it will display the correct answer, there's the 92 hours.

Now it might be displaying more than you want. Actually it's showing seconds here as well, so let's jump over to custom and change this display so that we only see the H and the MM, and although it's not obvious, and you wouldn't know this at first, when the H is within brackets, that means that you can use this as a format, when you're using hours that go above 24. That's a bit much to absorb all at once, but this is the format we want, the H within brackets means we can display hours greater than 24, :MM, and there's the result we would want to see.

And by the way the 20 that we saw earlier was 20 greater than the previous number of whole days, that would have been 72 hours. Nevertheless, we do have a way to correct this. Now, not too important, but just as a matter of how we can use these together, if you'd like to display information this way, I'll double click right here, this is a bit long, perhaps, but we're using various combinations of functions like hour and minute to display the information in a way that could make sense in some environments. That will display a little bit better if I move to the right, and we see this without wraparound.

So, there's an option on how you could be displaying information using the hour and minute function along with some other text capabilities here. So we've seen a number of different formats primarily associated with date but also with times, and again, anytime you're using these, you have the ability to display that information in a way that makes sense to you, using the various formats built in, or the custom formats that you can create.