Dates - Have It Your Way!

Dates can be curious animals in Excel, and for most Excel users, they bear a closer look.  We are all familiar with the mm/dd/yyyy date format, but it is not “All Things to All Users”.  It is, in fact, very convenient to use Custom Date Formats in Excel, and it is easy to set up custom formats on your worksheet. Let’s look at some Practical and Useful Date Formats.

The most common Date Formats are, of course, as follows:

•   m - Month as a number without leading zeros (1-10)
•   mm - Month as a number with leading zeros (01-10)
•   mmm - Month as an abbreviation (Jan - Dec)
•   mmmm - Unabbreviated Month (January - December)
•   d - Day without leading zeros (1-10)
•   dd - Day with leading zeros (01-10)
•   ddd - Week day as an abbreviation (Sun - Sat)
•   dddd - Unabbreviated week day (Sunday - Saturday)
•    yy - Year as a two-digit number (for example, 12)
•   yyyy - Year as a four-digit number (for example, 2012)

But let’s assume you want to create a Date Format that is Not included in the built-in list in Excel. What do you do then?  Let’s say that you wish to have a date format that has the elements shown as follows:

1.   4-Digit Year
2.   2-Digit Day
3.   Unabbreviated Month

The Steps to Achieve this Format are as Follows:

1.  Right-click on the cell containing the date
2.  Select Format Cells
3.   In the Category list on the Number tab, select Date
4.   In the Type list to the right select the format closest to what you want
5.   In the Category list select Custom, and the format you selected will show in the Edit Box above
6.   Modify the format in the Edit Box and see the preview in the Sample
7.  When you are happy with your results click OK
8.  The new format will be applied to the cell and will also be Added to the List of custom formats

For our Example above, the outcome will be a date format in the syntax: yyyy/dd/mmmm.

You may not have a current need to control your Date Formats, but if it ever arises, you now know it is easy in Excel. I am sure we all agree that Better Communication (often lacking in business) is always a worthy goal.