Time-Related Functions
Excel, as you might expect, 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 6-6 summarizes the time-related functions available in Excel. Like the date functions discussed earlier, time-related functions can be found under the Date & Time drop-down list via Formulas 
Function Library.
| Function | Description |
|---|---|
| HOUR | Returns the hour of a time value |
| MINUTE | Returns the minute of a time value |
| NOW | Returns the current date and time |
| SECOND | Returns the second of a time |
| TIME | Returns a time for a specified hour, minute, and second |
| TIMEVALUE | Converts a time in the form of text to an actual time value |
Open table as spreadsheet
Displaying the Current Time
This formula displays the current time as a time serial number (or 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. For example, you can apply the following number format:
hh:mm AM/PM
You can also display the time, combined with text. The formula that follows displays this text: The current time is 6:28 PM.
="The current time is "&TEXT(NOW(),"h:mm AM/PM")
| Note | These formulas are updated only when the worksheet is calculated. |
| Tip | To enter a time stamp into a cell, press Ctrl+Shift+: (colon). Excel inserts the time as a static value (it does not change). |
Displaying Any Time
Earlier in this chapter, I describe how to enter a time value into a cell: Just type it into a cell, 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 comprising 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)
| Caution | 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 6:30 PM on December 4, 2007 (which is date/time serial number 39420.7708333333):
=DATE(2007,12,4)+TIME(18,30,0)
| Note | When you enter the preceding formula, Excel formats the cell to display the date only. To see the time, you'll need to change the number format to one that displays a date and a time. |
| Tip | To enter the current date and time into a cell that doesn't change when the worksheet recalculates, press Ctrl+; (semicolon), space, Ctrl+Shift+: (colon), and then press Enter. |
The TIMEVALUE function converts a text string that looks like a time into a time serial number. This formula returns 0.2395833333, which is 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.")
Summing Times That Exceed 24 Hours
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 6-6 shows an example. The range B2:B8 contains times that represent the hours and minutes worked each day. The formula in cell B9 is
Figure 6-6: Incorrect cell formatting makes the total appear incorrectly.
=SUM(B2:B8)
As you can see, the formula returns a seemingly incorrect total (18 hours, 30 minutes). The total should read 42 hours, 30 minutes. The problem is that the formula is really displaying a date/time serial number of 1.770833, but the cell formatting is not displaying the "date" part of the date/time. In other words, cell B9 has an incorrect number format.
To view a time that exceeds 24 hours, you need to change the number format for the cell so square brackets surround the hour part of the format string. Applying the number format here to cell B9 displays the sum correctly:
[h]:mm
Figure 6-7 shows another example of a worksheet that manipulates times. This worksheet keeps track of hours worked during a week (regular hours and overtime hours).
Figure 6-7: An employee time sheet workbook.
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 that exceed 40 hours in a week are considered overtime hours. The worksheet contains a cell named Overtime (cell C23) that contains 40:00. If your standard workweek consists of something other than 40 hours, you can change the Overtime cell.
The following formula (in cell E18) calculates regular (non-overtime) hours. This formula returns the smaller of two values: the total hours, or the overtime hours.
=MIN(E17,Overtime)
The final formula, in cell E19, 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
| On the CD | The workbook shown in Figure 6-7, |
time sheet.xlsm
Calculating the Difference between Two Times
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. If the work shift spans midnight, though, 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 6-8 shows a worksheet that calculates the hours worked. As you can see, the shift that spans midnight presents a problem.
Figure 6-8: Calculating the number of hours worked returns an error if the shift spans midnight.
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
In fact, another formula (even simpler) can do the job:
=MOD(B2-A2,1)
| Tip | Negative times are permitted if the workbook uses the 1904 date system. To switch to the 1904 date system, choose Office |
Converting from Military Time
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 appear 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 returns a valid time for any military time value from 0 to 2359:
=TIMEVALUE(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(A1,2))
The following is a simpler formula that uses the TEXT function to return a formatted string and then uses the TIMEVALUE function to express the result in terms of a time.
=TIMEVALUE(TEXT(A1,"00\:00"))
Converting Decimal Hours, Minutes, or Seconds to a Time
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 (9 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 (8 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, 3 minutes, and 20 seconds):
=A1/86400
Adding Hours, Minutes, or Seconds to a Time
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 two 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 6-9 shows a worksheet with a series of times in ten-minute increments. Cell A1 contains a time that was entered directly. Cell A2 contains the following formula, which was copied down the column:
=A1+TIME(0,10,0)
Figure 6-9: Using a formula to create a series of incremental times.
| Tip | You can also use Excel AutoFill feature to fill a range with times. For example, to create a series of times with ten-minute increments, enter 8:00 AM in cell A1 and 8:10 AM in cell A2. Select both cells, and then drag the fill handle (in the lower-right corner of cell A2) down the column to create the series. |
Converting between Time Zones
You may receive a worksheet that contains dates and times in Greenwich Mean Time (GMT, sometimes referred to as Zulu time), and you need to convert these values to local time. To convert dates and times into local times, you need to determine the difference in hours between the two time zones. For example, to convert GMT times to U.S. Central Standard Time (CST), the hour conversion factor is –6.
You can't use the TIME function with a negative argument, so you need to take a different approach. One hour equals 1/24 of a day, so you can divide the time conversion factor by 24 and then add it to the time.
Figure 6-10 shows a worksheet set up to convert dates and times (expressed in GMT) to local times. Cell B1 contains the hour conversion factor (–5 hours for U.S. Eastern Standard Time; EST). The formula in B4, which copies down the column, is
Figure 6-10: This worksheet converts dates and times between time zones.
=A4+($B$1/24)
| On the CD | You can access the workbook shown in Figure 6-10, |
This formula effectively adds x hours to the date and time in column A. If cell B1 contains a negative hour value, the value subtracts from the date and time in column A. Note that, in some cases, this also affects the date.
Rounding Time Values
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 (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)
Working with Non–Time-of-Day Values
Sometimes, you may want to work with time values that don't represent an actual time of day. For example, you might 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 instance, might 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 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 6-11 shows an example of a worksheet set up to keep track of someone'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
Figure 6-11: This worksheet uses times not associated with a time of day.
=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
| On the CD | You can access the workbook shown in Figure 6-11, |
EAN: 2147483647
Pages: 212