Chapter 12: Using Formulas with Dates and Times
12
FIGURE 12.16
Extract the parts of a time.
These functions are fairly straightforward.
The HOUR function returns a number between 0 and 23 corresponding to the hour of a
given time. This formula returns 6.
=HOUR("6:15:27 AM")The MINUTE function returns a number between 0 and 59 corresponding to the minutes of
a given time. This formula returns 15.
=MINUTE("6:15:27 AM")The SECOND function returns a number between 0 and 59 corresponding to the seconds of a
given time. This formula returns 27.
=SECOND("6:15:27 AM")Calculating elapsed time
One of the more common calculations done with time values is calculating elapsed time,
that is, how many hours and minutes between a start time and an end time.
The table in Figure 12.17 shows a list of start and end times, along with calculated elapsed
times. Look at Figure 12.17, and you can see that the formula in cell D4 is as follows:
=IF(C4< B4, 1 + C4 - B4, C4 - B4)To get the elapsed time between a start and end time, all you need to do is to subtract the
start time from the end time. There is a catch, however. If the end time is less than the
start time, you have to assume that the clock has been running for a full 24-hour period,
effectively looping back the clock.
In these cases, you have to add a 1 to the time to represent a full day. This ensures that
you don’t have negative elapsed times.
In our elapsed time formula, we use an IF function to check whether the end time is less
than the start time. If so, we add a 1 to our simple subtraction. If not, we can just do the
subtraction.
=IF(C4< B4, 1 + C4 - B4, C4 - B4)