Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding hours (all)

    After all the time I spent improving my client's time sheet (with kind assistance from you folks!), they've just hit me with another curve ball. The time sheet allows me to record hours in fifteen minute intervals (very reasonable), but the project tracking spreadsheet is done in whole hours. Yes, it really is too much trouble for the data entry monkeys to use their wetware to round my time, so the project manager asked me to enter my time in whole hours (pain in the butt).

    First I tried to change the format of the total hours from h:mm to h, but that rounded all the time downward. The ROUND function doesn't appear to work hours and minutes. I could do it manually, but what's the fun in that!

    Thanks in advance!

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

    Re: Rounding hours (all)

    I don't think there is a time format that rounds times to the nearest hour. You can round using a formula, though. With a time value in cell A1, the formula
    <code>
    =ROUND(24*A1,0)/24
    </code>
    will return the time rounded to the nearest hour: 1:15 becomes 1:00 and 1:45 becomes 2:00. Following standard rounding conventions, 1:30 becomes 2:00.

  3. #3
    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

    Re: Rounding hours (all)

    <P ID="edit" class=small>(Edited by rory on 30-Apr-07 14:56. )</P>Edit: I forgot to mention that MROUND requires the Analysis Toolpak add-in!

    If you want to round to the nearest hour, then you can use <code>=MROUND(A1,1/24)</code>
    where A1 contains your time.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!

    As always, I'm most humbly thankful for the help!

    I just ran the new formula and discovered that I work too many half hour periods. My weekly total ends up being 41 instead of 40. It's probably easier to adjust my workday to finish on the hour, quarter hour or three-quarter hour period. <sigh>

Posting Permissions

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