Microsoft KB Archive/62402

Works: Elapsed Time Calculations

PSS ID Number: Q62402 Article last modified on 10-22-1998

1.00 1.05 2.00 3.00 | 2.00 3.00

MS-DOS WINDOWS

================================================================ ==

The information in this article applies to:

 Microsoft Works versions 1.0, 2.0, 3.0 for MS-DOS Microsoft Works versions 2.0, 3.0 for Windows == Microsoft Works versions 4.0, 4.5, 4.5a for Windows 95 == 

= SUMMARY =

Microsoft Works uses serial numbers to represent times and dates. Time is represented by the decimal portion of the serial number. A 24-hour day has a value of 1; therefore, a time such as 8:00 AM is represented by .3333 (repeated) because 8:00 is exactly one third (1/3) of the day.

For example, to calculate the time an employee works during a single day, using the two fields Time In and Time Out, you can use the following formula:

=(Time Out - Time In)*24

The following is an example:

(5:00 PM - 8:00 AM) * 24 = 9 hours (.7083 - .3333) * 24 = 9 hours (.375) * 24 = 9 hours

Note that .375 represents the fraction of the day that the employee worked; multiplying by 24 converts the number to hours.

= MORE INFORMATION =

A special circumstance arises when an employee works past midnight, putting Time In and Time Out on separate days. The value in the Time Out field is less than the value in the Time In field and a negative number results for the Total Hours Worked. The following are two solutions for this type of problem.

Include Fields for Date In and Date Out
A date represents the integer portion of the serial number; therefore, if an employee works past midnight, the serial number for Date Out will be greater than that for Date In. Thus, the following formula may be used:

=((Day Out-Day In)+(Time Out-Time In))*24

Use an IF Statement
The second possible solution is to use an IF statement to determine if the time when an employee quit is less than the time when an employee began. If the time is less, then a one (1) is added to offset the negative value. If the time is not less, then the formula is calculated in the normal way:

=If(Time Out<Time In,(1+(Time Out-Time In))24,(Time Out-Time In)24)

Note that this solution does not work for elapsed times spanning more than two days.

= Additional query words: =

Version : 1.00 1.05 2.00 3.00 | 2.00 3.00 Platform : MS-DOS WINDOWS ============================================================================= Copyright Microsoft Corporation 1998.