Time Duration in Excel

Why does time duration in reports show as time in Excel?

By default, Excel displays the format hh:mm:ss as time am/pm when clicked whether it is real time or time duration. This is just they way Excel views the data within the cell, but you may still add time duration if needed.


As refered to an article from Microsoft here are the steps to take to accomplish this to show total time duration.

Source: https://support.office.com/en-us/article/Add-or-subtract-units-of-time-9a6558a1-a86b-41d4-b244-1000fc7d31e9


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].
  5. Click OK.
    Excel displays 28:15 if you used the format [h]:mm).


 Like this guide? You might also like LiveHelpNow customer support software packed with features like Live chat, SMS messaging, Team Unified Inbox, Knowledge base management and phone integration.

Related articles