Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time sheet hours (Excel 2000/2003)

    I have a time sheet with the daily hours totalled using the following formula:
    =(SUM(J12:J42)*24)
    The total may be 145.35 hrs for the month.
    How do I 'round' the minutes so that if it is 145.15 and less it becomes xxx.0. If it is xxx.16 to xxx.30 it makes it xxx.30 etc?
    Regards

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

    Re: Time sheet hours (Excel 2000/2003)

    I hope you are aware that if you calculate the total hours this way, the .35 is a decimal fraction, not the number of minutes (i.e. .35 corresponds to .35 * 60 minutes = 21 minutes)

    Can you give a more complete description of how you want to round? To the nearest half hour? You could use =MROUND(A1,0.5) for this where A1 is the cell containing the total; MROUND is a function in the Analysis Toolpak add-in. If you want to avoid this add-in, you can use =TRUNC(2*A1+1)/2

  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: Time sheet hours (Excel 2000/2003)

    Hi,
    I'm not quite sure I understand your rounding method - are you interpreting 0.15 as being 15 minutes, because I would interpret it as 0.15 * 60 = 9 minutes. At any rate, I think the MROUND function is what you are looking for. Note: you need to install the Analysis Toolpak Add-in for this function to be available.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    No wonder the times would not work out correctly.
    Now I am back to square one.
    I have Start time, Lunch Out, Lunch In, End time and the hours worked.
    What I need to do is total up all the worked hours at the end of the month and then multiply this with the rate per hour.
    I subtracted the hours from each other, and then in a seperate column, multiplied the daily result by 24.
    The result I multiplies by 24 as well.
    Suggestions, please...
    Regards

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    Yes, I realise now I am not interpreting the time as the computer time.
    And I am wanting to round to the nearest 15 minutes (30 minutes in some cases).
    Also the problem is when the total hours become greater than 24 - and finally I need to multipy these hourse by an hourly rate.
    Regards

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

    Re: Time sheet hours (Excel 2000/2003)

    You can calculate daily worked hours as (Lunch Out)-(Start Time)+(End Time)-(Lunch In).
    You can simply add the results and set the number format to [h]:mm. This format is for accumulated time and displays hours above 23.
    To round the result to the nearest quarter of an hour, use =MROUND(...,1/96), and to round to the nearest half hour, use =MROUND(...,1/48).
    Explanation: Excel stores times as fractions of a day. There are 24 hours in a day, that is 2*24=48 half hours and 4*24=96 quarters of an hour. One quarter of an hour is 1/96 day.
    To calculate the total amount, multiply the rounded total hours by 24 to get hours instead of days and by the hourly rate to get the amount.

  7. #7
    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: Time sheet hours (Excel 2000/2003)

    Further to Hans' latest post, the attached should give you some idea.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Time sheet hours (Excel 2000/2003)

    If your number is in A1 you can use this formula:
    =INT(A1*24*60/15+0.5)*15/60/24

    If you are going to use it in a lot of formulas, I would put the numbers in a cell and create a reference to them.

    The format it as desired for date display (the value is in DAYS) so if you need it in calcs, in the formula multiply it by 24.

    If the number of hours will be >24 use a format like:
    [h]:mm
    to display elapsed time instead of time of day.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    Thanks Hans, Rory and Steve for your valuable input.
    Greatly appreciated.
    Regards

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    Please let me continue...
    I time sheet house are working fine now - the next stage is the total hours. Let's say it is 145 hrs and 12 minutes 145:12
    What I am required to do is round down if the hours are less than 15 minutes, round up to 30 minutes if time is from 16-29 minutes, round down to 30 minutes if from 31-45 and round up to the next hour if 46-59 minutes.
    Is this possible?????????
    Regards

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    The reply came back to me, so maybe I posted it incorrectly??
    Please let me continue...
    I time sheet house are working fine now - the next stage is the total hours. Let's say it is 145 hrs and 12 minutes 145:12
    What I am required to do is round down if the hours are less than 15 minutes, round up to 30 minutes if time is from 16-29 minutes, round down to 30 minutes if from 31-45 and round up to the next hour if 46-59 minutes.
    Is this possible?????????
    Regards

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

    Re: Time sheet hours (Excel 2000/2003)

    The various formulas provided by Rory, Steve and me higher up in this thread all demonstrate how to round to the nearest quarter of an hour or half hour. In this case, you want to round to the nearest half hour.

  13. #13
    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: Time sheet hours (Excel 2000/2003)

    Hi,
    If you amend your totals formula to read:
    =mround(sum(A1:A6),(0.5/24))
    you should get what you want (amend the range to suit your worksheet).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time sheet hours (Excel 2000/2003)

    Thanks again.
    Regards

Posting Permissions

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