Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Tulsa, Oklahoma, USA
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Time entries in 15 minute increments (Excel 2000)

    Does anyone know how to have Excel calculate time worked (beginning time+ending time) so that the sum is rounded up or down to the nearest 15-minute increment? Thanks muchly..........
    Cyndie Browning
    GableGotwals
    Tulsa, OK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Make sure that the Analysis ToolPak add-in has been installed/activated (Tools | Add-Ins...)
    Say that the beginning time is in A1 and the ending time in B1. Use the following formula to calculate working time rounded to the nearest multiple of 15 minutes:

    =MROUND(B1-A1,1/96)

    Excel stores times as a fraction of 1 day. 15 minutes = 1/4 hour = 1/4 * 1/24 day = 1/96 day. The MROUND function rounds the first argument (working time) to the nearest multiple of the second argument (1/96 day = 15 minutes)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time entries in 15 minute increments (Excel 2000)

    Cyndie

    You can also use =CEILING((B1-A1)/0.04167,0.25) where 0.04167= 1/24 and where value B1> Value of A1
    Jerry

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    Tulsa, Oklahoma, USA
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Thanks muchly, Jerry and Hans. I guess I don't have the Analysis ToolPak add-in because the "mround" thingee didn't work, but the "ceiling" thingee did---which keeps the boss happy. Thanks again...........
    Cyndie Browning
    GableGotwals
    Tulsa, OK

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    The solution with CEILING will suit your purpose, but isn't Analysis ToolPak listed when you select Tools | Add-Ins...?

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Tulsa, Oklahoma, USA
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Ahhh, yes, okay.... it's there. And when I check it and then insert the "=mround" equation you sent me, the equation works (that is, I do NOT get an error message) but by the same token, I do NOT get the right answer. I DO with the "=ceiling" equation. Since all I need is the right answer, that works for me. But thanks for pursuing it just a little further; I appreciate your taking the time.
    Cyndie Browning
    GableGotwals
    Tulsa, OK

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    I hope you are aware that Jezza's formula will always round UP, not up or down as you asked.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Tulsa, Oklahoma, USA
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Well, since you brought it up, let me show you the problem. Using your equation,
    =MROUND((+C1-B1-D1),1/96),
    where C1 is the time out, B1 is the time in, and D1 is an hour for lunch,
    I get the following result: 0.34.

    Excel wants to modify your equation as follows:
    =SUM(MROUND((+C1-B1-D1),1/96)), which looks a little different from yours but gives the same result: 0.34. Which is the wrong answer!

    So, what now, coach?
    Cyndie Browning
    GableGotwals
    Tulsa, OK

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Select the cell or cells with the formula, then select Format | Cells and apply a Time format.

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Location
    Tulsa, Oklahoma, USA
    Posts
    17
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Hey, it works!!

    Boy, Hans, whatever it is that you do, I hope they're payin' you enough!! Thanks SO much.
    Cyndie Browning
    GableGotwals
    Tulsa, OK

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    Oh, the Lounge pay is a nice round figure! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    And they double yours every month! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time entries in 15 minute increments (Excel 2000)

    Cyndie

    I wish I worked for your company, if you had used my formula I would be leaving at 1701hrs every day, I would get 4 hours and 40 mins extra pay a month unless you want to round that up too <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jerry

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Time entries in 15 minute increments (Excel 2000)

    A day late and a dollar short (Hans already earned it <img src=/S/yep.gif border=0 alt=yep width=15 height=15>), but see if this works without the ToolPack:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=center>Start</td><td align=center>End</td><td align=center>Diff</td><td align=center>2</td><td align=right>12:01:00 PM</td><td align=right>12:31:00 PM</td><td>=ROUND((B2-A2)*96,0)/96</td></table>
    Format Cell C2 as Hans explained (or for periods > 24 hours, with the custom format [h]:mm).
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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