Regular Hours on Timesheet

**Meep** · 03-21-2013, 12:12 AM

I need further assistance on a timesheet that I am fixing. Updated Payroll.xlsx

I am stuck on the Regular Hours column (G11:G26). I have the hours calculating correctly, with it only showing <= eight hours. However I need it to round to the quarter hour as well. Is there any formula I can add to also round the hours to the quarter hour (i.e. 8:08am = 8:15am and 8:07am = 8:00am)?

User martinandwilson provided the following formula calculating the total (=ROUND((E11-B11-D11+C11)*96,0)/4) which rounds to the quarter hour, but I need the regular hours to also round to the quarter hour.

User Berna11 was kind enough to provide this formula =IF(MOD(MINUTE(G12),15)<=7,FLOOR(G12,1/(24*4)),CEILING(G12, ---- but i would need to make a new column to use this which I don't want to do.

Thanks for any help in advance.

Meep.

**dredwolf** · 03-21-2013, 01:17 AM

Try this
For closest 1/4 hour:

Formula:

Please Login or Register  to view this content.

For next highest 1/4 hour:

Formula:

Please Login or Register  to view this content.

Hope this helps

**Meep** · 03-21-2013, 01:51 AM

You are a saint and a scholar.

Much appreciated! You solved an issue I spent an hour trying to resolve in mere minutes.

You and this forum are awesome.

Thanks again.

Meep.

**dredwolf** · 03-21-2013, 02:14 AM

You are welcome, and thank you for the kind words

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"