Performing Addition with Time-Formatted Cells in Calc

From NeoWiki

Revision as of 16:36, 14 March 2007 by Jgd (Talk | contribs)
Jump to: navigation, search
This article is not up to date, or it needs structural or stylistic changes to conform with the rest of this wiki.

Sometimes you have a Calc document whose some cells contain hour or date values. If you want to add the cells which are formatted in this way, you can simply do an usual sum, because the software adjusts automatically the format of the cell which displays the result. However, the default format of the cells starts again the account from 0 when the total of the hours exceeds 24. For example, let us assume that you have a cell range whose format is Time:

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. As a workaround, you have two methods:

  • Using the Time 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 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.


This article in other languages: Français Español Italiano
Personal tools