Results 1 to 14 of 14

20040816, 08:44 #1
 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

20040816, 08:56 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 addin. If you want to avoid this addin, you can use =TRUNC(2*A1+1)/2

20040816, 09:09 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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 Addin for this function to be available.
Hope that helps.Regards,
Rory
Microsoft MVP  Excel

20040816, 09:11 #4
 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

20040816, 09:20 #5
 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

20040816, 09:50 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040816, 09:52 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20040816, 10:06 #8
 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

20040816, 10:45 #9
 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

20040817, 09:53 #10
 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 1629 minutes, round down to 30 minutes if from 3145 and round up to the next hour if 4659 minutes.
Is this possible?????????
Regards

20040817, 10:02 #11
 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 1629 minutes, round down to 30 minutes if from 3145 and round up to the next hour if 4659 minutes.
Is this possible?????????
Regards

20040817, 10:55 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040817, 11:06 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20040817, 11:50 #14
 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