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”.
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…)
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…)
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.