Wednesday, October 17, 2012

Custom Date Formats

Although they are a major part of the grist which drives the Excel, handling Dates can be a bit tricky and are often misunderstood. The most common Date Formats are 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 suppose you want to create a Date Format that is Not included in the built-in list in Excel? No worries, here is what you do. Let’s assume 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 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, the results will be a date format in the syntax: yyyy/dd/mmmm.

Controlling your Date Formats, even when they are Custom, is really quite easy in Excel. The payoff is Improved Communication. And that is, of course, The Bottom Line…

No comments: