Microsoft KB Archive/41516

Works: Converting Time Format to Decimal Format

PSS ID Number: Q41516 Article last modified on 06-12-1996

1.00 1.05

MS-DOS

= SUMMARY =

To perform the SUM function on cells that have been formatted for a time/date format in Microsoft Works versions 1.0 and 1.05, you must first convert the data in the cells to a 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 into the original cell(s) to an equivalent decimal value, which may be added correctly using the SUM function.

= MORE INFORMATION =

An example of how to add a time/date formatted column with the SUM function is illustrated below:

Name Start_Time Stop_Time Total_Work —- ———- ——— ———-

Scott 7:00 AM 4:00 PM 9:00 Marty 7:05 16:45 8:40 Jeff 8:00 AM 5:25 PM 9:25 Roy 9:00 17:30 8:30

If the Total_Work column is formatted with the time/date cell format, the column is added with the SUM function and produces a total greater than 24. Because time can be displayed only in a 12-hour or 24-hour format, the time wraps around to 0 (zero) and starts over again (that is, when added the Total_Work column equals 35:35, but is displayed as 11:35 AM). 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.

KBCategory: kbother KBSubcategory: dworkskb: Additional reference words:

=
================================================================ Copyright Microsoft Corporation 1996.