Microsoft KB Archive/60280

= Works: Using Hours and Minutes Format in a Spreadsheet =

Article ID: 60280

Article Last Modified on 8/13/2007

-

APPLIES TO


 * Microsoft Works 3.0 for MS-DOS
 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.0a

-



This article was previously published under Q60280



SUMMARY
The Microsoft Works Spreadsheet can display time in different formats; however, the underlying data is always the same because time is represented as a decimal value showing a decimal fraction of a day that time represents. For example, 8 hours is one third of a 24-hour day. If 8:00 is entered into a cell and the cell is formatted for general, it will display as .3333333.



MORE INFORMATION
The following formula subtracts the time-in value in cell A1 from the time-out value in cell B1 and returns the result in hours rounded to the nearest tenth. The IF statement adds 1 to the decimal so that the results are correct if the time-out value is after midnight.

=ROUND(IF((B1-A1)*24<0, (((1+B1)-A1) * 24), ((B1-A1)*24)), 1)

To convert to minutes, the decimal hours value (for example, 1.5 hours) must be multiplied by 60. For example, if cell A2 contains the time-in value and cell B2 contains the time-out value, then the formula

=INT((B2-A2)*24)

placed in cell C2 returns the hours, and the formula

=(((B2-A1)*24)-INT((B2-A1)*24))*60

placed in cell D2 returns the minutes.

Additional query words: 2.00 3.00 4.00 4.00a 4.50 4.50a W_Works

Keywords: kbinfo KB60280

-

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

© Microsoft Corporation. All rights reserved.