Beginners often find that working with dates and times in Excel can be frustrating. To work with dates and times, you need a good understanding of how Excel handles time-based information. This chapter provides the information you need to create powerful formulas that manipulate dates and times.
The dates in this chapter correspond to the United States English date format: month/day/year. For example, the date 3/1/1952 refers to March 1, 1952, not January 3, 1952. I realize that this setup may seem illogical, but that’s the way Americans have been trained. I trust that the non-American readers of this book can make the adjustment.
This section presents a quick overview of how Excel deals with dates and times. It includes coverage of the Excel program’s date and time serial number system, and it offers tips for entering and formatting dates and times.
To Excel, a date is simply a number. More precisely, a date is a serial number that represents the number of days since the fictitious date of January 0, 1900. A serial number of 1 corresponds to January 1, 1900; a serial number of 2 corresponds to January 2, 1900, and so on. This system makes it possible to deal with dates in formulas. For example, you can create a formula to calculate the number of days between two dates (just subtract one from the other).
Excel support dates from January 1, 1900, through December 31, 9999 (serial number = 2,958,465).
You may wonder about January 0, 1900. This nondate (which corresponds to date serial number 0) is actually used to represent times that aren’t associated with a particular day. This nondate business becomes clear later in this chapter (see “Entering times”).
To view a date serial number as a date, you must format the cell as a date. Choose Home Number Number Format. This drop-down control provides you with two date formats. To select from additional date formats, see “Formatting dates and times,” later in this chapter.
You can enter a date directly as a serial number (if you know it), but more often, you enter a date using any of several recognized date formats. Excel automatically converts your entry into the corresponding date serial number (which it uses for calculations), and it also applies the default date format to the cell so that it displays as an actual date rather than as a cryptic serial number.
For example, if you need to enter June 18, 2007, you can simply enter the date by typing June 18, 2007 (or any of several different date formats). Excel interprets your entry and stores the value 39251, the date serial number for that date. It also applies the default date format so that the cell contents may not appear exactly as you typed them.
Depending on your regional settings, entering a date in a format, such as June 18, 2007, may be interpreted as a text string. In such a case, you’d need to enter the date in a format that corresponds to your regional settings, such as 18 June, 2007.
When you activate a cell that contains a date, the Formula bar shows the cell contents formatted by using the default date format—which corresponds to your system’s short date format. The Formula bar doesn’t display the date’s serial number. If you need to find out the serial number for a particular date, format the cell using a nondate number format.
To change the default date format, you need to change a system-wide setting. Access the Windows Control Panel and select Regional and Language Options. Then click the Customize button to display the Customize Regional Options dialog box. Select the Date tab. The item selected in the Short Date Format drop-down list box determines the default date format used by Excel. These instructions apply to Windows XP and may vary with other versions of Windows.
Table 13.1 shows a sampling of the date formats that Excel recognizes (using the U.S. settings). Results will vary if you use a different regional setting.
Table 13.1. Date Entry Formats Recognized by Excel
Entry | Excel’s Interpretation (U.S. Settings) |
---|---|
6-18-07 | June 18, 2007 |
6-18-2007 | June 18, 2007 |
6/18/07 | June 18, 2007 |
6/18/2007 | June 18, 2007 |
6-18/07 | June 18, 2007 |
June 18, 2007 | June 18, 2007 |
Jun 18 | June 18 of the current year |
June 18 | June 18 of the current year |
6/18 | June 18 of the current year |
6-18 | June 18 of the current year |
18-Jun-2007 | June 18, 2007 |
2007/6/18 | June 18, 2007 |
As you can see in Table 13.1, Excel is rather intelligent when it comes to recognizing dates entered into a cell. It’s not perfect, however. For example, Excel does not recognize any of the following entries as dates:
June 18 2007
Jun-18 2007
Jun-18/2007
Rather, it interprets these entries as text. If you plan to use dates in formulas, make sure that Excel can recognize the date you enter as a date; otherwise, the formulas that refer to these dates will produce incorrect results.
If you attempt to enter a date that lies outside of the supported date range, Excel interprets it as text. If you attempt to format a serial number that lies outside of the supported range as a date, the value displays as a series of hash marks (#########
).
When you need to work with time values, you simply extend the Excel date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2007, is 39234. Noon (halfway through the day) is represented internally as 39234.5.
The serial number equivalent of one minute is approximately 0.00069444. The formula that follows calculates this number by multiplying 24 hours by 60 minutes, and dividing the result into 1. The denominator consists of the number of minutes in a day (1,440).
=1/(24*60)
Similarly, the serial number equivalent of one second is approximately 0.00001157, obtained by the following formula: 1 divided by 24 hours times 60 minutes times 60 seconds. In this case, the denominator represents the number of seconds in a day (86,400).
=1/(24*60*60)
In Excel, the smallest unit of time is one one-thousandth of a second. The time serial number shown here represents 23:59:59.999 (or one one-thousandth of a second before midnight):
0.99999999
Table 13.2 shows various times of day along with each associated time serial numbers.
Table 13.2. Times of Day and Their Corresponding Serial Numbers
Time of Day | Time Serial Number |
---|---|
12:00:00 AM (midnight) | 0.00000000 |
1:30:00 AM | 0.06250000 |
3:00:00 AM | 0.12500000 |
4:30:00 AM | 0.18750000 |
6:00:00 AM | 0.25000000 |
7:30:00 AM | 0.31250000 |
9:00:00 AM | 0.37500000 |
10:30:00 AM | 0.43750000 |
12:00:00 PM (noon) | 0.50000000 |
1:30:00 PM | 0.56250000 |
3:00:00 PM | 0.62500000 |
4:30:00 PM | 0.68750000 |
6:00:00 PM | 0.75000000 |
7:30:00 PM | 0.81250000 |
9:00:00 PM | 0.87500000 |
10:30:00 PM | 0.93750000 |
As with entering dates, you normally don’t have to worry about the actual time serial numbers. Just enter the time into a cell using a recognized format. Table 13.3 shows some examples of time formats that Excel recognizes.
Because the preceding samples don’t have a specific day associated with them, Excel (by default) uses a date serial number of 0, which corresponds to the nonday January 0, 1900. Often, you’ll want to combine a date and time. Do so by using a recognized date-entry format, followed by a space, and then a recognized time-entry format. For example, if you enter 6/18/2007 11:30 in a cell, Excel interprets it as 11:30 a.m. on June 18, 2007. Its date/time serial number is 39251.4791666667.
When you enter a time that exceeds 24 hours, the associated date for the time increments accordingly. For example, if you enter 25:00:00 into a cell, it’s interpreted as 1:00 AM on January 1, 1900. The day part of the entry increments because the time exceeds 24 hours. Keep in mind that a time value without a date uses January 0, 1900 as the date.
Similarly, if you enter a date and a time (and the time exceeds 24 hours), the date that you entered is adjusted. If you enter 9/18/2007 25:00:00, for example, it’s interpreted as 9/19/2007 1:00:00 AM.
If you enter a time only (without an associated date), into an unformatted cell, the maximum time that you can enter into a cell is 9999:59:59 (just under 10,000 hours). Excel adds the appropriate number of days. In this case, 9999:59:59 is interpreted as 3:59:59 PM on 02/19/1901. If you enter a time that exceeds 10,000 hours, the entry is interpreted as a text string rather than a time.
You have a great deal of flexibility in formatting cells that contain dates and times. For example, you can format the cell to display the date part only, the time part only, or both the date and time parts.
You format dates and times by selecting the cells and then using the Number tab of the Format Cells dialog box, as shown in Figure 13.1. To display this dialog box, click the dialog box launcher icon in the Number group of the Home tab. Or, you can click the Number Format control and select More Number Formats from the list that appears.
The Date category shows built-in date formats, and the Time category shows built-in time formats. Some formats include both date and time displays. Just select the desired format from the Type list and click OK.
When you create a formula that refers to a cell containing a date or a time, Excel automatically formats the formula cell as a date or a time. Sometimes, this automation is very helpful; other times, it’s completely inappropriate and downright annoying. To return the number formatting to the default General format, choose Home Number Number Format, and select General from drop-down list Or, use this shortcut-key combination: Ctrl+Shift+~.
If none of the built-in formats meets your needs, you can create a custom number format. Select the Custom category and then type the custom format codes into the Type box. (See Chapter 24 for information on creating custom number formats.)
Excel has some problems when it comes to dates. Many of these problems stem from the fact that Excel was designed many years ago, before the acronym Y2K was even thought of. And, as I describe, the Excel designers basically emulated the Lotus 1-2-3 program’s limited date and time features, which contain a nasty bug duplicated intentionally in Excel.
If Excel were being designed from scratch today, I’m sure it would be much more versatile in dealing with dates. Unfortunately, users are currently stuck with a product that leaves much to be desired in the area of dates.
A leap year, which occurs every four years, contains an additional day (February 29). Although the year 1900 was not a leap year, Excel treats it as such. In other words, when you type 2/29/1900 into a cell, Excel interprets it as a valid date and assigns a serial number of 60.
If you type 2/29/1901, however, Excel correctly interprets it as a mistake and doesn’t convert it to a date. Rather, it simply makes the cell entry a text string.
How can a product used daily by millions of people contain such an obvious bug? The answer is historical. The original version of Lotus 1-2-3 contained a bug that caused it to consider 1900 as a leap year. When Excel was released some time later, the designers knew of this bug and chose to reproduce it in Excel to maintain compatibility with Lotus worksheet files.
Why does this bug still exist in later versions of Excel? Microsoft asserts that the disadvantages of correcting this bug outweigh the advantages. If the bug were eliminated, it would mess up millions of existing workbooks. In addition, correcting this problem would possibly affect compatibility between Excel and other programs that use dates. As it stands, this bug really causes very few problems because most users don’t use dates before March 1, 1900.
The world, of course, didn’t begin on January 1, 1900. People who use Excel to work with historical information often need to work with dates before January 1, 1900. Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you can enter July 4, 1776 into a cell, and Excel won’t complain.
You can’t, however, perform any manipulation on dates entered as text. For example, you can’t change its numeric formatting, you can’t determine which day of the week this date occurred on, and you can’t calculate the date that occurs seven days later.
My Power Utility Pak add-in includes eight new worksheet functions that enable you to work with any date in the years 0100 through 9999. Figure 13.2 shows a worksheet that uses these extended date functions in columns E though H to perform calculations that involve pre-1900 dates. You can download a trial version of Power Utility Pak from my Web site (http://j-walk.com/ss
), or use the coupon in the back of the book to order a copy at a discounted price.
You need to exercise caution when entering dates by using two digits for the year. When you do so, Excel has some rules that kick in to determine which century to use. And those rules vary, depending on the version of Excel that you use.
Two-digit years between 00 and 29 are interpreted as 21st century dates, and two-digit years between 30 and 99 are interpreted as 20th-century dates. For example, if you enter 12/15/28, Excel interprets your entry as December 15, 2028. But if you enter 12/15/30, Excel sees it as December 15, 1930, because Windows uses a default boundary year of 2029. You can keep the default as is or change it by using the Windows Control Panel. In Windows XP, display the Regional And Language Options dialog box. Then click the Customize button to display the Customize Regional Options dialog box. Select the Date tab and then specify a different year. This procedure may vary with different versions of Windows.
Excel has quite a few functions that work with dates. These functions are accessible by choosing Formulas Function Library Date & Time.
Table 13.4 summarizes the date-related functions available in Excel.
Table 13.4. Date-Related Functions
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. |
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).
You can easily enter a date into a cell by simply typing it while 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 comprised of the year in cell A1, the month in cell B1, and the day in cell C1:
=DATE(A1,B1,C1)
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.
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!
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 the right mouse button. Release the mouse button and select an option from the shortcut menu (see Figure 13.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 week days 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)
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.
Refer to Chapter 12 for more information about using formulas to manipulate text.
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.
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
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.
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 13.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)
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.
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.
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 ten 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).
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).
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.
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))
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")
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 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 the day of the year is stored in cell B1.
=DATE(A1,1,B1)
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))
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.
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).
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.
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.
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 13.5 shows a workbook with formulas that calculate the date for ten U.S. holidays. The formulas, which reference the year in cell A1, are listed in the sections that follow.
The workbook shown in Figure 13.5 also appears on the companion CD-ROM. The file is named holidays.xlsx.
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 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)
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.
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)
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 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)
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))
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)
This function returns the wrong result (TRUE
) if the year is 1900. See “Excel’s leap year bug,” earlier in this chapter.
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.
Excel also includes a number of functions that enable you to work with time values in your formulas. This section contains examples that demonstrate the use of these functions.
Table 13.5 summarizes the time-related functions available in Excel. When you use the Insert Function dialog box, these functions appear in the Date & Time function category.
Table 13.5. Time-Related Functions
Function | Description |
---|---|
HOUR | Converts a serial number to an hour |
MINUTE | Converts a serial number to a minute |
MONTH | Converts a serial number to a month |
NOW | Returns the serial number of the current date and time |
SECOND | Converts a serial number to a second |
TIME | Returns the serial number of a particular time |
TIMEVALUE | Converts a time in the form of text to a serial number |
This formula displays the current time as a time serial number (or as a serial number without an associated date):
=NOW()-TODAY()
You need to format the cell with a time format to view the result as a recognizable time. The quickest way is to choose Home Number Format Number and select Time from the drop-down list.
One way to enter a time value into a cell is to just type it, making sure that you include at least one colon (:). You can also create a time by using the TIME
function. For example, the following formula returns a time comprised of the hour in cell A1, the minute in cell B1, and the second in cell C1:
=TIME(A1,B1,C1)
Like the DATE
function, the TIME
function accepts invalid arguments and adjusts the result accordingly. For example, the following formula uses 80 as the minute argument and returns 10:20:15 AM. The 80 minutes are simply added to the hour, with 20 minutes remaining.
=TIME(9,80,15)
If you enter a value greater than 24 as the first argument for the TIME
function, the result may not be what you expect. Logically, a formula such as the one that follows should produce a date/time serial number of 1.041667 (that is, one day and one hour).
=TIME(25,0,0)
In fact, this formula is equivalent to the following:
=TIME(1,0,0)
You can also use the DATE
function along with the TIME
function in a single cell. The formula that follows generates a date and time with a serial number of 39420.7708333333—which represents 6:30 PM on December 4, 2007:
=DATE(2007,12,4)+TIME(18,30,0)
The TIMEVALUE
function converts a text string that looks like a time into a time serial number. This formula returns 0.2395833333, the time serial number for 5:45 AM:
=TIMEVALUE("5:45 am")
To view the result of this formula as a time, you need to apply number formatting to the cell. The TIMEVALUE
function doesn’t recognize all common time formats. For example, the following formula returns an error because Excel doesn’t like the periods in “a.m.”
=TIMEVALUE("5:45 a.m.")
Because times are represented as serial numbers, you can subtract the earlier time from the later time to get the difference. For example, if cell A2 contains 5:30:00 and cell B2 contains 14:00:00, the following formula returns 08:30:00 (a difference of eight hours and 30 minutes):
=B2-A2
If the subtraction results in a negative value, however, it becomes an invalid time; Excel displays a series of hash marks (#######
) because a time without a date has a date serial number of 0. A negative time results in a negative serial number, which is not permitted.
If the direction of the time difference doesn’t matter, you can use the ABS
function to return the absolute value of the difference:
=ABS(B2-A2)
This “negative time” problem often occurs when calculating an elapsed time—for example, calculating the number of hours worked given a start time and an end time. This presents no problem if the two times fall in the same day. But if the work shift spans midnight, the result is an invalid negative time. For example, you may start work at 10:00 PM and end work at 6:00 AM the next day. Figure 13.6 shows a worksheet that calculates the hours worked. As you can see, the shift that spans midnight presents a problem (cell C3).
Using the ABS
function (to calculate the absolute value) isn’t an option in this case because it returns the wrong result (16 hours). The following formula, however, does work:
=IF(B2<A2,B2+1,B2)-A2
Negative times are permitted if the workbook uses the 1904 date system. To switch to the 1904 date system, use the Advanced section of the Excel Options dialog box. Place a check mark next to the Use 1904 Date System option. But beware! When changing the workbook’s date system, if the workbook uses dates, the dates will be off by four years For more information about the 1904 date system, see the sidebar titled “Choose Your Date System: 1900 or 1904,” earlier in this chapter.
Many people are surprised to discover that when you sum a series of times that exceed 24 hours, Excel doesn’t display the correct total. Figure 13.7 shows an example. The range B2:B8 contains times that represent the hours and minutes worked each day. The formula in cell B9 is
=SUM(B2:B8)
As you can see, the formula returns a seemingly incorrect total (17 hours, 45 minutes). The total should read 41 hours, 45 minutes. The problem is that the formula is displaying the total as a date/time serial number of 1.7395833, but the cell formatting is not displaying the date part of the date/time. The answer is incorrect because cell B9 has the wrong number format.
To view a time that exceeds 24 hours, you need to apply a custom number format for the cell so that square brackets surround the hour part of the format string. Applying the number format here to cell B9 displays the sum correctly:
[h]:mm
For more information about custom number formats, see Chapter 24.
Figure 13.8 shows another example of a worksheet that manipulates times. This worksheet keeps track of hours worked during a week (regular hours and overtime hours).
This workbook is available on the companion CD-ROM. The file name is time sheet.xlsm. The workbook contains a few macros to make it easier to use.
The week’s starting date appears in cell D5, and the formulas in column B fill in the dates for the days of the week. Times appear in the range D8:G14, and formulas in column H calculate the number of hours worked each day. For example, the formula in cell H8 is
=IF(E8<D8,E8+1-D8,E8-D8)+IF(G8<F8,G8+1-G8,G8-F8)
The first part of this formula subtracts the time in column D from the time in column E to get the total hours worked before lunch. The second part subtracts the time in column F from the time in column G to get the total hours worked after lunch. I use IF
functions to accommodate graveyard shift cases that span midnight—for example, an employee may start work at 10:00 PM and begin lunch at 2:00 AM. Without the IF
function, the formula returns a negative result.
The following formula in cell H17 calculates the weekly total by summing the daily totals in column H:
=SUM(H8:H14)
This worksheet assumes that hours in excess of 40 hours in a week are considered overtime hours. The worksheet contains a cell named Overtime, in cell C23. This cell contains 40:00. If your standard workweek consists of something other than 40 hours, you can change this formula.
The following formula (in cell H18) calculates regular (nonovertime) hours. This formula returns the smaller of two values: the total hours or the overtime hours.
=MIN(E17,Overtime)
The final formula, in cell H19, simply subtracts the regular hours from the total hours to yield the overtime hours.
=E17-E18
The times in H17:H19 may display time values that exceed 24 hours, so these cells use a custom number format:
[h]:mm
Military time is expressed as a four-digit number from 0000 to 2359. For example, 1:00 AM is expressed as 0100 hours, and 3:30 PM is expressed as 1530 hours. The following formula converts such a number (assumed to be in cell A1) to a standard time:
=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))
The formula returns an incorrect result if the contents of cell A1 do not contain four digits. The following formula corrects the problem, and it returns a valid time for any military time value from 0 to 2359:
=TIMEVALUE(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(A1,2))
Following is a simpler formula that uses the TEXT
function to return a formatted string, and then it uses the TIMEVALUE
function to express the result in terms of a time.
=TIMEVALUE(TEXT(A1,"00:00"))
To convert decimal hours to a time, divide the decimal hours by 24. For example, if cell A1 contains 9.25 (representing hours), this formula returns 09:15:00 (nine hours, 15 minutes):
=A1/24
To convert decimal minutes to a time, divide the decimal hours by 1,440 (the number of minutes in a day). For example, if cell A1 contains 500 (representing minutes), the following formula returns 08:20:00 (eight hours, 20 minutes):
=A1/1440
To convert decimal seconds to a time, divide the decimal hours by 86,400 (the number of seconds in a day). For example, if cell A1 contains 65,000 (representing seconds), the following formula returns 18:03:20 (18 hours, three minutes, and 20 seconds):
=A1/86400
You can use the TIME
function to add any number of hours, minutes, or seconds to a time. For example, assume that cell A1 contains a time. The following formula adds 2 hours and 30 minutes to that time and displays the result:
=A1+TIME(2,30,0)
You can use the TIME
function to fill a range of cells with incremental times. Figure 13.9 shows a worksheet with a series of times in 10-minute increments. Cell A1 contains a time that was entered directly. Cell A2 contains the following formula, which copied down the column:
=A1+TIME(0,10,0)
You may need to create a formula that rounds a time to a particular value. For example, you may need to enter your company’s time records rounded to the nearest 15 minutes. This section presents examples of various ways to round a time value.
The following formula rounds the time in cell A1 to the nearest minute:
=ROUND(A1*1440,0)/1440
The formula works by multiplying the time by 1440 (to get total minutes). This value is passed to the ROUND
function, and the result is divided by 1440. For example, if cell A1 contains 11:52:34, the formula returns 11:53:00.
The following formula resembles this example, except that it rounds the time in cell A1 to the nearest hour:
=ROUND(A1*24,0)/24
If cell A1 contains 5:21:31, the formula returns 5:00:00.
The following formula rounds the time in cell A1 to the nearest 15 minutes (a quarter of an hour):
=ROUND(A1*24/0.25,0)*(0.25/24)
In this formula, 0.25 represents the fractional hour. To round a time to the nearest 30 minutes, change 0.25 to 0.5, as in the following formula:
=ROUND(A1*24/0.5,0)*(0.5/24)
Sometimes, you may want to work with time values that don’t represent an actual time of day. For example, you may want to create a list of the finish times for a race or record the time you spend jogging each day. Such times don’t represent a time of day. Rather, a value represents the time for an event (in hours, minutes, and seconds). The time to complete a test, for example, may be 35 minutes and 45 seconds. You can enter that value into a cell as:
00:35:45
Excel interprets such an entry as 12:35:45 AM, which works fine. (Just make sure that you format the cell so that it appears as you like.) When you enter such times that do not have an hour component, you must include at least one zero for the hour. If you omit a leading zero for a missing hour, Excel interprets your entry as 35 hours and 45 minutes.
Figure 13.10 shows an example of a worksheet set up to keep track of a person’s jogging activity. Column A contains simple dates. Column B contains the distance in miles. Column C contains the time it took to run the distance. Column D contains formulas to calculate the speed in miles per hour. For example, the formula in cell D2 is
=B2/(C2*24)
Column E contains formulas to calculate the pace, in minutes per mile. For example, the formula in cell E2 is
=(C2*60*24)/B2
Columns F and G contain formulas that calculate the year-to-date distance (using column B) and the cumulative time (using column C). The cells in column G are formatted using the following number format (which permits time displays that exceed 24 hours):
[hh]:mm:ss
You can also access the workbook shown in Figure 13.10 on the companion CD-ROM. The file is named jogging log.xlsx.