Date-Related Functions
Excel has quite a few functions that work with dates. They are all listed under the Date & Time drop-down list in the Formulas 
Function Library group.
Table 6-5 summarizes the date-related functions available in Excel.
| Function | Description |
|---|---|
| DATE | Returns the serial number of a date given the year, month, and day |
| DATEDIF | Calculates the number of days, months, or years between two dates |
| DATEVALUE | Converts a date in the form of text to an actual date |
| DAY | Returns the day of the month for a given date |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year |
| EDATE | Returns the date that represents the indicated number of months before or after the start date |
| EOMONTH | Returns the date of the last day of the month before or after a specified number of months |
| MONTH | Returns the month for a given date. |
| NETWORKDAYS[*] | Returns the number of whole workdays between two dates |
| NOW | Returns the current date and time |
| TODAY | Returns today's date |
| WEEKDAY | Returns the day of the week (expressed as a number) for a date |
| WEEKNUM[*] | Returns the week number of the year for a date |
| WORKDAY[*] | Returns the date before or after a specified number of workdays |
| YEAR | Returns the year for a given date |
| YEARFRAC[*] | Returns the year fraction representing the number of whole days between two dates |
|
[*]Prior to Excel 2007, this function required the Analysis ToolPak add-in installed. | |
Open table as spreadsheet
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 Friday, September 14, 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 will display the current date. When you open the workbook tomorrow, though, they will display the current date for that day (not the date when you entered the formula).
| Tip | To enter a date stamp into a cell, press Ctrl+; (semicolon). This enters the date directly into the cell and does not use a formula. Therefore, the date will not change. |
Displaying Any Date
As explained earlier in this chapter, you can easily enter a date into a cell by simply typing it, using any of the date formats that Excel recognizes. You can also 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 comprising 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, this next 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 22nd month! |
Generating a Series of Dates
Often, you'll 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-just use Excel's AutoFill feature to insert the dates. Enter the first date and then drag the cell's fill handle while pressing the right mouse button. (Right-drag the cell's fill handle.) Release the mouse button and select an option from the shortcut menu (see Figure 6-2).
Figure 6-2: Using Excel's AutoFill feature to create a series of dates.
The advantage of using formulas (rather than the AutoFill feature) to create a series of dates is that you can change the first date, and the others will 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 Non-Date 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, which assumes the coded date 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 uses these extracted digits as arguments for the DATE function.
| Cross Ref | Refer to Chapter 5 for more information about using formulas to manipulate text. |
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 how many days that 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 store 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 will automatically format this formula cell as a date rather than a numeric value. Therefore, you will need to change the number format so 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 dates. |
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 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. However, the promotion actually lasted nine days. In this case, the correct answer involves counting the fence posts, as it were, and not the gaps. The formula to calculate the length of the promotion (assuming 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. Using the NETWORKDAYS function can help.
| Note | The NETWORKDAYS function has a very misleading name. This function has nothing to do with networks or networking. Rather, it calculates the net number of workdays between two dates. |
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 6-3 shows a worksheet that calculates the workdays between two dates. The range A2:A11 contains a list of holiday dates. The formulas in column C calculate the workdays between the dates in column A and column B. For example, the formula in cell C15 is
Figure 6-3: Using the NETWORKDAYS function to calculate the number of working days between two dates.
=NETWORKDAYS(A15,B15,A2:A11)
This formula returns 4, which means that the seven-day period beginning with January 1 contains four workdays. In other words, the calculation excludes one holiday, one Saturday, and one Sunday. The formula in cell C16 calculates the total number of workdays in the year.
| On the CD | This workbook, |
work days.xlsx
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.
The following formula uses the WORKDAY function to determine the date ten working days from January 8, 2007. A working day is a weekday (Monday through Friday).
=WORKDAY("1/8/2007",10) The formula returns January 20, 2007; two weekend days fall between January 8 and January 20. Make sure that this formula cell is formatted to display a date format.
| Caution | The preceding formula may return a different result, depending on your regional date setting. (The hard-coded date may be interpreted as August 1, 2007.) A better formula is |
=WORKDAY(DATE(2007,1,8),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.
You can also use the YEARFRAC function to calculate the number of years between two dates. This function returns the number of years, including partial years. For example
=YEARFRAC(A1,B1,1)
Because the YEARFRAC function is often used for financial applications, it uses an optional third argument that represents the "basis" for the year (for example, a 360-day year). A third argument of 1 indicates an actual year.
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.
 |
In several places throughout this chapter, I refer to the DATEDIF function. You may notice that this function does not appear in the Insert Function dialog box, is not listed in the Date & Time drop-down, and does not appear in the Formula AutoComplete list. Therefore, to 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. You won't even find the DATEDIF function in the Help files although it's available in all Excel versions. Strangely, DATEDIF made an appearance in the Excel 2000 Help files but hasn't been seen since.
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.
| Unit Code | Returns |
|---|---|
| "y" | The number of complete years in the period. |
| "m" | The number of complete months in the period. |
| "d" | The number of days in the period. |
| "md" | The difference between the days in start_date and end_date. The months and years of the dates are ignored. |
| "ym" | The difference between the months in start_date and end_date. The days and years of the dates are ignored. |
| "yd" | The difference between the days of start_date and end_date. The years of the dates are ignored. |
The start_date argument must be earlier than the end_date argument, or the function returns an error.
|
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))
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 of 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 day argument supplied is zero, calling for the "0th" day of the first month. The DATE function interprets this as the day before the first day, or December 31 of the previous year in this example. Similarly, negative numbers can be supplied for the day argument.
The following formula returns the number of days remaining in the year from a particular date (assumed to be in cell A1):
=DATE(YEAR(A1),12,31)-A1
To convert a particular day of the year (for example, the 90th 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 that 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 2 because the first day of the year 2007 falls on a Monday:
=WEEKDAY(DATE(2007,1,1))
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. (You will need to format the cell to display as a 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 that 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 and cell A2 contains 2 (for Monday), the formula returns June 4, 2007. This is the first Monday after June 1, 2007 (which is a Friday).
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 weekday. For example, suppose 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 June 2007, it returns June 8, 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 sixth Friday in June 2007 (there is no such date), the formula returns the first Friday in July. |
Counting the Occurrences of a Day of the Week
You can use the following formula to count the number of occurrences of a particular day of the week for a specified month. It assumes that cell A1 contains a date and that cell B1 contains a day number (1 for Sunday, 2 for Monday, and so on). The formula is an array formula, so you must enter it by using Ctrl+Shift+Enter.
{=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:"& DAY(DATE(YEAR(A1),MONTH(A1)+1,0))))))=B1)*1)} If cell A1 contains the date January 8, 2007 and cell B1 contains the value 3 (for Tuesday), the formula returns 5, which reveals that January 2007 contains five Tuesdays.
The preceding array formula calculates the year and month by using the YEAR and MONTH functions. You can simplify the formula a bit if you store the year and month in separate cells. The following formula (also an array formula) assumes that the year appears in cell A1, the month in cell A2, and the day number in cell B1:
{=SUM((WEEKDAY(DATE(A1,A2,ROW(INDIRECT("1:"& DAY(DATE(A1,A2+1,0))))))=B1)*1)} | Cross Ref | Refer to Chapters 14 and 15 for more information about array formulas. |
Figure 6-4 shows this formula used in a worksheet. In this case, the formula uses mixed cell references so you can copy it. For example, the formula in cell C3 is
Figure 6-4: Calculating the number of each weekday in each month of a year.
{=SUM((WEEKDAY(DATE($B$2,$A3,ROW(INDIRECT("1:"& DAY(DATE($B$2,$A3+1,0))))))=C$1)*1)} Additional formulas use the SUM function to calculate the number of days per month (column J) and the number of each weekday in the year (row 15).
| On the CD | The workbook shown in Figure 6-4, |
Expressing a Date as an Ordinal Number
You may want to express the day portion of a date as an ordinal number. For example, you can display 4/16/2007 as April 16th, 2007. The following formula expresses the date in cell A1 as an ordinal date:
=TEXT(A1,"mmmm ")&DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, "th",IF(MOD(DAY(A1),10)=1,"st",IF(MOD(DAY(A1),10)=2,"nd",IF(MOD(DAY(A1),10)=3, "rd","th"))))&TEXT(A1,", yyyy")
| Caution | The result of this formula is text, not an actual date. |
The following formula shows a variation that expresses the date in cell A1 in day-month- year format. For example, 4/16/2007 would appear as 16th April, 2007. Again, the result of this formula represents text, not an actual date.
=DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, "th", IF(MOD(DAY(A1),10)=1, "st",IF(MOD(DAY(A1),10)=2,"nd", IF(MOD(DAY(A1),10)=3, "rd","th"))))& " " &TEXT(A1,"mmmm, yyyy")
| On the CD | The companion CD-ROM contains the workbook |
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, which I covered earlier in this chapter. 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 weekday in a particular month. For example, Labor Day in the United States falls on the first Monday in September.
Figure 6-5 shows a workbook with formulas to calculate the date for ten U.S. holidays. The formulas reference the year in cell A1. Notice that because New Year's Day, Independence Day, Veterans Day, and Christmas Day all fall on the same days of the year, their dates can be calculated by using the simple DATE function.
Figure 6-5: Using formulas to determine the date for various holidays.
| On the CD | The workbook shown in Figure 6-5, |
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)
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)
VETERANS DAY
This holiday always falls on November 11:
=DATE(A1,11,11)
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)
|
You'll notice that I omitted Easter from the previous section. Easter is an unusual holiday because its date is determined based on the phase of the moon and not by the calendar. Because of this, determining when Easter occurs proves a bit of a challenge.
Hans Herber, an Excel master in Germany, once sponsored an Easter formula contest at his Web site. The goal was to create the shortest formula possible that correctly determined the date of Easter for the years 1900 through 2078.
Twenty formulas were submitted, ranging in length from 44 characters up to 154 characters. Some of these formulas, however, work only with European date settings. The following formula, submitted by Thomas Jansen, is the shortest formula that works with any date setting. This formula returns the date for Easter and assumes that the year is stored in cell A1:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 Please don't ask me to explain this formula. I haven't a clue!
|
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 zero (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 make up 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 29th 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 Excel Leap Year Bug," earlier in this chapter. |
Determining a Date's Quarter
For financial reports, you might 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.
Converting a Year to Roman Numerals
Fans of old movies will like this one. The following formula converts the year 1945 to Roman numerals: MCMXLV:
=ROMAN(1945)
This function returns a text string, so you can't perform any calculations using the result. Unfortunately, Excel doesn't provide a function to convert Roman numerals back to normal numbers.
EAN: 2147483647
Pages: 212