Excel Dates Done Your Way

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

Well, don’t be frustrated any longer!

Today, I’ve got just the information you need to make Excel do things your way.

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

Now, go to the Format menu, Cells choice. (Both Ctrl + 1 or right click and choose the Format Cells choice will work 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. I’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, 31)

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. 06, 07, etc.)

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

Okay, 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 put in the field a combination of codes that will give you a date the way you need it.

For example, if I enter dddd, mmmm d, yyyy into the Type field, data entered as 5-26-06 in a cell will be displayed as Friday, May 26, 2006.

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 of the dates in the selected area are just the way you want them!

~ April