+ Reply to Thread
Results 1 to 3 of 3

Comparing Calculated Times

  1. #1
    Mike
    Guest

    Comparing Calculated Times

    Hello!

    I have a worksheet which will be used by employees to determine their
    break times in a decimal format (ex: 15 minutes = 0.25 hours). The
    employees enter their particular start time and end times into two
    columns and the difference is calculated. The result is then plugged
    into a VLOOKUP where the decimal value is returned.

    So far so good. The problem is that Excel does not always calculate one
    minute to be the exact same serial number. Every once in a while it
    produces a number which is close to but not exactly the same as another
    minute calculated serial. Since the VLOOKUP will only return an error
    on an exact match or a result which is less than the result on a
    "fuzzy" match up, I need to figure out how to compare different serials
    (or make them the same serial number for the VLOOKUP).

    For example:

    1:01 pm -1:00 pm = 0.000694444444444553
    2:01 pm - 2:00 pm = 0.000694444444444442
    3:01 pm - 3:00 pm = 0.000694444444443998
    8:01 pm - 8:00 pm = 0.000694444444442999

    All times calculated result in one minute of elapsed time, however the
    serial is different which will throw an exact match completely awry.

    Is there a function which will assist me in this endeavor? Any help is
    greatly appreciated.

    Thanks!

    Mike


  2. #2
    Forum Contributor
    Join Date
    03-23-2006
    Location
    Vancouver
    Posts
    114

    =round

    All you need is to use the =ROUND function to round off your results to fewer decimal places. 6 places should be sufficient since you only want to measure to the nearest minute.

    CELL A2 = 9:00
    CELL B2 = 9:10

    CELL C2 = ROUND(B2-A2,6)

  3. #3
    Mike
    Guest

    Re: Comparing Calculated Times

    Thanks. I feel like an idiot now. :-D

    Mike

    CaptainQuattro wrote:
    > All you need is to use the =ROUND function to round off your results to
    > fewer decimal places. 6 places should be sufficient since you only
    > want to measure to the nearest minute.
    >
    > CELL A2 = 9:00
    > CELL B2 = 9:10
    >
    > CELL C2 = ROUND(B2-A2,6)
    >
    >
    > --
    > CaptainQuattro
    > ------------------------------------------------------------------------
    > CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
    > View this thread: http://www.excelforum.com/showthread...hreadid=557747



+ 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