Performing Addition with Time-Formatted Cells in Calc

From NeoWiki

Revision as of 13:11, 14 March 2007 (edit) Jgd (Talk | contribs) ← Previous diff		Current revision (00:51, 17 March 2007) (edit) (undo) Lorinda (Talk | contribs) m (a little polish; remove fixme)
(2 intermediate revisions not shown.)
Line 1:		Line 1:
-	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''':	+	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:34
Line 9:		Line 9:
	12:31		12:31
-	and you want to add up these hours. You can select the cell which will display the result then click "=", and select {{prefName|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 {{button|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:	+	and you want to add up these hours. You can select the cell which will display the result then click "=", and select {{prefName|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 {{button|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		* Using the '''Time''' format
-	**Select the cell which have to contain the result	+	**Select the cell which is to contain the result
-	**Go in the {{menu|Format}} menu, then in the {{menu|Cells...}} sub-menu	+	**Go in the {{menu|Format}} menu, then the {{menu|Cells...}} sub-menu
	**Select {{section|Numbers}}		**Select {{section|Numbers}}
-	**In the {{section|Category}} section select {{prefName|Time}}	+	**In the {{section|Category}} section select {{prefName|Time}}
-	**In the {{section|Format code}} section enter : "[HH]:MM" or "[HH]:MM:SS" (without quotes)	+	**In the {{section|Format code}} section enter "[HH]:MM" or "[HH]:MM:SS" (without quotes)
	**Click {{button|OK}}.		**Click {{button|OK}}.
Line 23:		Line 23:
	**Go in the {{menu|Format}} menu then in the {{menu|Cells...}} sub-menu		**Go in the {{menu|Format}} menu then in the {{menu|Cells...}} sub-menu
	**Select {{section|Numbers}}		**Select {{section|Numbers}}
-	**In the {{section|Category}} section select {{prefName|Number}}	+	**In the {{section|Category}} section select {{prefName|Number}}
-	**In the {{section|Format}} section choose {{prefName|Standard}}	+	**In the {{section|Format}} section choose {{prefName|Standard}}
	**Click {{button|OK}}.		**Click {{button|OK}}.

---

Current revision

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.