- Worldstart's Tech Tips And Computer Help - http://www.worldstart.com -

Dates Done Your Way

Posted By On November 19, 2004 @ 2:15 PM In MS Excel | Comments Disabled

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.

image

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.

image

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


Article printed from Worldstart's Tech Tips And Computer Help: http://www.worldstart.com

URL to article: http://www.worldstart.com/dates-done-your-way/