Add or subtract units of time

Suppose that you want to calculate the total amount of time that a set of tasks will take so that you can determine a contract rate. Or perhaps you want to calculate the elapsed time of a practice marathon that you have run. You can do both of these by adding or subtracting units of time.

In these time calculations, you can use a standard format or a 24-hour (military) time format. For example, you can enter the same time as 10:45 PM (standard format) or 22:45 (24-hour format).

In this article


Top of Page Top of Page

Add units of time

Suppose that you want to know how many hours and minutes it will take to complete two tasks. You estimate that the first task will require 12 hours and 45 minutes and the second task will require 15 hours and 30 minutes.

  1. In cell A1, type 12:45, and then type 15:30 in A2.
  2. In cell A3, type =A1+A2.

By default, Excel interprets the time values you entered in the two cells as elapsed time from midnight. In this case, adding 12:45 to 15:30 results in 28:15, which is equal to one full day plus 4:15.

However, if the cells in which you entered the time values were originally set to the General format, Excel returns 4:15 as the result (28:15 minus 24 hours). To make Excel display the value that you expect (in this case, 28:15), do the following:

  1. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  2. In the Format Cells dialog box, click the Number tab.
  3. Under Category, click Custom.
  4. In the Type box, type [h]:mm.

 Tip   You can also show the results in minutes and seconds by setting the format to [m]:ss, or minutes only by typing [m].

  1. Click OK.

Excel displays 28:15 if you used the format [h]:mm).

Top of Page Top of Page

Calculate elapsed time (subtract time)

Elapsed time is the difference between two times. Suppose that you want to calculate the elapsed time of a practice marathon run that you started at 10:24:00 AM and that ended at 2:13:36 PM, measured to the second.

  1. Do the following:
    • In cell A1 (or any other cell), type the end time. For example, type 10:24:00 AM.
    • In cell A2 (or any other cell), type the start time. For example, type 2:13:36 PM.

 Tip   Make sure that you include a space between the minute value and "AM" or "PM."

  1. In cell A3, type =A2-A1.
  2. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  3. In the Format Cells dialog box, click the Number tab.
  4. Under Category, click Custom.
  5. In the Type box, type [h]:mm:ss.

 Tip   You can also show the results in minutes by setting the format to [m]:ss.

  1. Click OK.

In cell A3, Excel displays 3:49:36 (or 229:36 if you used the format [m]:ss).

Top of Page Top of Page

Display time units as 24-hour (military) time

To use a 24-hour format instead of a 12-hour AM/PM format, you can display time as 24-hour (military) time. This time format adds 12 hours to any time value greater than or equal to 1:00 PM. For example, 1:00 PM is displayed as 13:00, and 10:45 PM is displayed as 22:45.

  1. On the worksheet, select the cells that contain the time that you want to display in 24-hour time format.
  2. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  3. In the Format Cells dialog box, click the Number tab.
  4. Under Category, click Custom.
  5. In the Type box, type hh:mm.

Top of Page Top of Page

 
 
Applies to:
Excel 2007