Creating custom date formatting
Video: Creating custom date formattingIn 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.
Viewers: in countries Watching now:
In Excel 2010: Working with Dates and Times, Dennis Taylor shares his solutions for optimizing the use of dates and times in Excel 2010. This course explains what's going on behind the scenes when Excel stores dates and times, gives tips for entering dates and times, and shows options for date and time formatting. It also demonstrates the various date and time functions and shows how to calculate with dates and times in a range of scenarios. Exercise files are included with the course.
- 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
Creating custom date formatting
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.
There are currently no FAQs about Excel 2010: Working with Dates and Times.