Section 10.4. Date and Time Functions | Excel 2010: The Missing Manual

10.4. Date and Time Functions

You aren't limited to arithmetic operators for manipulating dates and times. Excel also provides some invaluable ready-to-use functions to help you do things like identify the current date, extract just part of the date, and perform calculations with a date.

10.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time

The TODAY( ) function automatically retrieves the current date. It doesn't require any arguments because it simply examines your computer's internal clock. The TODAY( ) function is extremely useful for creating spreadsheets that continuously update themselves (sometimes called dynamic spreadsheets ). For example, you could create a formula that determines the number of days a payment is overdue. Here's an example:

 =TODAY( )-A1

This formula assumes that cell A1 contains the date a payment was due. As a result, it calculates the number of days between the two dates, which shows up as an ordinary number (like 14 for a payment that's two weeks late). Remember, you'll need to display the result as an ordinary number (representing the number of days), not as a date.

The NOW( ) function is similar to the TODAY( ) function, except it retrieves the current date along with the current time. If you use NOW( ) to display a value in a cell that doesn't have any special formatting applied, Excel will use a custom format that shows the date and time (listed in the 24- hour format; for example, 1/5/2005 19:06.)

You can use other formats to hide some of this information. For example, a cell you've formatted using the custom number format [h]:mm:ss means you'll see only the time portion 19:06, not the date information. (For more information about custom number formats, see Section 4.1.4.) The following formula shows one handy way to calculate the current time by completely removing the date:

 =NOW( )-TODAY( )

Remember to format the displayed result as a time value (Section 4.1.2).

Tip: Excel recalculates both TODAY( ) and NOW( ) when you reopen a spreadsheet or when you explicitly refresh the worksheet by pressing F9. But, sometimes, you might want to insert the current date and make sure Excel never updates it again. In these cases, you can use the TODAY( ) or NOW( ) function, but you need to convert the result into a static date. Hit F2 to activate edit mode for the cell, then press F9 to replace the cell with the calculated result. At this point, you'll see the serial number appear in the cell. Finally, press Enter to commit this value.

10.4.2. DATE( ) and TIME( ): Dates and Times in Calculations

As you've learned over the last few chapters, formulas can include cell references and literals , or fixed values. So far, you've seen examples of literal numbers (like 1+1) and literal text. Unfortunately, it's not as easy to insert literal date values into a formula. Although Excel makes it easy to type a single date into a cell (just use a format like 1/7/2005), you can't use the same syntax inside a formula.

For example, if you enter the formula =2/9/2005-1/14/2005 , Excel won't recognize this as an attempt to calculate the number of days between January 14, 2005 and February 9, 2005. Instead, Excel sees the whole chain of numbers and backward slashes , as well as the minus sign, as one long arithmetic operation involving division. And while you can enter a literal date value by typing in the corresponding serial number (as explained on Section 2.1.1.3), this technique is confusing and error proneunless you're keeping track of the number of days that have elapsed since January 1, 1900.

An easier way to enter literal date values is to use the DATE( ) function. The DATE( ) function is a quick and easy way to insert a date into a formula. DATE( ) accepts three numbers, each of which represents a different component of the date. The function gives you the date's serial number. Here's what it looks like:

 DATE(year, month, day)

In other words, if you enter this formula into a cell:

 =DATE(2005, 1, 1)

Excel displays the date 1/1/2005 . However, you need to watch the formatting you use for the cell. If you use the DATE( ) function in a cell that uses the General number format, Excel automatically adjusts the cell to use the Date number format. However, if you've already applied a specific non-General number format to the cell (like Currency), the DATE( ) function won't change the number format. That means you'll see the familiar date serial number (in this example, 38353 ), which isn't what you want.

Note: For maximum clarity, you should always use a four-digit year with the DATE( ) function. If you enter a two-digit year, Excel assumes you're referring to a year that begins with 19, meaning that 04 will become 1904, not 2004.

Similarly, you can use the DATE( ) function to create a formula that combines date literals and cell references. Here's an example that determines the number of days between 12/30/2001 and another date (in this example, the one contained in cell A1):

 =A1-DATE(2005, 1, 1)

Note: You can use the DATE( ) function to take a year number, month number, and day number from three different cells and calculate the resulting date. However, it's unlikely that you'll find a worksheet that splits date information into more than one cell.

The TIME( ) function performs similar magic with time values. It requires three components : an hour, minute, and second number. If you want, you can use fractional seconds to indicate milliseconds . With this function, you have to enter hours using a 24-hour clock. Here's what the function looks like:

 TIME(hour, minute, second)

For example, the following function calculates how much time exists between now and a deadline at 9:00 PM (21:00):

 =(TIME(21,0,0)-(NOW( )-TODAY( )))*24

As you'll see below, constructing this formula requires several steps. (For a refresher on the precedence rules Excel uses when determining order of operations, see Section 7.1.1).

First, the formula creates the time literal for 9:00 PM using the TIME( ) function.
Next , the formula determines the current time using the calculation NOW( )-DATE( ). This works because it takes the current date and time (the result of the NOW( ) function), and removes the current date portion (the result of the DATE( ) function).
Finally, it calculates the difference between 9:00 PM and the current time. The result of this calculation is, like all time values, expressed as a fractional number of days. Consequently, the formula multiplies this number by 24 to display the final result in a format (hours) that humans can more easily understand.

10.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations

The DAY( ), MONTH( ), and YEAR( ) functions are great when you want to calculate dates that fall before or after a certain date you already know. All three functions take a date argument and give you a number representing the day (1 to 31), the month (1 to 12), or the year (1900 to 9999), respectively. For example, if you place the date 1/1/2005 in cell A1, the following formula displays a result of 2005:

 =YEAR(A1)

If you want to use the DAY( ), MONTH( ), and YEAR( ) functions with date literals, you need to use these functions in conjunction with the DATE( ) function. For example, the following formula displays the number 5:

 =MONTH(DATE(2005,05,20))

Note: The DAY( ), MONTH( ), and YEAR( ) functions all require that you put their arguments in the form of a date's underlying serial value, so a formula like =YEAR(1/1/2005) won't give you the value you're presumably looking for (i.e., 2005). Instead, Excel calculates the result of the division 1/1/2005 (which is 0.0005), and then passes that to the YEAR( ) function, which has no real meaning. To correct this problem, use the DATE( ) function to create the serial number for the date you need.

UP TO SPEED
Excel's Intentional Date Bug

Most software programmers spend their waking hours struggling to eradicate bugs from their software. So it might come as a bit of a surprise that Microsoft programmers have deliberately inserted at least one bug into all versions of Excel.

This bug is affectionately known as the date leap year bug . As leap year aficionados know, the year 1900 is not a leap year. According to Excel, however, the year 1900 is a leap year. So if you enter the following formula, Excel won't complain, even though February 29 ^th , 1900 is not an actual date:

 =DATE(1900,2,29)

The reason for this error is compatibility. When Microsoft released Excel, Lotus 1-2-3 was by far the most popular spreadsheet software. Lotus 1-2-3 contained this bug, and by emulating it, Excel could use the same date numbering system, enhancing compatibility.

This bug also means that the days of the week prior to March 1, 1900 are incorrect, and that date calculations that stretch over this date will also be off by one day.

It's unlikely that Microsoft will ever fix this problem. Most conceivable fixes would change the internal numbering for every date after February 28, potentially causing problems with every existing spreadsheet that uses dates. Fortunately, this problem is also fairly benign because few spreadsheets use dates before February 29 ^th , 1900.

The DAY( ), MONTH( ), and YEAR( ) functions really shine when you need to take an existing date and move it forward or backward a set number of days, months, or years . For example, say that, given any date, you want to know what the date in two weeks will be. You could use the DAY( ), MONTH( ), and YEAR( ) functions in conjunction with the DATE( ) function to find out. Here's the formula you'd use ( assuming your base date was in cell A1):

 =DATE(YEAR(A1), MONTH(A1), DAY(A1)+14)

Here's how this formula breaks down, assuming that cell A1 contains the date 5/14/2005. The YEAR( ) and MONTH( ) functions both generate pretty straightforward results for the DATE( ) function's first two arguments: 2005 and 5, respectively. The DAY( ) function gives you a value of 14, which, when added to 14, results in 28, which serves as the third argument in the DATE( ) function. The final result of the formula, therefore, is 5/28/2005.

Impressively, Excel handles these calculations correctly even if the DAY argument is greater than the number of days in the month. For example, if you pass 33 as the third argument to the DATE( ) function, and the current month has only 31 days, Excel simply rolls over to the next month. (Excel knows how many days are in every month.) The DATE( ) function performs a similar trick to increment the year if the second argument you use is greater than 12.

Note: As you might expect, the TIME( ) function, which uses three arguments (hour, minute, second), works similarly. Namely, if you try to pass the function an hour value that is 24 or greater, or a number value that is 60 or greater, TIME( ) automatically rolls these values over. Thus, =TIME(25, 10, 00) results in 1:10 AM.

These DAY( ), MONTH( ), and YEAR( ) functions are also useful for building conditions. For example, you can use MONTH( ) to check if a given date falls on a specific month or day, regardless of the year. The following formula shows a simple example that displays the heading "First of the month" when the day component of the current date is 1. In all other cases, Excel will leave the cell blank.

 =IF(DAY(TODAY( ))=1,"First of the month")

For much more on how to use conditional logic, see Chapter 12.

10.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations

The HOUR( ), MINUTE( ), and SECOND( ) functions work the same way as the DAY( ), MONTH( ), and YEAR( ) functions, except they each generate a number representing the number of hours, minutes, or seconds in a given time. The hour component always uses a 24-hour clock. For example, if you type 9:30 PM into cell A1 (which Excel stores as the serial number 0.89583), the following formula displays the number 21:

 =HOUR(A1)

And this function shows the minute component, which is the number 30:

 =MINUTE(A1)

10.4.5. WEEKDAY( ): Determining the Day of the Week

The WEEKDAY( ) function takes a date, and returns a number that represents which day of the week that date falls on. For example, if the date occurs on Sunday, the number is 1, on Monday it's 2, right up through Saturday, which is 7. If you need this sort of information, the WEEKDAY( ) function is indispensable .

Here's what the function looks like:

 WEEKDAY(date, [return-type])

The first argument in the WEEKDAY( ) function is the date serial number. The WEEKDAY( ) function also accepts an optional second parameter, which you can use to change the numbering system. The second parameter can take one of three preset values: 1, 2, or 3. If you specify number 1, Excel uses the default numbering system described above. Number 2 instructs Excel to start counting at 1 with Monday and end at 7 with Sunday. If you specify number 3, Excel starts counting at 0 with Monday and ends at 6 with Sunday.

Here's a sample formula that returns 7, indicating that January 1, 2005 occurs on a Saturday:

 =WEEKDAY(DATE(2005,1,1))

It's quite easy to change things if you prefer to show the day's name rather than its number. You simply need a little help from the TEXT( ) function (Section 10.1.7), which can convert a number to any number format. In this case, you want to use a custom date format that shows the day name . Here's how:

 =TEXT(WEEKDAY(DATE(2005,1,1)), "dddd")

Here, Excel will show the full day name ( Saturday ). You can also tweak the format string to show the day's three-letter abbreviation (like Sat ).

 =TEXT(WEEKDAY(DATE(2005,1,1)), "ddd")

10.4.6. DATEDIF( ): Calculating the Difference Between Dates

You've already learned how to use the DATE( ) function to subtract one date from another and display the results in terms of days. But what if you want to calculate the difference in years or months? You could try and break the date up into components using the DAY( ), MONTH( ), and YEAR( ) functions, but these types of calculations can get surprisingly complicated (as shown in the box on Sidebar 10.4). A better option is to use Excel's little-known DATEDIF( ) function. Despite this being a useful gem for many date calculations, Excel's own Help tool neglects to cover this function.

DATEDIF( ) accepts three arguments. The first two are the dates that you want to find the difference between. The smaller (older) date goes first, and the other date comes second. The third argument is a string that indicates the way you want to measure the interval, such as in days, months, years, and so on. Table 10-3 explains your options. Remember, as with all string arguments, you need to put quotation marks around whatever value you choose. The formula looks like this:

 DATEDIF(start_date, end_date, interval_type)

Note: Remember, you can't use literal dates in any formula (ever). Instead, use tools like the DATE( ) function to first transform your date into the type of number you can use inside a formula.

Table 10-3. Interval Strings for DATEDIF( )

Code	Meaning	Description
"m"	Months	The number of complete months between the two dates.
"d"	Days	The number of days between the two dates.
"y"	Years	The number of complete years between the two dates.
"ym"	Months Excluding Years	The number of months between the two dates, as if they were in the same year.
"yd"	Days Excluding Years	The number of days between the two dates, as if they were in the same year.
"md"	Days Excluding Months and Years	The number of days between the two dates, as if they were in the same month and the same year.

FREQUENTLY ASKED QUESTION
Solving the DATEDIF( ) Mystery

Why isn't DATEDIF( ) mentioned in Excel's Help?

DATEDIF( ) is an Excel oddity. It's used by Excel gurus and unknown by just about everyone else. But DATEDIF( ) isn't anything newin fact, it was introduced in Excel 5.0. Microsoft even documented the DATEDIF( ) function in Excel 2000, but the documentation was removed in later versions.

The DATEDIF( ) function has its origins in Lotus 1-2-3. Apparently, Microsoft included it in Excel for compatibility purposes. Microsoft won't say why it has removed the documentation, but it's most likely due to a legal issue. On the other hand, because it has a similar name to the built-in VBA (Visual Basic for Applications) function DateDiff( ), it might create needless confusion. Either way, DATEDIF( ) is unlikely to ever disappear from Excel because it's used in countless spreadsheets to fill a gap that other date functions don't.

If you're still curious , you can read some unofficial DATEDIF( ) documentation at www.cpearson.com/excel/datedif.htm, or you can check out other Excel oddities at http://j-walk.com/ss/excel/odd.

For example, here's how to calculate the number of months that separate a date in the future (stored in cell A1) from the current date:

 =DATEDIF(TODAY( ), A1, "m")

Remember that when using the DATEDIF( ) function with the "m" argument, you're counting complete months. That means Excel counts the interval from, say, January 6, 2005 to February 6, 2005, as one month, but the interval from January 6, 2005 to February 5, 2005, is still a day shy, and Excel therefore counts it as zero months. Usually, this is the behavior you want. However, you do have another, somewhat more complicated option, if you want intervals like January 6-February 5 to register as one month: use the YEAR( ) and MONTH( ) functions.

For example, here's the DATEDIF( ) approach (which has a result of 0):

 =DATEDIF(DATE(2005,1,6), DATE(2005,2,5), "m")

And here's the YEAR( ) and MONTH( ) approach, which has the result of 1 (it works by subtracting the one month number from the other):

 =MONTH(DATE(2005,2,5))-MONTH(DATE(2005,1,6))

And here's a revised approach that works with dates that aren't in the same year:

 =(YEAR(DATE(2006,2,5))-YEAR(DATE(2005,1,6)))*12 +  MONTH(DATE(2006,2,5))-MONTH(DATE(2005,1,6))

Although this formula looks more complicated at first glance, it's really not that difficult. The reason it's so long is because Excel calculates the year and month components separately. Once you find the difference in year numbers, that number is multiplied by 12 and added to the month component. You then end up with the total number of months.

Unfortunately, this formula assumes that every year is 365 days, which neglects leap years. It's likely that this formula will be right most of the time, but will fail in the days just before or after a person's birthday.

POWER USERS' CLINIC
Getting a Birthday Right

Now that you've learned about a wide variety of Excel date functions, it probably seems like it wouldn't be very difficult to calculate a person's age based on the current date and his or her birth date. But, in fact, it can be surprisingly tricky. Here's how to get it right.

One approach is to use Excel's support for date subtraction. The following formula is a good first try (assuming the birthday is stored in cell A1):

 =INT((NOW( )-A1)/365)

The YEAR( ), MONTH( ), and DAY( ) functions don't provide a solutionthey all suffer from the same problem of not taking leap years into account. The only real solution is to use DATEDIF( ), which does take leap years into account. Here's the DATEDIF( ) formula you'd need:

 =DATEDIF(A1,NOW( ),"y")

You can even get a little fancier with the following formula, which uses some of the text manipulation techniques you saw earlier in this chapter. It displays a person's age in years, months, and days:

 =DATEDIF(A1,NOW( ),"y") & " years, " & DATEDIF(A1,NOW( ),"ym") & " months, " & DATEDIF(A1,NOW( ),"md") & " days"

10.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers

DATEVALUE( ) and TIMEVALUE( ) convert dates and times that Excel has stored as text into serial date numbers. Of course, in order for this to work, the text must be interpretable as a date or time. This means if you type 1-1-2005 or January 1, 2005 into a cell, these functions would be able to convert these values, but if you entered something like 1st January, 05 or 1,1,2005 , you'd get an error message.

Note: Keep in mind that the DATEVALUE( ) and TIMEVALUE( ) functions don't change the formatting of the cell. Therefore, if you want to see something other than the date serial number, you'll need to choose a Date number format.

The rules Excel uses to convert a piece of text to a date with DATEVALUE( ) are the same ones that Excel uses for recognizing dates and time values when you enter them into a cell. To refresh your memory see the Section 2.1.1.3 on Section 2.1.1.3. The only difference is that DATEVALUE( ) and TIMEVALUE( ) ignore the initial apostrophe in a text value.

For example, you can type the following text in cell A1. Remember that the apostrophe tells Excel to treat this entry as text even though it's clearly recognizable as a date:

 '1/1/2004

Now another cell can perform a date calculation by first converting this text to a real date:

 =NOW( )-DATEVALUE(A1)

This formula calculates the number of days elapsed since January 1, 2004. Because the result is a number of days, you'll want to use the General number format (not a Date number format).

DATEVALUE( ) and TIMEVALUE( ) also work with literal text strings. That means you can use these functions as an alternative to DATE( ) and TIME( ) if you want to create a date or time literal. The difference? Whereas DATE( ) and TIME( ) create dates and times based on several numeric components you supply, DATEVALUE( ) and TIMEVALUE( ) create dates and times from string literals. Here's an example of TIMEVALUE( ) in action:

 =TIMEVALUE("19:30:00")

This formula converts a static piece of text into the time value 19:30:00 (which Excel represents internally as the fractional number 0.8125). The only drawback to using DATEVALUE( ) and TIMEVALUE( ) is that you'll run into trouble if Excel can't interpret the text you supply. For example, the text string January 1,2005 isn't formatted properly, because there's no space between the comma and the number 2005. This minor glitch is enough to completely stymie the DATEVALUE( ) function. It's much less likely that you'll make an error if you were relying on the DATE( ) function instead.

10.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates

DAYS360( ) gives you the number of days between two dates. Unlike simple date subtraction, however, DAYS360( ) assumes that every month has 30 days (giving the year a total of 360 days). So is this the kind of function you're supposed to use when following some radically alternative calendar system? Sort of. The only reason you'd use DAYS360( ) is if you were using an accounting system that performs its calculations based on 360-day years, which is common in some payroll systems. (Also, some systems calculate investments by assuming 12 months with 30 days each, for a total of 360 days.) Read on if you're still interested.

Here's what the function looks like:

 =DAYS360(start_date, end_date, [European_method])

The smaller (older) date goes first (unless you want a negative number). The second argument is the date following the first date.

The third argument in DAYS360( ) is an optional value that you can set to TRUE to use the European version of the DAYS360( ) calculation. The only difference is how the calculation deals with start or end dates that occur on the 31 ^st day of the month:

In the default U.S. (NASD, short for National Association of Securities Dealers) method, a starting day of 31 changes to 30. An ending day of 31 is handled differently depending on the start day. If the start day is 30 or 31, the end date changes to day 30 in the same month. If the start day is earlier than 30, the end date changes from 31 to the first day of the next month.
In the European method, both a starting and ending day of 31 change to 30.