Microsoft KB Archive/41516
Works: Converting Time Format to Decimal Format
PSS ID Number: Q41516 Article last modified on 06-12-1996
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.
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.