Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data
Skill Level Intermediate
In addition to the built-in formatting options for formatting dates and times in Excel you also have the ability to create your own custom formatting. Suppose, for example, the data in Column B here, we might want to have the day of the week included here. Let's recognize if we were to right- click here on Column B and choose Format Cells in the Date category under the Number heading here we do have an option that we'll put in the day of the week along with the date. Now that might be more than what we need here.
If we jump into Custom, that format will be represented here. Now there are some things here we don't want to talk about. We could get pretty far afield looking at some of these other formats, but let's just recognize the use of d's and m's and y's in this display. Let's suppose we do want the day of the week, but maybe we only want a three-letter abbreviation. Three d's will give us a three-letter abbreviation, four d's will spell out the day of the week. If we want the months spelled out, four m's, if want only an abbreviation, three m's.
If you want the day, for example, if it's the 3rd, you want to see a 03, put in dd. You want to see simply a 3, put in d, and of course if it's a 12, this will automatically handle that option. Often you would probably want a four- digit year, but not always. But again use commas in here as you wish. This particular display is going to give us a three-digit day of the week, a three-character month as I put in that third m there, the day of the month, a comma and four digit years.
Adjust the column width, and there we have it. Experimenting with this using m's and ds' and y's. You can come up with quite a few different varieties of how you might display dates. Same sort of thing in Column D as well. And keep in mind too, sometimes all you really want to do is display if it's all within the same year. Use the built-in Date option here. It simply displays, everything except the year. So an example in this case might be just these here. If the heading says 2011, then fine, so that's understood.
But different ways to do this either using the built-in variations or by customizing these using various combinations of m's and d's and y's.