Date-Related Functions
Excel has quite a few functions that work with dates. These functions are accessible by choosing Formulas 
Function Library 
Date & Time.
Table 16-4 summarizes the date-related functions available in Excel.
| Function | Description |
|---|---|
| DATE | Returns the serial number of a particular date |
| DATEVALUE | Converts a date in the form of text to a serial number |
| DAY | Converts a serial number to a day of the month |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year |
| EDATE[*] | Returns the serial number of the date that represents the indicated number of months before or after the start date |
| EOMONTH[*] | Returns the serial number of the last day of the month before or after a specified number of months |
| MONTH | Converts a serial number to a month |
| NETWORKDAYS[*] | Returns the number of whole work days between two dates |
| NOW | Returns the serial number of the current date and time |
| TODAY | Returns the serial number of today’s date |
| WEEKDAY | Converts a serial number to a day of the week |
| WEEKNUM[*] | Returns the week number in the year |
| WORKDAY[*] | Returns the serial number of the date before or after a specified number of workdays |
| YEAR | Converts a serial number to a year |
| YEARFRAC[*] | Returns the year fraction representing the number of whole days between start_date and end_date |
[*] In versions prior to Excel 2007, these functions are available only when the Analysis ToolPak add-in is installed.
Displaying the current date
The following function displays the current date in a cell:
=TODAY()
You can also display the date combined with text. The formula that follows, for example, displays text, such as Today is Monday, April 9, 2007.
="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")
It’s important to understand that the TODAY function is updated whenever the worksheet is calculated. For example, if you enter either of the preceding formulas into a worksheet, the formulas display the current date. But when you open the workbook tomorrow, they will display the current date (not the date when you entered the formula).
Tip
To enter a date stamp into a cell, press Ctrl+; (semicolon). This action enters the date directly into the cell and does not use a formula. Therefore, the date will not change.
Displaying any date
You can easily enter a date into a cell by simply typing it while using any of the date formats that Excel recognizes. You also can create a date by using the DATE function, which takes three arguments: the year, the month, and the day. The following formula, for example, returns a date comprised of the year in cell A1, the month in cell B1, and the day in cell C1:
=DATE(A1,B1,C1)
Note
The DATE function accepts invalid arguments and adjusts the result accordingly. For example, the following formula uses 13 as the month argument and returns January 1, 2008. The month argument is automatically translated as month 1 of the following year.
=DATE(2007,13,1)
Often, you’ll use the DATE function with other functions as arguments. For example, the formula that follows uses the YEAR and TODAY functions to return the date for Independence Day (July 4th) of the current year:
=DATE(YEAR(TODAY()),7,4)
The DATEVALUE function converts a text string that looks like a date into a date serial number. The following formula returns 39316, the date serial number for August 22, 2007:
=DATEVALUE("8/22/2007")To view the result of this formula as a date, you need to apply a date number format to the cell.
Caution
Be careful when using the DATEVALUE function. A text string that looks like a date in your country may not look like a date in another country. The preceding example works fine if your system is set for U.S. date formats, but it returns an error for other regional date formats because Excel is looking for the eighth day of the twenty-second month!
Generating a series of dates
Often, you want to insert a series of dates into a worksheet. For example, in tracking weekly sales, you may want to enter a series of dates, each separated by seven days. These dates will serve to identify the sales figures.
The most efficient way to enter a series of dates doesn’t require any formulas. Use the Excel AutoFill feature to insert a series of dates. Enter the first date and drag the cell’s fill handle while pressing and holding the right mouse button. Release the mouse button and select an option from the shortcut menu (see Figure 16-3)—either Fill Days, Fill Weekdays, Fill Months, or Fill Years.
The advantage of using formulas (instead of the AutoFill feature) to create a series of dates is that you can change the first date, and the others update automatically. You need to enter the starting date into a cell and then use formulas (copied down the column) to generate the additional dates.
The following examples assume that you entered the first date of the series into cell A1 and the formula into cell A2. You can then copy this formula down the column as many times as needed.
To generate a series of dates separated by seven days, use this formula:
=A1+7
To generate a series of dates separated by one month, use this formula:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
To generate a series of dates separated by one year, use this formula:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
To generate a series of weekdays only (no Saturdays or Sundays), use the formula that follows. This formula assumes that the date in cell A1 is not a weekend day.
=IF(WEEKDAY(A1)=6,A1+3,A1+1)
Converting a nondate string to a date
You may import data that contains dates coded as text strings. For example, the following text represents August 21, 2007 (a four-digit year followed by a two-digit month, followed by a two-digit day):
20070821
To convert this string to an actual date, you can use a formula, such as this one. (It assumes that the coded data is in cell A1.)
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
This formula uses text functions (LEFT, MID, and RIGHT) to extract the digits, and then it uses these extracted digits as arguments for the DATE function.
Calculating the number of days between two dates
A common type of date calculation determines the number of days between two dates. For example, you may have a financial worksheet that calculates interest earned on a deposit account. The interest earned depends on the number of days the account is open. If your sheet contains the open date and the close date for the account, you can calculate the number of days the account was open.
Because dates are stored as consecutive serial numbers, you can use simple subtraction to calculate the number of days between two dates. For example, if cells A1 and B1 both contain a date, the following formula returns the number of days between these dates:
=A1-B1
Excel automatically formats this formula cell as a date rather than as a numeric value. Therefore, you will need to change the number format so that the result is displayed as a nondate. If cell B1 contains a more recent date than the date in cell A1, the result will be negative.
Note
If this formula does not display the correct value, make sure that A1 and B1 both contain actual dates—not text that looks like a date.
Sometimes, calculating the difference between two days is more difficult. To demonstrate, consider the common fence-post analogy. If somebody asks you how many units make up a fence, you can respond with either of two answers: the number of fence posts or the number of gaps between the fence posts. The number of fence posts is always one more than the number of gaps between the posts.
To bring this analogy into the realm of dates, suppose that you start a sales promotion on February 1 and end the promotion on February 9. How many days was the promotion in effect? Subtracting February 1 from February 9 produces an answer of eight days. Actually, the promotion lasted nine days. In this case, the correct answer involves counting the fence posts, not the gaps. The formula to calculate the length of the promotion (assuming that you have appropriately named cells) appears like this:
=EndDay-StartDay+1
Calculating the number of work days between two dates
When calculating the difference between two dates, you may want to exclude weekends and holidays. For example, you may need to know how many business days fall in the month of November. This calculation should exclude Saturdays, Sundays, and holidays. The NETWORKDAYS function can help out.
New Feature
In versions prior to Excel 2007, the NETWORKDAYS function was available only when the Analysis ToolPak add-in was installed. The function is now part of Excel 2007.
The NETWORKDAYS function calculates the difference between two dates, excluding weekend days (Saturdays and Sundays). As an option, you can specify a range of cells that contain the dates of holidays, which are also excluded. Excel has absolutely no way of determining which days are holidays, so you must provide this information in a range.
Figure 16-4 shows a worksheet that calculates the work days between two dates. The range A2:A11 contains a list of holiday dates. The two formulas in column C calculate the work days between the dates in column A and column B. For example, the formula in cell C15 is:
=NETWORKDAYS(A15,B15,A2:A11)
Figure 16-4. Using the NETWORKDAYS function to calculate the number of working days between two dates.
This formula returns 4, which means that the seven-day period beginning with January 1 contains four work days. In other words, the calculation excludes one holiday, one Saturday, and one Sunday. The formula in cell C16 calculates the total number of work days in the year.
Offsetting a date using only work days
The WORKDAY function is the opposite of the NETWORKDAYS function. For example, if you start a project on January 4, and the project requires ten working days to complete, the WORKDAY function can calculate the date you will finish the project.
New Feature
In versions prior to Excel 2007, the WORKDAY function was available only when the Analysis ToolPak add-in was installed. The function is now part of Excel 2007.
The following formula uses the WORKDAY function to determine the date that is 10 working days from January 4, 2008. A working day consists of a week day (Monday through Friday).
=WORKDAY("1/4/2008",10)The formula returns a date serial number, which must be formatted as a date. The result is January 18, 2008 (four weekend dates fall between January 4 and January 18).
Caution
The preceding formula may return a different result, depending on your regional date setting. (The hard-coded date may be interpreted as April 1, 2008.) A better formula is:
=WORKDAY(DATE(2008,1,4),10)
The second argument for the WORKDAY function can be negative. And, as with the NETWORKDAYS function, the WORKDAY function accepts an optional third argument (a reference to a range that contains a list of holiday dates).
Calculating the number of years between two dates
The following formula calculates the number of years between two dates. This formula assumes that cells A1 and B1 both contain dates:
=YEAR(A1)-YEAR(B1)
This formula uses the YEAR function to extract the year from each date and then subtracts one year from the other. If cell B1 contains a more recent date than the date in cell A1, the result will be negative.
Note that this function doesn’t calculate full years. For example, if cell A1 contains 12/31/2007 and cell B1 contains 01/01/2008, the formula returns a difference of one year, even though the dates differ by only one day. See the next section for another way to calculate the number of full years.
Calculating a person’s age
A person’s age indicates the number of full years that the person has been alive. The formula in the previous section (for calculating the number of years between two dates) won’t calculate this value correctly. You can use two other formulas, however, to calculate a person’s age.
The following formula returns the age of the person whose date of birth you enter into cell A1. This formula uses the YEARFRAC function.
=INT(YEARFRAC(TODAY(),A1,1))
New Feature
In versions prior to Excel 2007, the YEARFRAC function was available only when the Analysis ToolPak add-in was installed. The function is now part of Excel 2007.
The following formula uses the DATEDIF function to calculate an age. (See the sidebar, “Where’s the DATEDIF Function?”)
=DATEDIF(A1,TODAY(),"Y")
Determining the day of the year
January 1 is the first day of the year, and December 31 is the last day. But what about all those days in between? The following formula returns the day of the year for a date stored in cell A1:
=A1-DATE(YEAR(A1),1,0)
The following formula returns the number of days remaining in the year after a particular date (assumed to be in cell A1):
=DATE(YEAR(A1),12,31)-A1
When you enter either of these formulas, Excel applies date formatting to the cell. You need to apply a non-date number format to view the result as a number.
To convert a particular day of the year (for example, the ninetieth day of the year) to an actual date in a specified year, use the formula that follows. This formula assumes that the year is stored in cell A1 and the day of the year is stored in cell B1:
=DATE(A1,1,B1)
Determining the day of the week
The WEEKDAY function accepts a date argument and returns an integer between 1 and 7 that corresponds to the day of the week. The following formula, for example, returns 3 because the first day of the year 2008 falls on a Tuesday:
=WEEKDAY(DATE(2008,1,1))
Where’s the DATEDIF Function?One of Excel’s mysteries is the DATEDIF function. You may notice that this function does not appear in the drop-down function list for the Date & Time category, nor does it appear in the Insert Function dialog box. Therefore, when you use this function, you must always enter it manually. The DATEDIF function has its origins in Lotus 1-2-3, and apparently Excel provides it for compatibility purposes. For some reason, Microsoft wants to keep this function a secret. The function has been available since Excel 5, but Excel 2000 is the only version that ever documented it in its Help system. DATEDIF is a handy function that calculates the number of days, months, or years between two dates. The function takes three arguments: start_date, end_date, and a code that represents the time unit of interest. The following table displays valid codes for the third argument. (You must enclose the codes in quotation marks.)
The start_date argument must be earlier than the end_date argument, or the function returns an error. |
The WEEKDAY function uses an optional second argument that specifies the day numbering system for the result. If you specify 2 as the second argument, the function returns 1 for Monday, 2 for Tuesday, and so on. If you specify 3 as the second argument, the function returns 0 for Monday, 1 for Tuesday, and so on.
Tip
You can also determine the day of the week for a cell that contains a date by applying a custom number format. A cell that uses the following custom number format displays the day of the week, spelled out:
dddd
Determining the date of the most recent Sunday
You can use the following formula to return the date for the previous Sunday. If the current day is a Sunday, the formula returns the current date:
=TODAY()-MOD(TODAY()-1,7)
To modify this formula to find the date of a day other than Sunday, change the 1 to a different number between 2 (for Monday) and 7 (for Saturday).
Determining the first day of the week after a date
This next formula returns the specified day of the week that occurs after a particular date. For example, use this formula to determine the date of the first Monday after June 1, 2007. The formula assumes that cell A1 contains a date and cell A2 contains a number between 1 and 7 (1 for Sunday, 2 for Monday, and so on):
=A1+A2-WEEKDAY(A1)+(A2<WEEKDAY(A1))*7
If cell A1 contains June 1, 2007 (a Friday), and cell A2 contains 2 (for Monday), the formula returns June 4, 2007. This is the first Monday after June 1, 2004.
Determining the nth occurrence of a day of the week in a month
You may need a formula to determine the date for a particular occurrence of a week day. For example, suppose that your company payday falls on the second Friday of each month, and you need to determine the paydays for each month of the year. The following formula will make this type of calculation:
=DATE(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+ (A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7
The formula in this section assumes that:
Cell A1 contains a year.
Cell A2 contains a month.
Cell A3 contains a day number (1 for Sunday, 2 for Monday, and so on).
Cell A4 contains the occurrence number (for example, 2 to select the second occurrence of the weekday specified in cell A3).
If you use this formula to determine the date of the second Friday in November 2007, it returns November 11, 2007.
Note
If the value in cell A4 exceeds the number of the specified day in the month, the formula returns a date from a subsequent month. For example, if you attempt to determine the date of the fifth Friday in November 2007 (there is no such date), the formula returns the first Friday in December.
Calculating dates of holidays
Determining the date for a particular holiday can be tricky. Some, such as New Year’s Day and U.S. Independence Day are no-brainers because they always occur on the same date. For these kinds of holidays, you can simply use the DATE function. To enter New Year’s Day (which always falls on January 1) for a specific year in cell A1, you can enter this function:
=DATE(A1,1,1)
Other holidays are defined in terms of a particular occurrence of a particular week day in a particular month. For example, Labor Day falls on the first Monday in September.
Figure 16-5 shows a workbook with formulas that calculate the date for 10 U.S. holidays. The formulas, which reference the year in cell A1, are listed in the sections that follow.
New Year’s Day
This holiday always falls on January 1:
=DATE(A1,1,1)
Martin Luther King, Jr. Day
This holiday occurs on the third Monday in January. This formula calculates Martin Luther King, Jr. Day for the year in cell A1:
=DATE(A1,1,1)+IF(2<WEEKDAY(DATE(A1,1,1)),7-WEEKDAY (DATE(A1,1,1))+2,2-WEEKDAY(DATE(A1,1,1)))+((3-1)*7)
Presidents’ Day
Presidents’ Day occurs on the third Monday in February. This formula calculates Presidents’ Day for the year in cell A1:
=DATE(A1,2,1)+IF(2<WEEKDAY(DATE(A1,2,1)),7-WEEKDAY (DATE(A1,2,1))+2,2-WEEKDAY(DATE(A1,2,1)))+((3-1)*7)
Memorial Day
The last Monday in May is Memorial Day. This formula calculates Memorial Day for the year in cell A1:
=DATE(A1,6,1)+IF(2<WEEKDAY(DATE(A1,6,1)),7-WEEKDAY (DATE(A1,6,1))+2,2-WEEKDAY(DATE(A1,6,1)))+((1-1)*7)-7
Notice that this formula actually calculates the first Monday in June and then subtracts 7 from the result to return the last Monday in May.
Independence Day
This holiday always falls on July 4:
=DATE(A1,7,4)
Labor Day
Labor Day occurs on the first Monday in September. This formula calculates Labor Day for the year in cell A1:
=DATE(A1,9,1)+IF(2<WEEKDAY(DATE(A1,9,1)),7-WEEKDAY (DATE(A1,9,1))+2,2-WEEKDAY(DATE(A1,9,1)))+((1-1)*7)
Veterans Day
This holiday always falls on November 11:
=DATE(A1,11,11)
Columbus Day
This holiday occurs on the second Monday in October. This formula calculates Columbus Day for the year in cell A1:
=DATE(A1,10,1)+IF(2<WEEKDAY(DATE(A1,10,1)),7-WEEKDAY (DATE(A1,10,1))+2,2-WEEKDAY(DATE(A1,10,1)))+((2-1)*7)
Thanksgiving Day
Thanksgiving Day is celebrated on the fourth Thursday in November. This formula calculates Thanksgiving Day for the year in cell A1:
=DATE(A1,11,1)+IF(5<WEEKDAY(DATE(A1,11,1)),7-WEEKDAY (DATE(A1,11,1))+5,5-WEEKDAY(DATE(A1,11,1)))+((4-1)*7)
Christmas Day
This holiday always falls on December 25:
=DATE(A1,12,25)
Determining the last day of a month
To determine the date that corresponds to the last day of a month, you can use the DATE function. However, you need to increment the month by 1 and use a day value of 0. In other words, the “0th” day of the next month is the last day of the current month.
The following formula assumes that a date is stored in cell A1. The formula returns the date that corresponds to the last day of the month:
=DATE(YEAR(A1),MONTH(A1)+1,0)
You can use a variation of this formula to determine how many days comprise a specified month. The formula that follows returns an integer that corresponds to the number of days in the month for the date in cell A1:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
Determining whether a year is a leap year
To determine whether a particular year is a leap year, you can write a formula that determines whether the twenty-ninth day of February occurs in February or March. You can take advantage of the fact that Excel’s DATE function adjusts the result when you supply an invalid argument—for example, a day of 29 when February contains only 28 days.
The following formula returns TRUE if the year of the date in cell A1 is a leap year. Otherwise, it returns FALSE.
=IF(MONTH(DATE(YEAR(A1),2,29))=2,TRUE,FALSE)
Caution
This function returns the wrong result (TRUE) if the year is 1900. See the section “Excel’s leap year bug,” earlier in this chapter.
Determining a date’s quarter
For financial reports, you may find it useful to present information in terms of quarters. The following formula returns an integer between 1 and 4 that corresponds to the calendar quarter for the date in cell A1:
=ROUNDUP(MONTH(A1)/3,0)
This formula divides the month number by 3 and then rounds up the result.