Rounding to the nearest quarter hour

Myrdrai

New Member
Joined
Feb 18, 2005
Messages
7
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


Code:
=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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
=MROUND(A1,1/96)

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

Myrdrai

New Member
Joined
Feb 18, 2005
Messages
7
Would code look like the following then?


Code:
=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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

New Member
Joined
Feb 18, 2005
Messages
7

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
You can't paste HTMLMaker from the clip board. You need to View Source, copy it and paste that instead.
 

Myrdrai

New Member
Joined
Feb 18, 2005
Messages
7

ADVERTISEMENT

sorry about that, here it is again:
timesheet-template.xls
ABCDEFGHIJK
10Day of WeekTime InTime OutMealTime InTime OutTotal HrsRegular HrsOvertime HrsSick HrsVacation Hrs
11Sunday 
12Monday8:00 AM11:33 AM12:07 PM5:00 PM8.438.000.43
13Tuesday 
14Wednesday 
15Thursday 
16Friday 
17Saturday 
18Total:8.000.43  
19Rate:$15$23$15$15
20Total ($):120.009.67--
TimeSheet
 

Myrdrai

New Member
Joined
Feb 18, 2005
Messages
7
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
ABCDEFGHIJK
10Day of WeekTime InTime OutMealTime InTime OutTotal HrsRegular HrsOvertime HrsSick HrsVacation Hrs
11Sunday 
12Monday8:00 AM11:33 AM12:07 PM5:00 PM8.508.000.50
13Tuesday 
14Wednesday 
15Thursday 
16Friday 
17Saturday 
18Total:8.000.50  
19Rate:$15$23$15$15
20Total ($):120.0011.25--
TimeSheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,770
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top