D

#### Distribution Man

work that I perform on behalf of clients. The formula used to compute the

elapsed time in the "Billable Hours" column is identical for every row.

Unfortunately, the value retured by the formula is NOT accurate if I perform

30 minutes of service beginning at 7:29 or 13:29! What is wrong -- the

formula or Excel?

The formula for the cells in the "Billable Hours" column is as follows:

=IF(((F20-INT(F20))*24)-((D20-INT(D20))*24)<0,"Dumb

Entry",ROUNDDOWN(((F20-INT(F20))*24)-((D20-INT(D20))*24),2)). In the

formula, Column D provides the starting time and Column F records the time

work stops. All entries in those columns are simply two digits for the hour

and two digits for the minutes. Seconds are NOT recorded. Therefore, full

minutes are the smallest increment.

How can the formula return 0.49 hours (instead of 0.50 hours) if work

lasting 30 minutes commences at 7:29 or 13.29? The correct answer appears at

all other starting times as shown in my illustration. (Note: I have not

attempted to check the validity of the formula for all possible 30 minute

increments throughout a 24 hour cycle.) Incidentally, the same error occurs

if the formula is used on an old .xls formatted spreadsheet.

My OS is VISTA Ultimate.

Time Time Billable

Started Finished Hours

22:29 22:59 0.50

21:29 21:59 0.50

20:29 20:59 0.50

19:29 19:59 0.50

18:29 18:59 0.50

17:29 17:59 0.50

16:29 16:59 0.50

15:29 15:59 0.50

14:29 14:59 0.50

13:29 13:59 0.49

12:29 12:59 0.50

11:29 11:59 0.50

10:29 10:59 0.50

9:29 9:59 0.50

8:29 8:59 0.50

7:29 7:59 0.49

6:29 6:59 0.50

5:29 5:59 0.50