# Thread: Time sheet hours (Excel 2000/2003)

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

4. ## 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.
Regards

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

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

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

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

10. ## Re: Time sheet hours (Excel 2000/2003)

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

The reply came back to me, so maybe I posted it incorrectly??
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. ## 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. ## Re: Time sheet hours (Excel 2000/2003)

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

14. ## 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
•