In the last edition of Office for Mere Mortals, we used the basic date formulas in Excel as building blocks and showed how to combine them into more powerful date-based functions.

In this article we round off our date functions in Excel topic with a look at custom date formats, the NETWORKDAYS() function, and an in-depth discussion on sharing date-related worksheets between Mac and Windows.

CUSTOM DATE FORMATS

There are times that you want to see the displayed value of a date as something other than the actual date or the generic date formats. For example, rather than a cell in a worksheet displaying today's date as 03/15/2007, you might want to see the value as either "Thursday", "Mar-07", "15 Mar" or "Thursday, 15 March 2007". All of these formatting combinations and many more can be achieved in a couple of different ways.

The first way is to set the format on the actual cell in question. To do this select the cell or range of cells, navigate to "Format | Cells" and select the "Number" tab. In the "Category" pane, select "Date" and scroll through the "Type" pane for common examples of how dates will appear.

If you can't find the date format you want in the "Date" category, choose the "Custom" category and you can type in your own date format in the "Type" text box as a combination of m's (month), d's (day) and y's (year).

The following list from Microsoft describes each of the date format building blocks that can be used in a custom date format:

• m - Display the month as a number without a leading zero
• mm - Display the month as a number with a leading zero when appropriate
• mmm - Display the month as an abbreviation (Jan to Dec)
• mmmm - Display the month as a full name (January to December)
• mmmmm - Display the month as a single letter (J to D)
• d - Display the day as a number without a leading zero
• dd - Display the day as a number with a leading zero when appropriate
• ddd - Display the day as an abbreviation (Sun to Sat)
• dddd - Display the day as a full name (Sunday to Saturday)
• yy - Display the year as a two-digit number
• yyyy - Display the year as a four-digit number

So assuming our cell contains the date 03/15/07 we can create a custom date format to display our date in any number of ways (not limited to the following):

• "ddd" produces "Thu"
• "dddd" produces "Thursday"
• "dd-mmm-yyyy" produces "15-Mar-2007"
• "dd, mmmm-yyyy" produces "15, March-2007"
• "mmm-yy" produces "Mar-07"
• "dd mmm" produces "15 Mar"