D
Distribution Man
Shown below is a portion of a time sheet that I created for keeping track of
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
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