Time calculation inconsistent

  • I'm working on a sheet that is basically a Gantt chart. The user enters a start time and duration, then using conditional formatting, the appropriate cells are filled. Time values are entered in 1/2 hour increments (from list). There are three 1/2-hour breaks (9 a.m. 11:00 a.m. and 2:00 p.m.) that need to be factored in. This is the basic rule I'm using for conditional formatting: from 6 a.m. to 9 a.m. =AND($D6<=H$4,$D6+($F6/24)>=I$4) D6 contains start time, F6 has duration and row 4 has time in 1/2 hour increments. For time periods after the breaks, I add additional checks to determine start time. I broke the rules up and tested each portion to see where things go awry and see that at around 4 p.m. the calculation returns unexpected values. I'm attaching the sheet and have highlighted the cell where I detected the first problem. It's basically saying that start time (1:30 p.m.) + duration (2.5 hours) > 4 p.m. and that shouldn't be true. I appreciate anyone that can take a look at this and provide ideas on how to correct it. It's pretty messy, because a this point, I'm just trying to get the formulas right.forum.ozgrid.com/index.php?attachment/71241/

  • Re: Time calculation inconsistent

    I realized the problem lies with the "hours added/24" method of adding hours to a time value in Excel. The degree of accuracy causes a value that's displayed to be slightly lower than the actual value calculated (maybe by milliseconds?). My workaround isn't elegant, but it works for this case - subtract .00001 from the quotient. I suspect this could more easily be accomplished by using the rnd function, but I haven't used it and would need to study that.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!