Results 1 to 10 of 10

Calculate Time, but round to nearest 15 minutes

This is a discussion on Calculate Time, but round to nearest 15 minutes within the Excel Questions forums, part of the Question Forums category; We bill services in 15 minute intervals. I am trying to calculate the time between two cells and have the ...

  1. #1
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23

    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
    16,818

    Default

    Hi, welcome to the board!

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

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  3. #3
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default

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

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,818

    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 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,793

    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
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    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
    16,818

    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 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  8. #8
    New Member
    Join Date
    Jan 2007
    Location
    Honolulu, HI
    Posts
    23

    Default Answer

    Yes, sorry. 2.25 was the desired result.

    Mahalo to all of you.

  9. #9
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    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
    16,818

    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 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

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