Chapter 6: 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 it to a normal date?

• Can I use a formula to automatically display 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 an Excel worksheet. How do I write formulas can I use to extract from each date the month, year, day of the month, and day of the week?

• My business has purchased and sold machines. For some, I have the date the machine was purchased and the date the machine was sold. Can I easily determine how many months we kept each machine?

To illustrate the most commonly used month-day-year formats in Microsoft Office Excel 2007, 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

If you enter only two digits to represent a year, and the digits are 30 or higher, Excel assumes the digits represent years in the twentieth century; if the digits are lower than 30, Excel assumes they represent years in the nineteenth 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 Dates.xlsx.

• 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 it 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-day-year 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).

  Note 

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

• Figure 6-1 shows the worksheet named Serial Format in the file Dates.xlsx. 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, right-click the selection, and choose Format Cells. Now select the date format you want from the list shown in Figure 6-2. The dates in E5:E14 will be displayed in date format, as you can see in Figure 6-1. If you want to format dates in the serial number format, select E5:E14, right-click the selection, and choose Format Cells General.

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

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

• Simply changing the date format of a cell to General will yield the date in serial format. Another way to obtain the date in serial format is to use the DATEVALUE function, and enclose the date in quotation marks. For example, in the Date Format worksheet of file Dates.xlsx, cell I5 contains the formula DATEVALUE("1/4/2003"). Excel yields 37625, which is the serial format for January 4, 2003.

• Can I use a formula to automatically display today’s date?

• Displaying today’s date with a formula is easy, as you can see by looking at cell C13 of the Date Format worksheet shown in Figure 6-3. 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, but if you update a worksheet every day and want to display the current date, use TODAY().

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

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

• The function WORKDAY(start_date,#days,[holidays]) displays the date that is the number of workdays indicated by #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 after 01/03/2003. If we believe that the only two holidays that matter are Martin Luther King Day and Independence Day, 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. We note that instead of referring to the holidays in other cells, they may be directly entered in the WORKDAY formula with the serial number of each holiday enclosed in { }. For example, WORKDAY(38500,10,{38600,38680,38711}) would find the tenth workday after the date with serial number 38500, ignoring Labor Day, Thanksgiving, and Christmas of 2005.

• 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 is 152. The formula NETWORK-DAYS(C14,C15,F17:F18) in cell C17 yields the number of workdays between 1/3/2003 and 8/4/ 2003 excluding Martin Luther King Day and Independence Day. The answer is 152–2=150.

• 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 Date Format worksheet (see Figure 6-3) 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:D10 the formula YEAR(B5), we extract the year for each date. By copying from E5 to E6:E10 the formula MONTH(B5), we extract the month (1=January, 2=February, and so on) portion of each date. By copying from F5 to F6:F10 the formula DAY(B5), we extract the day of the month for each date. Finally, by copying from G5 to G6:G10 the formula WEEKDAY(B5,1), we extract the day of the week for each date.

• When the last argument of the Weekday function is 1, then 1=Sunday, 2=Monday, and so on. When the last argument is 2, then 1=Monday, 2=Tuesday, and so on. When the last argument is 3, then 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 Date Format worksheet, copying from cell H5 to cells H6:H10 the formula DATE(D5,E5,F5) recovers the dates we started with.

• My business has purchased and sold machines. For some, I have the date the machine was purchased and the date the machine was sold. Can I easily determine how many months we kept each machine?

• The DATEDIF function can easily determine the number of complete years, months, or days between two dates. In file Datedif.xlsx (see Figure 6-4), we see that a machine was bought on 10/15/2006 and will be sold on 4/10/2008. How many complete years, months, or days was the machine kept? The syntax of the DATEDIF function is DATEDIF(startdate,enddate,time unit). If unit is written as “y,” we get number of complete years between the start and end dates; if unit is written as “m,” we get the number of complete months between the start and end dates; if unit is written as “d,” we get the number of complete days between the start and end dates. Thus, entering DATEDIF(D4,D5,"y") in cell D6 shows that the machine was kept for one full year. Entering the formula DATEDIF(D4,D5,"m") in cell D7 shows the machine was kept for 17 complete months. Entering the formula DATEDIF(D4,D5,"d") in cell D7 shows the machine was kept for 543 complete days.

  
  Figure 6-4: Using the DATEDIF function.