Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time in increments (Excel 2000)

    (Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

    I was using the formula Hans gave in post re: <post#=517237>post 517237</post#> from CBrowning, but it is not working on my sheet. I am using 30 minute increments, and not 15...that may be the problem. could someone take a peek at the timesheet I have and tell me where I am going wrong. Is it my cell format? Is it the formula?
    Thank you
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Time in increments (Excel 2000)

    I assume your problem concerns cell I3 on the Instructor & Class Info sheet.
    1) If you want to round to 30 minutes, use

    =MROUND(H3-G3,1/48)

    The reason for 1/48 is that 30 minutes = 1/2 hour = 1/48 day. Excel stores dates and times with days as unit.

    2) You must use Format | Cells... to format I3 as a time.

    Added:
    To get the result in J3 right, you must multiply the value in I3 or in J3 with 24, since the above formula uses 1 day as unit, and the pay is by the hour.
    For example, change the formula in J3 to

    =IF(ISERROR(I3*F3),"",I3*F3*24)

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

    Re: Time in increments (Excel 2000)

    Two things:

    1- If you read HANS' post, you will find that 1/96 = 1/4hour * 1/24 day. If you want to use 30 minutes, that would be 1/2 hour * 1/24 day = 1/48. Therefore, you need to use 1/48 in the MROUND function.

    2- Cell I3 is not formatted in a Time format. Therefore, it is displaying the fractional part of a day, not a time.
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in increments (Excel 2000)

    Hans,
    I did make that change after posting the message. But in the final Pay cell it only gives me $.94.

    Instructor HrPa StartTime EndTime Total Hrs Total Pay

    Christine Ly $15.00 07:30 09:00 1:30 $0.94

    And I3 is formatted as time...eg. 13:30 and still only get .94 cents
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Time in increments (Excel 2000)

    That's because you did not convert Excel's time value (fractional part of a day) into decimal hours. The formula should be:

    =IF(ISERROR(I3*24*F3),"",I3*24*F3)
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in increments (Excel 2000)SOLVED

    Thank you Hans...That was it. And you to Legare...both are wonderful

    =IF(ISERROR(I3*F3),"",I3*F3*24)
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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