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 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).
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”
CUSTOM DATE FORMATS USING TEXT()
Another way to display a customized date is to use the TEXT() function along with a recognizable pattern of the m’s, d’s and y’s mentioned above.
For example, if you want a cell to display the current day of the week, try using the formula =TEXT(TODAY(), “dddd”). If today’s date is 15-Mar-2007, then the result will be displayed as “Thursday”.
You can also specify a particular date within the formula itself. Typing =TEXT(DATEVALUE(“15-Mar-2007”), “dd mmmm”) in a cell will display the result as “15 March”.
If you have a date referenced in a cell (e.g. A1) that you wish to display in a more detailed fashion elsewhere, try using a formula like =TEXT(A1, “dddd, dd mmmm yyyy”). With “15-Mar-2007” in cell A1, the result would be displayed as “Thursday, 15 March 2007”.
THE POWER OF NETWORKDAYS()
One date-based Excel function that is worth its weight in gold is NETWORKDAYS(). The syntax is as follows =NETWORKDAYS(start_date, end_date, holidays). This function returns the number of whole working days between two dates – excluding weekends and any specific dates you have typed in the “holidays” parameter.
The “holidays” parameter is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
It is possible to enter starting and ending dates directly as serial numbers, as text, through a cell reference, or by using the DATE or DATEVALUE functions. You can also enter a single holiday date as the holiday parameter in this way.
For example, say we wanted to find out how many work days there are between the 19th of March 2007 and the end of the year – only excluding Christmas Eve and Christmas Day as the designated holidays. The following formula can be used to produce a result of “204” work days (taking particular note of the syntax used to include multiple holidays):
=NETWORKDAYS(DATEVALUE(“19-Mar-2007”), DATEVALUE(“31-Dec-2007”), DATEVALUE({“24-Dec-2007″,”25-Dec-2007”}))
If you created a list of holidays in cells A1 through to A10 and wanted to know how many work days there are between now and Christmas Day (excluding your range of holidays), you could use the following formula (taking particular note of the syntax used to include a cell-reference range of holidays):
=NETWORKDAYS(TODAY(), DATEVALUE(“25-Dec-2007”), A1:A10)
The NETWORKDAYS() function is particularly useful for giving you the actual number of available work days left before a large project is due. Once you take the weekends out of the equation, you’ll begin to realize just how little time you really have left!
You could also theoretically use NETWORKDAYS() to count down the number of work days until you retire (however frighteningly large).
NOTE: If the NETWORKDAYS() function returns a #NAME error you will need to install the “Analysis ToolPak Add-In”. To do this, go to “Tools | Add-Ins”, select the “Analysis ToolPak” check-box and click “OK”.
THE 1904 DATE SYSTEM AND CROSS PLATFORM SHARING
In our first “Dates in Excel” article, we briefly mentioned the 2 Jan 1904 base date system that can be enabled under “Tools | Options | Calculations” as an alternative to the default 1 Jan 1900 base date system. We didn’t mention why the option is there or what pitfalls there are in using the system.
Office for Mere Mortals reader Char R. wrote in with some interesting points on the subject. When Microsoft originally made Excel for Mac there was a problem that prevented it from correctly using the 1 Jan 1900 base calculation date, hence the use of a 2 Jan 1904 base date. This means that when you type the serial date number 1 in and format it as a date, Excel for Mac displays it as 2 Jan 1904 12:00AM, while Excel for Windows displays it as 1 Jan 1900 12:00AM.
Since Excel for Mac defaults to the 1904 system and Excel for Windows defaults to the 1900 system, sharing worksheets between Mac and Windows can be problematic.
According to Microsoft: “If you transfer files from Excel for Mac to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date.”
Since Excel in both Windows and Mac can be changed to use either date base system, the best practice when sharing a worksheet across platforms is to make the recipient aware of what format the dates are stored in. They can then change the system stored in the worksheet to match their default system. Similarly, when receiving worksheets across platforms it is best practice to check how the dates are stored and change it to match your default system.
Since Mac users often live in a world of compromise where Microsoft is concerned, they may find it more convenient to clear the “1904 date system” checkbox (“Edit | Preferences | Calculation”) when sending worksheets to Windows users. They may even wish to use the 1900 date system by default, although this may then create issues when sharing worksheets with Mac users who still use the 1904 system and so on. There really is no perfect solution.
For more information on the 1900 and 1904 date systems, read the Microsoft KB article.
CALCULATING AGES BEFORE 1900
Excel enters dates prior to 01/01/1900 as plain text. This Microsoft KB article provides a macro for a function that calculates the age of someone or something created before 1900. It works with dates entered as text beginning with 01/01/0001, normal dates, and can handle dates when the starting date is before 1900 and ending date is after 1900.
See Also
- Excel Online – changing date format
- Getting truly random numbers into Excel
- Dates in Excel 2003 – Part 2
- Dates in Excel 2003 – Part 1
