Performing Addition with Time-Formatted Cells in Calc

From NeoWiki

Sometimes you have a Calc document in which some cells contain hour or date values. If you want to perform addition using the contents of time-formatted cells, you can use the normal Sum function, because the software automatically adjusts the formatting of the cell which displays the result. Be aware, however, that the default format of the cells restarts counting from 0 when the total hours exceeds 24. For example, let us assume that you have a range of cells formatted as Time with the following values:

12:34

12:33

12:32

12:31

and you want to add up these hours. You can select the cell which will display the result then click "=", and select SUM in the drop-down menu at the top and on the left side of the window. Select the cells whose sum is to be calculated and press Enter. If the cell which displays the result is in the Time format, (e.g. HH:MM:SS or HH:MM) the total will be equal to: 2:10 instead of 50:10, because the calculation starts again from 0 every time the sum reaches 24. There are two workaround methods:

• Using the Time format
  • Select the cell which is to contain the result
  • Go in the Format menu, then the Cells... sub-menu
  • Select Numbers
  • In the Category section select Time
  • In the Format code section enter "[HH]:MM" or "[HH]:MM:SS" (without quotes)
  • Click OK.

• Using the Standard format
  • Select the cell which have to contain the result
  • Go in the Format menu then in the Cells... sub-menu
  • Select Numbers
  • In the Category section select Number
  • In the Format section choose Standard
  • Click OK.