Excel 2007 Erroneous Calculation of Elapsed Time

[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

 

[dorisglazer]

I am having a similar problem. I am doing a simple calculation (I am just trying to teach simple time calculations). But I get a sorts of crazy answers.
- e.g. 1pm - 10 am, or 1 am - 10 am. Sometimes the answer is 0, sometimes I get a name error, sometimes I get a format of #####.
- AND I am trying to have consistent formats for all my cells, e.g. time for the data cells, and for my calculation (b2-a2), I have tried time, number, custom "h:mm". I can't seem to get a consistent answer.

for the calculation sometimes b2-a2 works, sometimes b2-a2)*24 works. Sometimes timevalue(text(b2-a2,"h:mm")) works, but none of these work all the time.

This is crazy. I found your post and hoped you might be able to explain a consistent stable way to get time calculations to work.

Thank you.

Doris