# Excel 2007 Erroneous Calculation of Elapsed Time

Distribution Man
Guest
Posts: n/a

 23rd May 2008
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

New Member
Join Date: Jun 2011
Posts: 1

 26th Jun 2011
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?a2x5c2VsbA==?= Microsoft Excel Programming 2 26th Apr 2007 04:58 PM =?Utf-8?B?aGVsaW9z?= Microsoft Access Queries 4 19th Jul 2005 06:20 PM =?Utf-8?B?RmdyaXp6?= Microsoft Access Queries 1 27th Jan 2005 07:15 AM =?Utf-8?B?cndm?= Microsoft Excel Misc 1 21st Jan 2005 05:51 AM Ken Microsoft Excel Misc 0 15th Sep 2004 07:27 PM

Features