Microsoft KB Archive/81177

= Works: Creating a Total for a Series of Time Calculations =

Article ID: 81177

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 3.0 Standard Edition

-



This article was previously published under Q81177





SUMMARY
To perform the SUM function on cells that are formatted with a time/date format in Microsoft Works, you must first convert the data in the cells to numeric format.

To accomplish this conversion, create a new column in the spreadsheet and enter a formula that multiplies the time stored in the time/date formatted cells by 24. This converts the time entered in the original cell(s) to an equivalent decimal value; this decimal number can be added correctly using the SUM function. You can use this method for a spreadsheet cell value (or range of cell values) as well as database field values.



MORE INFORMATION
The following is an example of how to add a time/date formatted column with the SUM function: Name      Start_Time     Stop_Time     Total_Work --    -     --

Scott     7:00 AM         4:00 PM      9.00 Marty     7:00 AM         4:45 PM      9.75 Jeff      8:00 AM         5:25 PM      9.42 Roy       9:00 AM         5:30 PM      8.50

Total:                                   36.67 The Total_Work column contains the following formula: =([stop_time]*24)-([start_time]*24) Start_time and Stop_time can be field names OR cell references.

By multiplying each value in the Total_Work column by 24, the content of each cell becomes a decimal value (for example, 9:15*24 = 9.25), and the new column can be added correctly by using the SUM function.

Note 1:

The above example is created by selecting the &quot;Hours, minutes&quot; and &quot;12 hour&quot; options after choosing Time/Date from the Format menu. Using the &quot;24 hour&quot; option requires that you enter all times in 24-hour format (for example, 4:00 P.M. must be entered as 16:00).

Note 2:

The above example assumes that Start_Time and Stop_Time occur on the same date. If the two times are on consecutive days, add 24 to the result to get the correct total.

Note 3:

If you format the Total_Work column (column D) with the time/date cell format, the column produces a total greater than 24. Because time can be displayed only in 12- or 24-hour format, the time wraps to 0 (zero) and begins again. For example, if the Total_Work column equals 35:35 when added, it displays as 11:35 A.M. Formatting the column for a numeric format corrects this problem (no wrapping occurs).

Additional query words: w_works1.00 1.05 2.00 2.xx 3.xx clock hours calculate

Keywords: KB81177

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.