+ Reply to Thread
Results 1 to 4 of 4

How to avoid date-time decimal precision errors?

  1. #1
    Registered User
    Join Date
    04-14-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    24

    How to avoid date-time decimal precision errors?

    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

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to avoid date-time decimal precision errors?

    Try =mround(a1, 1/2880)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-14-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: How to avoid date-time decimal precision errors?

    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

  4. #4
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: How to avoid date-time decimal precision errors?

    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!
    Last edited by shg; 05-29-2010 at 12:43 PM. Reason: Deleted spurious quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1