MS Office 101

Dates In Excel

I'm sure by now you've realized that when you type a date into MS Excel the program seems to magically know what it is and treats it as such.

Cool—less work for us.

But have you ever wished the date was in a different format?

I mean, maybe you want just the month and day—10/15 for example. Or maybe you're looking to have something like 15-Oct-03.

Well, what you may not know is that Excel has a whole list of built-in formats to choose from.

Here's the scoop to dating your way.

Select the cells to be formatted.

Now go to the Format menu, Cells choice. (Ctrl + 1 works too.)

You're looking for the Number tab.

On the left, in the Categories list choose Date.

On the right (in the "Type" section) a list of standard options is displayed.

Select the format you need.

Click OK.

Poof! Date format done.

But wait... what was that I heard?

The standard dates aren't what you're looking for either?

Well then, keep reading while we cover the business of creating custom date formats.

(Yes, that's right, date formatting to please everyone.)

Office Tip of the Day

Dates Done Your Way

OK, so you're frustrated with the standard Excel date formats and you're looking for a way to meet your needs.

Well, be frustrated no more!

Today we've got just the information you need to make Excel to do things your way.

First you need is to select the cells that should be in the date format.

Now go to the Format menu, Cells choice. (Ctrl + 1 works too.)

You're looking for the Number tab.

On the left side you'll find the list of categories

Choose Custom from the list.

You can scroll through the "Type" choices on the right just to see if your needs can be met with what's there - but if not don't worry - we've got the info to get exactly the formatting you need.

At this point you probably need to know the symbols for date formats. (After all, you can't create custom formats if you don't know the "lingo".) So... let's take a look at the Excel date "lingo".

Date codes:

d will give a day number. (i.e. 1, 2, 3, 4 ... 30,)

dd will give the day number as a two-digit number. (i.e. 01, 02 ... 30, 31)

ddd will give the day of the week - abbreviated. (i.e. Mon, Tue, etc...)

dddd will give the full word for the day. (i.e. Monday, Tuesday, etc...)

 

Month codes:

m will give the month number. (i.e. 1, 2, 3 ... 11, 12)

mm will give the month as a two-digit number. (i.e. 01, 02 ... 12)

mmm will give the name of the month - abbreviated. (i.e. Jan, Feb, etc...)

mmmm will give the full name of the month. (i.e. January, February, etc...)

mmmmm will give just the first letter of the month name. (i.e. J, F, M, A, etc...)

 

Year codes:

yy will give the year in two digits. (i.e. 03, 04, etc...)

yyyy will give the year as four digits. (i.e. 2003, 2004, etc...)

 

OK, so we've got the codes to create our custom date formatting but what do we do with them?

Well... when you clicked into the Custom category you might have noticed that on the right, under the word "Type", a field appeared which will allow you to enter your formatting.

All you have to do is to put in the field a combination of codes that will give you a date the way to need it.

For example, if I enter dddd, mmmm d, yyyy into the "Type" field, data entered as 10-30-03 in a cell will be displayed as Thursday, October 30, 2003.

Did you notice that the commas I inserted into the code showed up in the date?

Yes? Good. (That's just a handy little tidbit of info that might come in handy some day.)

You also might want to notice the preview box located just above the "Type" field.

If there was data in a selected cell when you started, you can see how it's coming along as you're entering your custom code. It lets you check your progress so you don't find out after you've left this window that you have a wrong format.

When you have everything set click OK.

You're returned to your worksheet where all dates in the selected area are just the way you want them.

~ April