Rounding to the nearest quarter hour

[Myrdrai]

I'm using a template I found for employee timesheets and it works well but there is just one problem how do I get it to calculate the hours to the "nearest quarter hour"

EDIT: Sorry, I didn't read the guidelines before posting

Using Excel 2003, on Windows XP

=ROUND(IF((OR(B12="",C12="")),0,IF((C12<B12),((C12-B12)*24)+24,(C12-B12)*24))+IF((OR(E12="",F12="")),0,IF((F12<E12),((F12-E12)*24)+24,(F12-E12)*24)),2)

I know literally Nothing about this so I have no idea what I'm even looking at.

Thanks for all your time and patience

 

[Mark W.]

=MROUND(A1,1/96)

...rounds timevalue in a1 to nearest quarter hour.

 

[Myrdrai]

Would code look like the following then?

=MROUND(IF((OR(B12="",C12="")),0,IF((C12<B12),((C12-B12)*24)+24,(C12-B12)*24))+IF((OR(E12="",F12="")),0,IF((F12<E12),((F12-E12)*24)+24,(F12-E12)*24)),1/96)

 

[Mark W.]

I guess. I don't know what this is...

=IF((OR(B12="",C12="")),0,IF((C12< B12),((C12-B12)*24)+24,(C12-B12)*24))+IF((OR(E12="",F12="")),0,IF((F12< E12),((F12-E12)*24)+24,(F12-E12)*24))

...does it produce a timevalue?

 

[Myrdrai]

That makes 2 of us, I don't what what it all is either

I didn't create the sheet so I have no idea what it all means unfortunately, and I don't really know how to describe what I need for it to do except that I need the Total Hours to round to the nearest quarter hour


EDIT: Removed content

 

[Mark W.]

You can't paste HTMLMaker from the clip board. You need to View Source, copy it and paste that instead.

 

[Myrdrai]

sorry about that, here it is again:

timesheet-template.xls
	A	B	C	D	E	F	G	H	I	J	K
10	Day of Week	Time In	Time Out	Meal	Time In	Time Out	Total Hrs	Regular Hrs	Overtime Hrs	Sick Hrs	Vacation Hrs
11	Sunday
12	Monday	8:00 AM	11:33 AM		12:07 PM	5:00 PM	8.43	8.00	0.43
13	Tuesday
14	Wednesday
15	Thursday
16	Friday
17	Saturday
18							Total:	8.00	0.43
19							Rate:	$15	$23	$15	$15
20							Total ($):	120.00	9.67	-	-
TimeSheet

 

[Mark W.]

=MROUND([your formula here],0.25)

 

[Myrdrai]

When I made the edits I got the message

The formula in this cell differs from the formulas in this area of the spreadsheet

New sheet:

timesheet-templateTEST.xls
	A	B	C	D	E	F	G	H	I	J	K
10	Day of Week	Time In	Time Out	Meal	Time In	Time Out	Total Hrs	Regular Hrs	Overtime Hrs	Sick Hrs	Vacation Hrs
11	Sunday
12	Monday	8:00 AM	11:33 AM		12:07 PM	5:00 PM	8.50	8.00	0.50
13	Tuesday
14	Wednesday
15	Thursday
16	Friday
17	Saturday
18							Total:	8.00	0.50
19							Rate:	$15	$23	$15	$15
20							Total ($):	120.00	11.25	-	-
TimeSheet

 

[Mark W.]

See http://support.microsoft.com/default.aspx?scid=kb;en-us;277614