Works: Creating a Total for a Series of Time Calculations (81177)

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 "Hours, minutes" and "12 hour" options after choosing Time/Date from the Format menu. Using the "24 hour" 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).