Excel Timesheet Rounding

[jackiembg]

Hi, I'm trying to develop a timesheet that will round to the nearest 15 minutes and I can't figure out what to use for a formula. So if someone clocks in at 8:04, they can enter that, but it will count as 8:00. Can anyone please help me? I'm attaching a timesheet that we currently use. This is my first time posting and any I'm looking for any help I can get. Please and thank you!!

These are the guidelines we currently use:

1. * Rounding set to 15 minutes, an employee clocking in between 8:00AM and 8:07AM will have their time rounded down to 8:00:00AM.
2. * Rounding set to 15 minutes, an employee clocking in between 8:08AM and 8:14AM will have their time rounded up to 8:15:00AM.
All time should be rounded to fifteen minute increments (i.e. 8:15, 8:30, 8:45 and 9:00). When totaling the number of hours for each day, they should be in increments of 15 minutes = .25 hours, 30 minutes = .5 hours, 45 minutes = .75 hours and 60 minutes = 1 hour.

I.E.
CLOCK IN TIME = 7:52 a.m. / PAY AS 7:45 a.m.
7:55 a.m. / PAY AS 8:00 a.m.
8:07 a.m. / PAY AS 8:00 a.m.
8:09 a.m. / PAY AS 8:15 a.m.
8:21 a.m. / PAY AS 8:15 a.m.
8:23 a.m. / PAY AS 8:30 a.m.
CLOCK OUT TIME = 4:51 p.m. / PAY AS 4:45 p.m.
4:54 p.m. / PAY AS 5:00 p.m.
5:07 p.m. / PAY AS 5:00 p.m.
5:09 p.m. / PAY AS 5:15 p.m.
5:22 p.m. / PAY AS 5:15 p.m.
5:24 p.m. / PAY AS 5:30 p.m.

[SteveG]

Hi,

Use MROUND. =MROUND(A2,15/(60*24)) where A2 is your time.

HTH
Steve

[jackiembg]

Hi Steve, thank you so much for responding. Sorry, but I'm not an advanced excel user. Would that formula go in the cell where the total hours are calculating or where they check-in and our their time?

[SteveG]

See the attached sheet. You would round the Login and Logout and use the formula in the Sum column to change those to decimals in the sum.

Steve

[jackiembg]

Thank you, I've been trying to figure this out for an entire day. THANK YOU!!

[binker]

Hiya
Thanks this helps so much! But how do I factor in nightshifts ?
Same rule applies to rounding , except staff start shift in the evening and end shift in the morning?

[whyplay]

I know its an older thread but this was exactly what I was looking for. I am glad I found this site. Thank you.

[VMRevClerk]

Hey, y'all. I know this info is from awhile back, but it is what I need only I can't view the document Steve provided. I need help!! where do I enter the formula?? Please help!!

[FDibbins]

Hey, y'all. I know this info is from awhile back, but it is what I need only I can't view the document Steve provided. I need help!! where do I enter the formula?? Please help!!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread