mjoyce
asked on
Excel Date Precision: possible bug
I think I've found a bug in Excel, and the precision to which it stores dates.
Try this sheet.
A1 16:00 time format, hh:mm
A2 20:00 time format, hh:mm
A3 =(a2-a1)*24 scientific notation, shows 4.00E+00
A4 0.25 scientific notation, shows 4.00E+00
A5 =a4-a3 scientific notation, shows 3.75E+00
A6 3.75 scientific notation, shows 3.75E+00
A7 0 or blank scientific notation, shows 0.00E+00
A8 =a5-a6-a7 scientific notation, shows 1.78E-15
I guess it's because (A2-A1)*24 gives a decimal fraction which is not exactly equal to 4.00, but as far as I can tell Excel stores this as 4.00 exactly.
I can get around it by using a rounding function, but I'm not happy with this solution.
It works with many different values, but not if you change the a8 formula to =a5-a6: the 0/blank value in A7 needs to be there to stuff things up.
Any ideas, or is this a genuine, typical bug? Apparently it's the same in Excel 2000. And, just to make things even more annoying, it fails in Access too.
Try this sheet.
A1 16:00 time format, hh:mm
A2 20:00 time format, hh:mm
A3 =(a2-a1)*24 scientific notation, shows 4.00E+00
A4 0.25 scientific notation, shows 4.00E+00
A5 =a4-a3 scientific notation, shows 3.75E+00
A6 3.75 scientific notation, shows 3.75E+00
A7 0 or blank scientific notation, shows 0.00E+00
A8 =a5-a6-a7 scientific notation, shows 1.78E-15
I guess it's because (A2-A1)*24 gives a decimal fraction which is not exactly equal to 4.00, but as far as I can tell Excel stores this as 4.00 exactly.
I can get around it by using a rounding function, but I'm not happy with this solution.
It works with many different values, but not if you change the a8 formula to =a5-a6: the 0/blank value in A7 needs to be there to stuff things up.
Any ideas, or is this a genuine, typical bug? Apparently it's the same in Excel 2000. And, just to make things even more annoying, it fails in Access too.
Try typing 16:00:00 and 20:00:00 as the first two entries.
Nope .. didn't work. Sorry.
Yes, if you type "4" into A3, everything works right. Seems like a bug with the time functions...
This looks like the reason:
http://support.microsoft.com/support/kb/articles/Q263/2/13.ASP
To wit: "Excel is limited to 15 digits of precision because of its adherence to the IEEE 754 specification."
Thus, that's why you get your error in the 15th significant digit.
WKHays
This is because of:
a) Excel has a 15 digit precision 'only'. Details see
http://support.microsoft.com/support/kb/articles/Q182/1/96.ASP
b) Hours, regardless of the formatting, are stored as fraction of a day.
Therefore 20:00 actually is 20/24 which should be
0.833333333333333333333333
a) Excel has a 15 digit precision 'only'. Details see
http://support.microsoft.com/support/kb/articles/Q182/1/96.ASP
b) Hours, regardless of the formatting, are stored as fraction of a day.
Therefore 20:00 actually is 20/24 which should be
0.833333333333333333333333
ASKER
Thanks,
I knew the error was because of precision, and knew a work-around, but you found the MS Knowledge Base article that confirmed it.
I still don't understand why the cell with the value of
(20:00-16:00)*24 displays
exactly 4.0 (or 4.000000000000000000000000
3.99999999999999900000
Not much I can do about it, I guess, except put ROUND functions in just about everywhere!
I knew the error was because of precision, and knew a work-around, but you found the MS Knowledge Base article that confirmed it.
I still don't understand why the cell with the value of
(20:00-16:00)*24 displays
exactly 4.0 (or 4.000000000000000000000000
3.99999999999999900000
Not much I can do about it, I guess, except put ROUND functions in just about everywhere!
This is exactly the reason I spent too much time trying to find out, making my post looking like a rehash of wkhays. IMO it is because of rounding rules.
As for using the ROUND function. There _might_ be an alternative: Tools|Options|Calculation:
Just for curiosity:
a) Why is this difference important ? Are you calculating the bank interest of Bill Gates ?
b) Have you accepted as answer ? I got a notification (but it was for Topic -1) and here it is still open.
As for using the ROUND function. There _might_ be an alternative: Tools|Options|Calculation:
Just for curiosity:
a) Why is this difference important ? Are you calculating the bank interest of Bill Gates ?
b) Have you accepted as answer ? I got a notification (but it was for Topic -1) and here it is still open.
mjoyce ...
There was a problem with EE when the question was answered... I got notification that you accepted my answer, but the question was never closed out properly.
So ... Could you please accept my answer again? Sorry to trouble you but winning EE tee-shirts is my only real source of new clothes and Christmas presents for my kids :)
Thanks,
WKHays
There was a problem with EE when the question was answered... I got notification that you accepted my answer, but the question was never closed out properly.
So ... Could you please accept my answer again? Sorry to trouble you but winning EE tee-shirts is my only real source of new clothes and Christmas presents for my kids :)
Thanks,
WKHays
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it took so long to close.