Dates in Excel 2003 - Part 3

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.

by Office for Mere Mortals

| Mobile | click for more article services

By Michael Barden

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

Custom Date Format Creation

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"

Article posted: Tuesday, 20 March 2007
[1] 2 3 4 5 Next » [ View on Single Page ]

More from Office Watch:

Article Services sponsored by: Office Watch Ebooks - available now to download and read today.

RSS feed for this category

Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2010 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.
Search | Sitemap | Popular Topics | Privacy Statement | Advertising | Twitter | FAQs | Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J.