Chapter 12: Time and Time Functions

Overview

• How do I enter times into Excel?

• How do I enter a time and date in the same cell?

• How does Excel do computations with times?

• How can I have my worksheet always display the current time?

• How can I use the TIME function to create times?

• How can I use the TIMEVALUE function to convert a text string to a time?

• How do I extract the hour, minute, or second from a given time?

• Given work starting and ending times, how do I determine the number of hours an employee worked?

• I added up the total time an employee worked and I never get more than 24 hours. What did I do wrong?

• How can I easily create a sequence of regularly spaced time intervals?

Recall from Chapter 6, “Dates and Date Functions,” that Microsoft Office Excel 2007 gives a date of January 1, 1900 a serial number of 1, a date of January 2, 1900 a serial number of 2, and so on. Excel also assigns serial numbers to times (as a fraction of a 24-hour day). The starting point is midnight, so 3:00 A.M. has a serial number of .125, noon has a serial number of .5, 6:00 P.M. has a serial number of .75, and so on. If you combine a date and time in a cell, then the serial number is the number of days after January 1, 1900 plus the time fraction associated with the given time. Thus, entering January 1, 2007 in a cell yields (when formatted as General) a serial number of 39083, whereas January 1, 2007 6:00 A.M. yields a serial number of 39083.25.

• How do I enter times into Excel?

• To indicate times we enter a colon (:) after the hour and another colon before the seconds. For example, in file Time.xlsx (see Figure 12-1 on the next page), we entered in cell C2 the time 8:30 A.M. as either simply 8:30 or 8:30 A.M. In cell C3, we entered 8:30 PM as 8:30 PM. As shown in cell D3, we could also have entered 8:30 PM with 24-hour military time as 20:30. In cell A4, we entered 3:10:30 PM. This represents 30 seconds after 3:10 PM.

  
  Figure 12-1: Examples of time formats

• How do I enter a time and date in the same cell?

• Simply put a space after the date and enter the time. In cell F13 of file Time.xlsx, we entered January 1, 2007 5:35. Of course, this represents 5:35 A.M. on January 1, 2007. Excel immediately reformatted this to 1/1/2007 5:35.

• How does Excel do computations with times?

• When Excel does computations with times involving differences in times the displayed result depends on the format used in the cell. Figure 12-2 displays the various Excel time formats.

  
  Figure 12-2: Excel time formats

• In file Time.xlsx (see Figure 12-1), we took the difference between 8:30 PM and 8:30 A.M. in cells F5 and H5 with the formula =C3–C2. If we do not change the format, Excel thinks these times are 12 hours apart and enters 12:00 PM, as shown in cell H5. In most cases, we would like Excel to portray these times are .5 days apart. (Multiplying by 24, we could convert this time difference to hours.) To make Excel show .5 in cell F5 simply format cell F5 as a number.

• In cell F7 we try to subtract an earlier time from a later time with the formula =D2–D3. If we do not reformat the cell, then Excel displays the dreaded ##############. If we simply change the cell containing the formula to Number format (as in cell F8) we obtain the correct time difference –.5 days.

• Cells B17 and C17 give the start times for two jobs whereas cells B18 and C18 give the finish times for the jobs. (See Figure 12-3.) If we want to calculate how many hours it takes to complete each job, simply copy from B19 to C19 the formula =B18–B17 and reformat the cell as a Number. Thus, the first job took 29.18 days whereas the second job took 28.97 days.

  
  Figure 12-3: Determining time needed to complete jobs

• How can I have my worksheet always display the current time?

• The Excel formula =NOW() gives today’s date and the current time. For example, in cell G2 (see Figure 12-4) of file Time.xlsx, entering =NOW() yielded 3/7/2007 13:05 because I created the screen capture at 1:05 P.M. on March 7, 2007. To compute the current time, simply enter in cell H2 or I2 the formula =NOW()–TODAY(). Cell H2 is formatted to show a time (1:05 P.M.) whereas cell I2 is formatted to show a number (.55days). This represents the fact that 1:05 P.M. is 39 percent of the way between midnight of one day and midnight of the next day.

  
  Figure 12-4: Using the Now() and Today() functions

• How can I use the TIME function to create times?

• The TIME function has the syntax TIME(hour,minute,second). Given an hour, minute, and second, the TIME function returns a time of day. The TIME function will never return a value exceeding 24 hours.

• In cell A2 (see Figure 12-1), the formula =TIME(8,30,0) yields 8:30 A.M. In cell A3, the formula =TIME(20,30,0) yields 8:30 PM. In cell A4, the formula =TIME(15,10,30) yields 3:10:30 PM. Finally, note that in cell A5 the formula =TIME(25,10,30) treats the 25 like it is 25–24=1 and yields 1:10:30 A.M.

• Of course, if the second amount does not show up in a cell, just select a Time format that displays seconds.

• How can I use the TIMEVALUE function to convert a text string to a time?

• The function TIMEVALUE has the syntax =TIMEVALUE(timetext) where timetext is a text string that gives a time in a valid format. Then TIMEVALUE returns the time as a fraction between 0 and 1. (This means that the TIMEVALUE function ignores any date in time-text.) For example, in cell A7 (see Figure 12-1) the formula =TIMEVALUE("8:30") yields 0.354166667 because 8:30 A.M. is 35.4 percent of the way between midnight of one day and midnight of the next day.

• How do I extract the hour, minute or second from a given time?

• The Excel HOUR, MINUTE, and SECOND functions will extract the appropriate time unit from a cell containing time. For example, (as shown in Figure 12-1) entering in cell C5 the formula =HOUR(A4) yields 15 (3:00 PM is 15:00 military time). Entering in cell D5 the formula =Minute(a4) yields10 whereas entering =SECOND(A4) in cell E5 yields 30.

• Given work starting and ending times, how do I determine the number of hours an employee worked?

• In cells C10:D11, we entered the times that Jane and Jack started and ended work. We want to figure out how long each of them worked. (See Figure 12-5.) The problem is that Jane finished work the day after she started, so a simple subtraction will not give the actual number of hours she worked. Copying from C13 to C14 the formula =IF(D10>C10, 24*(D10–C10),24+24*(D10–C10)) yields the correct result. Of course, we formatted these cells as Number. If the finish time is after the start time, then subtracting the start time from the finish time and multiplying by 24 yields hours worked. If the finish time is before the start time, then 24*(finish time–start time) yields a negative number of hours, but adding 24 hours fixes things, assuming the end of the shift was one day later. Thus, Jane worked 9 hours and Jack worked 8.5 hours.

  
  Figure 12-5: Computing length of time worked by employees

• I added up the total time an employee worked and I never get more than 24 hours. What did I do wrong?

• In cells C31:D35, we give the number of hours (formatted as h:mm) an employee worked on each day of her workweek. (See Figure 12-6.) In cell D36, the formula =SUM(D31:D35) is used to compute the total number of hours worked during the week. Excel yields 14:48. This is clearly wrong! With the format h:mm, Excel will never yield a value exceeding 24 hours. In cell D38, we chose a format (38:48:00) which allows for more than 24 hours. Then summing up the hours worked each day yields the correct number of hours worked (38 hours and 48 minutes).

  
  Figure 12-6: Determining total hours worked during the week

• How can I easily create a sequence of regularly spaced time intervals?

• Suppose a doctor takes appointments from 8:00 A.M. in 20 minute segments up to 5:00 PM. How can I enter in different rows the list of possible appointment times? Simply use Excel’s great Auto Fill feature. (See Figure 12-7.) Simply enter the first two times (8:00 A.M. and 8:20 A.M.) in cells L15: L16. Now select cells L15: L16 and move the cursor to the lower-right corner of cell L16 until you see the black cross. Now drag the pointer down until you see 5:00 PM (the last appointment time). From cells L15: L16, Excel has guessed (correctly!) that you want to enter times that are 20 minutes apart. Of course, entering Monday in a cell and Tuesday below and using Auto Fill will yield the sequence Monday, Tuesday, Wednesday,…, eventually repeating Monday. Entering 1/1/2007 in one cell, 2/1/2007 in another cell, selecting these two cells, and using Auto Fill will yield a sequence of dates like 1/1/2007, 2/1/2007, 3/1/2007, and so on.

  
  Figure 12-7: Entering a sequence of times