Getting inconsistent results when dealing with time values

Question

I've created a schedule in Excel and am using conditional formatting to control the generation of a Gantt chart. The sheet is formatted with a start time in column B and an end time in column C. I have in row 2 a series of times, in five minute intervals. I then have the following formula in the intersecting cells =IF(StartTime <= CurrentTime,IF(EndTime > CurrentTime,10,0),0) For example:

B3 = 7:00 PM - manually entered

C3 = 8:00 PM - manually entered

E2 = 7:00 PM - manually entered (F2:BL2 = (Col-1)+Time(0,5,0) to give me the five minute intervals)

E3 = =IF($B3 <=E$2,IF($C3>E$2,10,0),0)

I then use conditional formatting to control the highlighting of cell E3:BL66 to draw the lines in my Gantt chart.

My problem is that - sometimes - I wind up with the line extending one cell too far. For example, on row 32 I have an event that runs from 8:00 PM to 9:00 PM. This corresponds to:

B32 = 8:00 PM - manually entered

C32 = 9:00 PM - manually entered

Q2 = 8:00 PM - calculated as described above through AC2 = 9:05 PM, also calculated

Q32 [=IF($B32 <=Q$2,IF($C32>Q$2,10,0),0)] through

AC32 [=IF($B32 <=AC$2,IF($C32>AC$2,10,0),0)]

The expected result is that cells Q32 through AB32 are calculated to a value of 10 and AC32 is calculated to a value of 0. In this particular case, I get the expected results.

In row 41, I have an event that runs from 9:00 PM to 10:00 PM, so I have the following values:

B41 = 9:00 PM - manually entered

C41 = 10:00 PM - manually entered

AC2 = 9:00 PM - calculated as described above through AO2 = 10:05 PM, also calculated

AC41 [=IF($B41 <=AC$2,IF($C41>AC$2,10,0),0)] through

AO41 [=IF($B41 <=AO$2,IF($C41>AO$2,10,0),0)]

The expected result is that cells AC41 through AN41 are calculated to a value of 10 and AO41 is calculated to a value of 0. In this particular case, AC41 through AN41 are calculated correctly; however, AO41 is calculated to a value of 10.

The interesting thing is that if I copy the manually entered time from cell C41 (10:00 PM) and paste it into cell AO41, I get the expected result! This suggests to me that the formula I am using to calculate my time intervals [=AO2+TIME(0,5,0)] has a rounding error. Is there an alternative calculation that I can use, or some sort of wrapper function that can be used to compensate for any rounding that may occur?

It's also interesting to note that I initially entered all the times manually (including those in row 2) and observed the same behavior. That's when I implemented the calculation to automate the time interval population. That's when I discovered that I could "solve" the problem by copying the time value from my "End" time to the appropriate cell in row 2.

Any help is appreciated. I'd be happy to send you a copy of the spreadsheet, if it would help!

Thanks!

Answer 1

Ken wrote:

This suggests to me that the formula I am using to calculate my time intervals [=AO2+TIME(0,5,0)] has a rounding error. 

Correct.  Use the following formula instead:

=--TEXT(AO2+TIME(0,5,0),"hh:mm:ss")

The double negative (--) converts the text to a number.

The issue is:  Excel time is stored as a fraction of a day.  So 1 hour is 1/24, 1 minute is 1/1440, and 1 second is 1/86400.  Excel and most applications normally use binary floating-point to represent numbers and do arithmetic.  Consequently, most non-integers (and integers with a magnitude greater than 2^53) cannot be represented exactly.  This causes usually-small differences between the representation of the computation and what you might expect using pencil and paper.

When you copy-and-paste-value, Excel sometimes corrects these infinitesimal differences, effectively entering the TEXT() representation instead of copying the exact binary representation.  However, the operative is "sometimes".  Sometimes not!

Answer 2

This is due to very small rounding errors that occur when adding TIME(0,5,0). These accumulate as you progress to the right although they're too small to see with the naked eye... :)

One workaround is to enter 7:00 PM in E2 and 7:05 PM in F2, then select E2:F2 and use the fill handle in the lower right corner of the selection to fill right to BL2.

Answer 3

Adding sequentially where each addition has an error is worse than adding once. (There are still chances for errors, though it is reduced.)

Try changing your formula for adding 5 minutes from

=AO2+TIME(0,5,0)

to

=$AO$2+COLUMNS($A$1:A1)*5/60/24

if you are copying across

You may also want to change your formulas that do comparison to include only rounded values.

=IF($B41 <=AC$2,IF($C41>AC$2,10,0),0)

to

=IF(ROUND($B41,6) <=ROUND(AC$2,6),IF(ROUND($C41,6)>ROUND(AC$2,6),10,0),0)

or change your time values to be rounded:

=ROUND($AO$2+COLUMNS($A$1:A1)*5/60/24,6)

You could also change your times to be a second less or a second greater and then they won't be right at the cusp.

HTH,

Bernie