Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Calculate Time, but round to nearest 15 minutes

  1. #1
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate Time, but round to nearest 15 minutes

    We bill services in 15 minute intervals. I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments.

    Desired result:
    1/1/07 12:00 in A1
    1/1/07 14:10 in B1
    1.25 in C1

    0-7 Minutes - round down to 0.00
    8-22 Minutes - round to 0.25
    23-37 Minutes - round to 0.50
    38-52 Minutes - round to 0.75
    52-60 Minutes - round to 1.00

    Because this is billable time, the number is never negative, so I don't need the formula to verify the first date/time cell is less than the second.

    Any suggestions would be greatly appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,027
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, welcome to the board!

    How do you get 1.25 from those times? It looks like 2 hours and ten minutes
    Office 2010/365

  3. #3
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =ROUND((B1-A1)*24*4,0)/4

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,027
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Which of course could be written as:
    =ROUND((B1-A1)*96,0)/4

    but it will return 2.25 for your example, not 1.25
    Office 2010/365

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you don't mind using Analysis ToolPak functions you could also use MROUND, i.e

    =MROUND((B1-A1)*24,0.25)

    or

    =MROUND(B1-A1,"0:15")*24

  6. #6
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Which of course could be written as:
    =ROUND((B1-A1)*96,0)/4
    but doesn't show as clearly what is going on

    but it will return 2.25 for your example, not 1.25
    I assumed that was just a typo, 1.25 is just ridiculous with those numbers.

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,027
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assumed that was just a typo, 1.25 is just ridiculous with those numbers.
    Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.

    Here's what I've come up with as a possible scenario for wanting 1.25

    Say for example, a service call costs $75 and includes the first hour of service. Anything else after that is billed at a different rate, in this case you would not want to count the first hour, but everything after that.
    Office 2010/365

  8. #8
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Answer

    Yes, sorry. 2.25 was the desired result.

    Mahalo to all of you.

  9. #9
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assumed that was just a typo, 1.25 is just ridiculous with those numbers.
    Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.
    IYO. There was also a subject matter which was quite clear. All in, there seemed insufficient cause to pick holes in someone else's suggestion, disingenuous at the mimimum.

  10. #10
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,027
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I wasn't picking holes in someone elses suggestion, I was just saying the op might want 1.25 like he asked for, and since that was what he asked for maybe there was a reason he wanted that.

    BTW, my suggestion was the same as yours, all I said was you didn't have to type 24*4, you could just type 96.
    Office 2010/365

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com