Chapter 7: Dates and Date Functions

Overview

• When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change 37625 to a normal date?
• Can I enter a formula that automatically displays today's date?
• How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
• How do I determine the number of workdays between two dates?
• I have 500 different dates entered in Excel. What formulas can I use to extract the month, year, day of the month, and day of the week from each date?

To illustrate the most commonly used month-day-year formats in Excel, suppose-today is January 4, 2004. We could enter this date as any of the following:

• 1/4/2004
• 4-Jan-2004
• January 4, 2004
• 1/4/04
  
  

  Note

  If you enter a year with only two digits, for years entered as 30 or higher, Excel assumes the digits represent years in the twentieth century. For example, 1/1/29 is treated as January 1, 2029, but 1/1/30 is treated as January 1, 1930. Each year, the year treated as dates in the twentieth century increases by one.

If you want to walk through the explanations for the problems in this chapter-in Excel, open the file Date.xls. (Be sure you open the correct file. There is a similarly named file named Dates.xls, which is used in the next section.) 

When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change 37625 to a normal date?

The way Excel treats calendar dates is sometimes confusing to the novice. The key is understanding that Excel can display a date in a variety of month-dayyear formats, or it can display a date in serial format. A date in serial format, such as 37625, is simply a positive integer that represents the number of days between the given date and January 1, 1900. Both the current date and January 1, 1900, are included in the count. For example, Excel displays January 3, 1900, in serial format as the number 3, which means there are three days between January 1, 1900, and January 3, 1900 (including both days).

  

Excel assumes that 1900 was a leap year containing 366 days. In reality, 1900 contained only 365 days.

Figure 7-1 shows the worksheet named Serial Format in the file Dates.xls. Suppose you are given the dates shown in cells D5:D14 in serial format. For example, the value 37622 in cell D5 indicates a date that is 37,622 days after January 1, 1900 (including both January 1, 1900, and the current day). To display these dates in month-day-year format, copy them to E5:E14. Select the cell range E5:E14, choose Format Cells, and then select the date format you want from the list shown in Figure 7-2. The dates in E5:E14 will now be displayed in date format, as you can see in Figure 7-1. If you want to format dates in the serial number format, select E5:E14 and then choose Format Cells General. 

                                          Figure 7-1: Use the Format Cells command to change dates from serial number format to month-day-year format. 

                                                   Figure 7-2: Reformatting a serial number to month-day-year format.

As I mentioned previously, simply changing the format of a cell in a date format to General will yield the date in serial format. Another way to obtain the date in serial format is to use the DATEVALUE function. Enclose any date in quotation marks, and this function will return the date's serial number. For example, cell I5 in the worksheet named Date Format contains the formula DATEVALUE("1/4/2003"). Excel yields 37625, which is the serial format for January 4, 2003.

                                                   Figure 7-3: You can use the DATEVALUE function to format a date in serial format. 

 

Can I enter a formula that automatically displays today's date? 

Displaying today's date with a formula is easy, as you can see by looking at the worksheet Date Format, shown in Figure 7-4. Entering the TODAY() function in a cell will display today's date. Of course, whenever you open the workbook, the cell displays the current date. If you update a worksheet every day and want the current date to be displayed, use TODAY(). 

                                          Figure 7-4: Use the TODAY function to display today's date. Use the WORKDAY and NETWORKDAYS functions to calculate dates a certain number of days from today.

How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays? 

The function WORKDAY(start_date, #days, [holidays]) displays the date that is the number of workdays indicated in #days (a workday is a nonweekend day) after a given start date. Holidays is an optional argument for the function that allows you to exclude from the calculation any dates that are listed in a cell range. Thus, entering the formula WORKDAY(C14,50) in cell D14 of the Date Format worksheet tells us that 3/14/2003 is 50 workdays (Monday-Friday dates) after 01/03/2003. If we believe that the only two holidays that matter are Martin Luther King Day and the 4th of July, we can change the formula to WORKDAY(C14,50,F17:F18). With this addition, Excel does not count 01/20/2003 in its calculations, making 03/17/2003 the 50th workday after 01/03/2003.

Important

To use the WORKDAY function, you must have the Analysis Toolpak installed. To install the toolpak, run Tools Add-Ins and select Analysis ToolPak and Analysis ToolPak VBA.

How do I determine the number of workdays between two dates?

The key to solving this problem is to use the NETWORKDAYS function. The syntax for this function is NETWORKDAYS(start_date, end_date, [holidays]), where holidays is an optional argument identifying a cell range that lists the dates you want to count as holidays. The NETWORKDAYS function returns the number of working days between start_date and end_date excluding weekends and any listed holidays. As an illustration of the NETWORKDAYS function, look at cell C18 in the Date Format worksheet, which contains the formula NETWORKDAYS(C14,C15). This formula yields the number of working days between 1/3/2003 and 8/4/2003, which turns out to be 152. The formula NETWORKDAYS(C14,C15,F17:F18), which is entered in cell C17 of the worksheet Date Format, yields the number of workdays between 1/3/2003 and 8/4/2003 excluding Martin Luther King Day and the 4th of July. The answer is 152 -2 = 150. The NETWORKDAYS function also requires installation of the Analysis Toolpak.

I have 500 different dates entered in an Excel worksheet. How do I write formulas that will extract from each date the month, year, day of the month, and day of the week?

The worksheet Date Format (see Figure 7-4) lists several dates in the cell range C5:C10. In C5 and C7:C9, I've used four different formats to display January 4, 2003. In columns D:G, I've extracted the year, month, day of the month, and day of the week for each date. By copying from D5 to D6:D9 the formula YEAR(C5), we extract the year for each date. By copying from E5 to E6:E9 the formula MONTH(C5), we extract the month (1 = January, 2 = February, and so on) part of each date. By copying from F5 to F6:F9 the formula DAY(C5), we extract the day of the month for each date. Finally, by copying from G5 to G6:G9 the formula WEEKDAY(C5,1), we extract the day of the week for each date. 

Note

When the last argument of the Weekday function is 1, 1 = Sunday, 2 = Monday, and so on. When the last argument is 2, 1 = Monday, 2 = Tuesday, and so on. When the last argument is 3, 0 = Monday, 1 = Tuesday, and so on.

Suppose you are given the year, month, and day of the month for a date. Is there an easy way to recover the actual date? The DATE function, whose arguments are DATE(year,month,day), will return the date with the given year, month, and day of the month. In the worksheet Date Format, copying from cell H5 to cells H6:H9 the formula DATE(D5,E5,F5) recovers the dates we started with.