Works: Using Hours and Minutes Format in a Spreadsheet (60280)


The information in this article applies to:

  • Microsoft Works for MS-DOS
  • Microsoft Works 2.0
  • Microsoft Works 3.0
  • Microsoft Works 4.5
  • Microsoft Works 4.5a
  • Microsoft Works for Windows 95, version 4.0
  • Microsoft Works for Windows 95, version 4.0 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 the percent of a full 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.
Modification Type:MajorLast Reviewed:11/15/2004
Keywords:kbinfo KB60280
©2004 Microsoft Corporation. All rights reserved.