How to avoid date-time decimal precision errors?

**jimojimo** · 05-28-2010, 07:05 PM

Hi all,
I'm dealing with a sheet that has a lot of time values in the cells. I need to match up times to a specific 30 second interval. So I need to round times so that they fall on :00 or :30 second intervals, then other cells need lookup these cells and match them exactly.

Essentially, I want to scale-up the decimal value of the time to an integer, I tried Mround() and I can't seem to get it to do it correctly.

The problem is that one cell might be, say 8:30:30 AM, which may have a value of 0.354513888888889

Another cell might get the 8:30:30 AM time from another source and it might happen to have a value of:
0.354514988888889

Becaus of the 3rd part solver program that's operating on these cells the values *must* be numeric, so I can't just truncate using Left(), or do a Text() or read the formatted value or anything like that--I need to deal with the underlying decimal value, which is why I'm thinking if I scaled everything prior to loading it to the sheet I could avoid this.

I'm thinking I need to scale the numbers up but any scaling *must* take place in an excel forumula--not a user-defined function. I tried the Fix(), Clng() functions that I'm used to in VB, but these don't seem to work in formulas, just in the vba environment.

So am I possibly barking up the wrong tree here? I'm sure others have come across similar needs and I'm wondering if there's an elegant solution to this. Maybe I just need help with the Mround() function, I'm not sure.
Thanks for any advice,
--Jim

**shg** · 05-28-2010, 07:09 PM

Try =mround(a1, 1/2880)

**jimojimo** · 05-29-2010, 10:26 AM

shg,
Thanks, but I had tried that, it's something else. Using the 1/2880 makes the cell referencing the times appear to have an evenly rounded time, it still doesn't calculate behind-the-scenes. So I just converted all of my time inputs to integers upon load, and that did it.

I scaled all the time values to 1/2 minutes ie, 0=midnite, 1 = 12:00:30 AM, 2 = 12:01 AM, etc. up to 2880 for the day. Problem solved instantly.

Even though using that 1/2880 made the display of time *look* correct, somewhere in the numerous sumif's and sumproducts, it was missing a cell due to minor precision difference, even if by a number 8 places right of point.
--Jim

**squiggler47** · 05-29-2010, 10:53 AM

you could do this :-
=((HOUR(B2)*60+MINUTE(B2))*60+ROUND(SECOND(B2)/30,0)*30)

it rounds to the nearest 30 seconds as an integer, you can convert it back to time by dividing by 86400, since it does the rounding before dividing it should always give the same result!

Note that excel cant represent some decimal numbers exactly 0.1 being one, so using the above formula and comparing would achieve a better result for you!