+ Reply to Thread
Results 1 to 6 of 6

Time Values and Rounding

  1. #1
    Registered User
    Join Date
    09-16-2009
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2003
    Posts
    30

    Time Values and Rounding

    I need a simple formula that will do the following:

    1. Take the time value in A1 (say 6:30 or whatever)
    2. Multiply that by 1.5 (to get time and a half) and then
    3. Round the sum to the nearest 15 minutes
    All cells are formatted as [h]:mm

    I can do these functions separately but can't figure out how to combine them into one formula.

    Thanks,
    Kirk

  2. #2
    Registered User
    Join Date
    09-26-2010
    Location
    A, A
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Time Values and Rounding

    this should work...
    =VALUE(A1)*1.5
    assuming there is 6:30 in cell A1 then the result in B1 is 9:45

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Time Values and Rounding

    Perhaps =MROUND(A1*1.5,1/96)

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

    Re: Time Values and Rounding

    Or =MROUND(1.5*A1, "0:15")
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-16-2009
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Time Values and Rounding

    All three formulas work great. Is there any advantage to using one over the other?

    Thank you all for your help,
    Kirk

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

    Re: Time Values and Rounding

    The Value function in the first is unnecessary, and the formula doesn't round to the nearest 15 minutes. The second two are equivalent.

+ 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