Results 1 to 12 of 12

Thread: Roundup ?

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Roundup ?

    Hi

    I use this formula to calculate time IF(H4="","",H4-G4) the result in this instance is 1:59 I want to roundup to 2:00 I won't bore you with the reason why unless it becomes necessary.


    Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Braddy

    Is this hrs:mins or mins:secs??

    zeddy

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Zeddy

    It's hrs:mins

    Thanks for the reply
    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Braddy

    So, you want to round up to the nearest hour?

    In Excel, all time is stored in cells in decimal units of 'days', irrespective of your chosen display format.
    So your calc (H4-G4) gives a result in 'days' but displays in your chosen format.
    We first convert your answer in days to give an answer in decimal hours:
    (H4-G4)*24 e.g. 1.965432
    Then use
    =ROUNDUP((H4-G4)*24,0) to round up to the nearest hour.
    Then convert back into days for the cell value:
    =ROUNDUP((H4-G4)*24,0)/24

    zeddy

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Zeddy

    Thanks very much for that, however I should have made it clear I need to roundup to the nearest half hour.

    Regards
    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Change the 24s to 48s

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve

    That's great

    Thanks to you and Zeddy, I am in your debt

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    As an alternative, I think
    IF(H4="","",CEILING(H4-G4,TIME(0,30,0))
    would do it.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    Never heard of the ceiling function before, I will give it a try

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Tacoma, Washington, USA
    Posts
    431
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by rory View Post
    As an alternative, I think
    IF(H4="","",CEILING(H4-G4,TIME(0,30,0))
    would do it.
    Fascinating. We have a timesheet that calculates the hours worked with =sum(I6-D6)-(F6-E6)-(H6-G6) and puts the results in the J6 cell. D6 being the time they first arrive, I6 being the time they leave for the day, and the other four are for two in and out times during the day for lunch or appointments.

    The trouble we've had was rounding up the real time to the quarter hour. At the end of the day J6 might equal 7:54. We've left it up to the user to translate that into a useful number rounded to the nearest quarter. This number rounds up to 8. If it had been 7:52, the user rounds down to 7.75.

    Your use of CEILING and TIME is the closest I've been to solving how to have the timesheet do that rounding instead of the user. Is there a way to do that, rounding to nearly 15 minute increment?
    Daisy

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like:
    =CEILING(j6,TIME(0,15,0))

    Steve

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Tacoma, Washington, USA
    Posts
    431
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Here's the formula we came up with:

    =ROUND((SUM(H12-C12)-(E12-D12)-(G12-F12))*96,0)/96

    C12 is when the person arrives, H12 is when they leave at the end of the day, and the others are for two breaks like for lunch or an appointment. The formula rounds to the nearly 15 minutes and then converts the time into a usable number for our bookkeeper.

    Without the help I get here we'd still be doing this the hard way. Thank you!
    Daisy

Posting Permissions

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